My main macro is in an .xlA file; it operates on a 'host' .xlS file and pulls data from several other .xlS files into three worksheets in one file, processes that data, then depending on the value of a specified cell in each worksheet, sends up to three e-mails (I am referring to this process as 'First E-Mail'); each e-mail sent has a corresponding worksheet attached as an .xlS file. All of this works fine.

I am utilizing an .xlT file that is stored on a shared drive. This file contains, in the 'ThisWorkbook' module, 'BeforeSave Events' code that saves an .xlS file (worksheets are first copied to the .xlT file by the main macro), after it has been modified by an user, to a specified location and also sends an e-mail with such modified file as an attachment (I am referring to this process as 'Second E-Mail').

So, I have created code that will copy each of the three worksheets created by my main macro (depending on the value of a specified cell in each worksheet, there will be 0, 1, 2, or 3 workbooks created). Each copy will be to a corresponding empty worksheet in the .xlT file that contains 'BeforeSave Events' code in the 'ThisWorkbook' module.

This is a snippet, in a 'For i = 1 To 3' loop, from my main macro:

Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("R:\PAS Income\FUND ACCT RETURN E-MAIL.xlT")

wb1.Worksheets(i).Copy After:=wb2.Sheets(wb2.Sheets.Count)

'DELETE EMPTY WORKSHEET
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete

wb2.SaveAs "R:\PAS Income\PAS AND-OR ACCT ADJUSTMENTS\" & _
"ACCT ADJUST - " & ActiveSheet.Name & " " & Format(myDate, "mm-dd-yy") & ".xlS"

If i = 1 Then fName1 = ActiveWorkbook.Name
If i = 2 Then fName2 = ActiveWorkbook.Name
If i = 3 Then fName3 = ActiveWorkbook.Name

ActiveWorkbook.Close False


Below is my code in the 'ThisWorkbook' module of my .xlT file:

Option Explicit
Dim myPath2 As String
Dim myDate As Variant

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

'IF PATH EXISTS, CODE IS PROCESSED AS A RETURN E-MAIL (SAVES MODIFIED .XLS FILE & E-MAILS SUCH FILE AS ATTACHMENT)
'IF PATH DOES NOT EXIST, THE MAIN MACRO IS CREATING .XLS ATTACHMENT
If ThisWorkbook.Path <> "" Then
myPath2 = "R:\PAS Income\PAS AND-OR ACCT ADJUSTMENTS\"
myDate = Date - 1

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=myPath2 & "COMMENTS - " & ActiveSheet.Name & " " & Format(myDate, "mm-dd-yy") & ".xlS"
Application.DisplayAlerts = True

'THIS CALLS PROCEDURE FOR SENDING SECOND E-MAIL
Call Second_Email

Application.Quit
End

Else
'DO NOTHING
End If

End Sub

A little rationale for this: the main macro sends the First-E-mail with the appropriate workbook as an attachment; the user receives and opens the e-mail; opens the .xlS file; keys in his/her explanation for receipt of such e-mail; clicks on save; and the user is done. That's when the code in 'BeforeSave Events' kicks in, saves the modified .xlS file in the specified location, and sends the Second E-Mail with such modified .xlS file as an attachment.

PROBLEMS:
- In my .xlT file, if I delete the 'ThisWorkbook.SaveAs ...' stmt, everything works just as I intend. However, with such stmt, Excel crashes -- even though Excel crashes, the file is saved in the appropiate location, but no e-mail is sent. Help.
- Variable 'myDate' is defined in my main macro (it is 1, 2, 3, or 4 calendar days prior to Date; this date is next preceding business day). I want to include this variable in the 'ThisWorkbook.SaveAs Filename ...' stmt in my .xlT file; I do not want this variable to change once it is brought into the .xlT file. How can I do this? One potential solution would be to replace the text 'ACCT ADJUST' with the text 'COMMENTS' in the names of the respective files created in the main macro and in the .xlT file. How can I do this? (this would eliminate the need to pass 'myDate' from the .xlA file to the .xlT file; also, since a procedure in my main macro is named 'Auto_Open', if I include a Reference to the .xlA file in the .xlT file, the macro goes berserk).

Jingle123