Keep Macro in tact Excel to Outlook, the Outook reply back to Excel

[Jackieb302]

I have created a form in Excel and the code contains 2 send buttons. Once the form is completed it lauches outlook with prefilled information. The first send works great. When the receipent reply back with the information, the 2nd send button in the form is not launching the new outlook window. I am very new to using code and would appreciate any help. I know the issues is the macro is not staying attached to the excel spreadsheet so when the file is sent back there is not code for Send button 2. I can' figure out how to keep the macro with the sheet so it remains in tact.

Any help is much appreciated.

[6StringJazzer]

Please attach the Excel file you are using. If you cannot provide your file, post code in a reply using CODE tags. Include all the code.

To use CODE tags:

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[Jackieb302]

Private Sub CSSEND_Click()

[Jackieb302]

Private Sub CSSEND_Click()
          
 Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

     ActiveSheet.Copy
    Set Destwb = ActiveWorkbook

         With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2013
            Select Case Sourcewb.FileFormat
            Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
            Case 52:
                If .HasVBProject Then
                    FileExtStr = ".xlsm": FileFormatNum = 52
                Else
                    FileExtStr = ".xlsx": FileFormatNum = 51
                End If
            Case 56: FileExtStr = ".xls": FileFormatNum = 56
            Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
            End Select
        End If
    End With

  TempFilePath = Environ$("temp") & "\"
    TempFileName = "" & Sourcewb.Name & "" & Format(Now, "dd-mmm-yy h-mm-ss")

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            .Display
            .to = "CUSTOMERMASTERTEAM@SAINT-GOBAIN.COM"
            .CC = ""
            .BCC = ""
            .Subject = Range("F353") & " " & Range("B21") & Range("F354") & Range("B12") & " " & Range("G12") & Range("F355") & Range("B16") & " " & Range("G16") & " " & "CUSTOMER SET UP/CHANGE"
            .Body = "THANK YOU FOR COMPLETING THE FORM."
            .Attachments.Add Destwb.FullName
            .Display
            
        End With
        On Error GoTo 0
        .Close savechanges:=False
    End With

        Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub



Sub submit()
'
' submit Macro
'

'
    Application.GoTo Reference:="Module2.Mail_ActiveSheet"
    
     Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With Destwb
             On Error Resume Next
             
        With OutMail
            .Display
            .to = ""
            .CC = ""
            .BCC = ""
            .Subject = Range("F353") & Range("B21") & Range("F354") & Range("B12") & " " & Range("G12") & Range("F355") & Range("B16") & " " & Range("G16") & " " & "CUSTOMER SET UP/CHANGE"
            .Body = "THANK YOU FOR COMPLETING THE FORM.  NEW CUSTOMER SET UPS REQUIRE THE FORM TO BE COMPLETED BY THE TERRITORY MANAGER AND APPROVED BY BOTH THE REGIONAL MANAGER AND THE REGIONAL VICE PRESIDENT.  PLEASE FORWARD THE FORM WITH APPROVALS TO THE CUSTOMER MASTER DATA TEAM.  CUSTOMER SERVICE WILL NOTIFY YOU WHEN YOUR REQUEST HAS BEEN COMPLETED. PLEASE BE SURE TO INCLUDE ALL RECIPIENTS THAT SHOULD RECEIVE THE RETURN NOTIFICATION."
            .Attachments.Add Destwb.FullName
            .Display
            
        End With
        On Error GoTo 0
        .Close savechanges:=False
    End With

       Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    
   
End Sub

[Jackieb302]

Thanks for your patience. I have never used a forum for assistance before.