C
Chuckles123
My main macro is in an .xlA file; it operates on a 'host' .xlS file an
pulls data from several other .xlS files into three worksheets in on
file, processes that data, then depending on the value of a specifie
cell in each worksheet, sends up to three e-mails (I am referring t
this process as 'First E-Mail'); each e-mail sent has a correspondin
worksheet attached as an .xlS file. All of this works fine.
I am utilizing an .xlT file that is stored on a shared drive. Thi
file contains, in the 'ThisWorkbook' module, 'BeforeSave Events' cod
that saves an .xlS file (worksheets are first copied to the .xlT fil
by the main macro), after it has been modified by an user, to
specified location and also sends an e-mail with such modified file a
an attachment (I am referring to this process as 'Second E-Mail').
So, I have created code that will copy each of the three worksheet
created by my main macro (depending on the value of a specified cell i
each worksheet, there will be 0, 1, 2, or 3 workbooks created). Eac
copy will be to a corresponding empty worksheet in the .xlT file tha
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 RETUR
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 (SAVE
MODIFIED .XLS FILE & E-MAILS SUCH FILE AS ATTACHMENT)
'IF PATH DOES NOT EXIST, THE MAIN MACRO IS CREATING .XL
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-mai
with the appropriate workbook as an attachment; the user receives an
opens the e-mail; opens the .xlS file; keys in his/her explanation fo
receipt of such e-mail; clicks on save; and the user is done. That'
when the code in 'BeforeSave Events' kicks in, saves the modified .xl
file in the specified location, and sends the Second E-Mail with suc
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, Exce
crashes -- even though Excel crashes, the file is saved in th
appropiate location, but no e-mail is sent. Help.
- Variable 'myDate' is defined in my main macro (it is 1, 2, 3, or
calendar days prior to Date; this date is next preceding business day)
I want to include this variable in the 'ThisWorkbook.SaveAs Filenam
...' stmt in my .xlT file; I do not want this variable to change onc
it is brought into the .xlT file. How can I do this? One potentia
solution would be to replace the text 'ACCT ADJUST' with the tex
'COMMENTS' in the names of the respective files created in the mai
macro and in the .xlT file. How can I do this? (this would eliminat
the need to pass 'myDate' from the .xlA file to the .xlT file; also
since a procedure in my main macro is named 'Aut
pen', if I include
Reference to the .xlA file in the .xlT file, the macro goes berserk).
Jingle123
pulls data from several other .xlS files into three worksheets in on
file, processes that data, then depending on the value of a specifie
cell in each worksheet, sends up to three e-mails (I am referring t
this process as 'First E-Mail'); each e-mail sent has a correspondin
worksheet attached as an .xlS file. All of this works fine.
I am utilizing an .xlT file that is stored on a shared drive. Thi
file contains, in the 'ThisWorkbook' module, 'BeforeSave Events' cod
that saves an .xlS file (worksheets are first copied to the .xlT fil
by the main macro), after it has been modified by an user, to
specified location and also sends an e-mail with such modified file a
an attachment (I am referring to this process as 'Second E-Mail').
So, I have created code that will copy each of the three worksheet
created by my main macro (depending on the value of a specified cell i
each worksheet, there will be 0, 1, 2, or 3 workbooks created). Eac
copy will be to a corresponding empty worksheet in the .xlT file tha
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 RETUR
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 (SAVE
MODIFIED .XLS FILE & E-MAILS SUCH FILE AS ATTACHMENT)
'IF PATH DOES NOT EXIST, THE MAIN MACRO IS CREATING .XL
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-mai
with the appropriate workbook as an attachment; the user receives an
opens the e-mail; opens the .xlS file; keys in his/her explanation fo
receipt of such e-mail; clicks on save; and the user is done. That'
when the code in 'BeforeSave Events' kicks in, saves the modified .xl
file in the specified location, and sends the Second E-Mail with suc
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, Exce
crashes -- even though Excel crashes, the file is saved in th
appropiate location, but no e-mail is sent. Help.
- Variable 'myDate' is defined in my main macro (it is 1, 2, 3, or
calendar days prior to Date; this date is next preceding business day)
I want to include this variable in the 'ThisWorkbook.SaveAs Filenam
...' stmt in my .xlT file; I do not want this variable to change onc
it is brought into the .xlT file. How can I do this? One potentia
solution would be to replace the text 'ACCT ADJUST' with the tex
'COMMENTS' in the names of the respective files created in the mai
macro and in the .xlT file. How can I do this? (this would eliminat
the need to pass 'myDate' from the .xlA file to the .xlT file; also
since a procedure in my main macro is named 'Aut
pen', if I includeReference to the .xlA file in the .xlT file, the macro goes berserk).
Jingle123