E-Mail macro deletes macros in workbook

[fangfacekitty]

I'm sorry for the length of the post, I am trying to provide as much info as possible.

I have a shared workbook form that various users fill in and then send to the appropriate managers for approvals. The workbook is basically a template, although just saved as a normal .xls workbook (sadly, most of my users are still in Excel 2003).

The form has 2 worksheets ("IPCN" & "History") but a user can make copies of the IPCN worksheet in a single workbook if he has multiple items to be approved. Each item (worksheet) can have different approvers. The History tab is just the form revision history we are required to maintain and not needed by the users.

My first macro is for the user, to make a copy of each worksheet that has a specific cell filled in - this cell (G77) is the first approver's e-mail (depending on circumstances there can be between 2-4 total approvers) - and create an e-mail with the worksheet attached. Each worksheet thus becomes its own workbook with a specific name assigned based on the value in cell D27 and the date (the temp file name).

This e-mail works great. The problem is that the worksheet Approver #1 receives has no macros in it. There is supposed to be Macro #2 that basically does the same thing as Macro #1 if the form is approved, except it sends it to the next person in line, and Macro #3 which returns the form to the sender if the form is rejected.

How do I adjust Macro #1 to keep the rest of the macros in the new workbooks it creates from each of the worksheets? Or maybe it is a specific setting in Excel itself that I need to change?

Some other points :
*this process really should be handled electronically through an approval workflow on our intranet but IT is beyond slow and it won't happen probably until sometime next year; nor will they open access to the workflow creation to non-IT dept. users so doing it myself is not possible (although it would be much simpler - workflows are easy, macros...not so much)
*my users do not know much more than the basics of excel so expecting them to understand and use the Send To routing recipient feature is probably not overly realistic
*each worksheet might have to go to a different Approver 1 so just sending the entire workbook is not a viable option - each sheet needs to be a separate attachment/e-mail; even if Approver #1 is the same for each worksheet, each worksheet must still be approved individually

Here is the macro I am using (Macro #1), it is a slightly modified macro from Ron DeBruin. Thank you in advance for your help!

Sub Send_to_Approver1()

'Working in Excel 2000-2013

Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object

TempFilePath = Environ$("temp") & "\"

If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143

Else
'You use Excel 2007-2010
FileExtStr = ".xlsm": FileFormatNum = 52

End If

With Application
.ScreenUpdating = False
.EnableEvents = False

End With

Set OutApp = CreateObject("Outlook.Application")

For Each sh In ThisWorkbook.Worksheets
If sh.Range("G77").Value Like "?*@?*.?*" Then
sh.Copy
Set wb = ActiveWorkbook
TempFileName = sh.Range("D27").Value & " - IPCN - " _
& Format(Now, "yy-mmm-dd")

Set OutMail = OutApp.CreateItem(0)

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.to = sh.Range("G77").Value
.CC = ""
.BCC = ""
.Subject = "IPCN Approval - " & TempFileName
.Body = "Please review and approve the attached IPCN."
.Attachments.Add wb.FullName
.Display


End With
On Error GoTo 0
.Close savechanges:=False
End With
Set OutMail = Nothing

Kill TempFilePath & TempFileName & FileExtStr

End If

Next sh
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True

End With


End Sub

 

[Andrew Poulsom]

When you copy a worksheet to a new workbook macros aren't copied unless they are in the module for the worksheet. So use SaveCopyAs to save a copy, open it, delete the sheets you don't want and resave before attaching it.

 

[fangfacekitty]

Thanks for the quick reply.

What I am trying to do is automate as much as possible and avoid the users having to save / open / modify / create individual e-mails / manually attach to each one / etc. That is the current situation and I am hoping to make the process simpler for them.

 

[Andrew Poulsom]

You can do all that I suggested in your VBA procedure.

 

[fangfacekitty]

OK, thanks again. I misunderstood.

I can figure out how to do this for the first sheet, but how would the macro in the original workbook know Sheet1 has already been sent and to repeat the process to only keep/send Sheet2, etc.?

 

[Andrew Poulsom]

You have a loop:

For Each sh In ThisWorkbook.Worksheets

so put all your code in there.