Macro unlinking from buttons when file name changes

[MichaelOneida]

Hello.
I am rather new to VBA.
I have a form set up in Excel. There are fields to fill out,and when the “Submit” button is clicked, there is VBA code that creates a newemail, re-names the file according to specific information in the forms fieldsand attaches the file to the email, as well as Auto-Populates the “To” fieldwith a shared mailbox.

A person at a different location opens this attachment, fills out more fields, clicks a separate button in the form, which attachesthe newly updated form to an email and Auto-populates the “To” field with adifferent shared mailbox.

There is also a 3rd button in the form for the last person in this process to email the form after they add their information.

The problem is, after the first “Submit” button is used,when the person who gets this email opens the attachment, the Macros are nolonger linked to the buttons. When you click the button, it tries to find thepath and original file name.
I can right-click and re-assign the Macro, but the buttonwas set up to begin with because these 2nd and 3rd users are not that computer savvy, so would rather not have to explain that extra step to them.

Is there a way this code could be altered so that the buttons worked even after the file name change and email transfers to different locations.

I would really appreciate any help you could offer. The coding below is for the 1st “Submit” button. For the 2nd and 3rd button, I just copied and pasted this code into new Subs with the numbers 1 and 2 after “Mail_ActiveSheet1()” “Mail_ActiveSheet2()”

[FONT=Arial][SIZE=3][COLOR=#000000]Sub Mail_ActiveSheet()[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]'Working in 2000-2010[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Dim FileExtStr AsString[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Dim FileFormatNumAs Long[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Dim Sourcewb AsWorkbook[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Dim Destwb AsWorkbook[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Dim TempFilePathAs String[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Dim TempFileNameAs String[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Dim OutApp AsObject[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Dim OutMail AsObject[/COLOR][/SIZE][/FONT]


[FONT=Arial][SIZE=3][COLOR=#000000]Set Sourcewb =ActiveWorkbook[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]ActiveWorkbook.Saved = True[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]'Copy the sheet toa new workbook[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]ActiveSheet.Copy[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Set Destwb =ActiveWorkbook[/COLOR][/SIZE][/FONT]

[FONT=Arial][SIZE=3][COLOR=#000000]'Determine theExcel version and file extension/format[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]With Destwb[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]IfVal(Application.Version) < 12 Then[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]'You use Excel2000-2003[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]FileExtStr =".xls": FileFormatNum = -4143[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]'You use Excel2007-2010, we exit the sub when your answer is[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]'NO in the securitydialog that you only see when you copy[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]'an sheet from axlsm file with macro's disabled.[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]If Sourcewb.Name =Name Then[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]With Application[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].ScreenUpdating =True[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].EnableEvents =True[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]MsgBox "Youranswer is NO in the security dialog"[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Exit Sub[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Select CaseSourcewb.FileFormat[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Case 51:FileExtStr = ".xlsx": FileFormatNum = 51[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Case 52:[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]If .HasVBProjectThen[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]FileExtStr =".xlsm": FileFormatNum = 52[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]FileExtStr =".xlsx": FileFormatNum = 51[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Case 56:FileExtStr = ".xls": FileFormatNum = 56[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Case Else:FileExtStr = ".xlsb": FileFormatNum = 50[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]End Select[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]


[FONT=Arial][SIZE=3][COLOR=#000000]FileExtStr = ".xlsm": FileFormatNum = 52[/COLOR][/SIZE][/FONT]

[FONT=Arial][SIZE=3][COLOR=#000000]'Save the new workbook/Mail it/Delete it[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]TempFilePath = "M:"[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]TempFileName ="NF Treatment Request - " & [B8] & ", " & [J8]& " " _[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]&Format(Now, "mm-dd-yyyy")[/COLOR][/SIZE][/FONT]

[FONT=Arial][SIZE=3][COLOR=#000000]Set OutApp = CreateObject("Outlook.Application")[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Set OutMail = OutApp.CreateItem(0)[/COLOR][/SIZE][/FONT]

[FONT=Arial][SIZE=3][COLOR=#000000]With Destwb[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].SaveAsTempFilePath & TempFileName & FileExtStr, _[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]FileFormat:=FileFormatNum[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]On ErrorResume Next[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]With OutMail[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].To = "Omitted_for_Privacy"[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].CC = ""[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].BCC =""[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].Subject ="NF Treatment Request - " & [J6] & ", " & [B6]& ", " & [B8] & "," & [J8][/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].Body ="Attached Find NF Treatment Request for " & [B8] & "," & [J8][/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].Attachments.Add Destwb.FullName[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].Display [/COLOR][/SIZE][/FONT]

[FONT=Arial][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]On Error GoTo 0[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].CloseSaveChanges:=False[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Kill TempFilePath & TempFileName & FileExtStr[/COLOR][/SIZE][/FONT]

[FONT=Arial][SIZE=3][COLOR=#000000]Set OutMail = Nothing[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]Set OutApp = Nothing[/COLOR][/SIZE][/FONT]

[FONT=Arial][SIZE=3][COLOR=#000000]With Application[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].ScreenUpdating =True[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000].EnableEvents =True[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]