CREATING E-MAIL ATTACHMENT WITH 'BeforeSave Events' IN CODE

My main macro pulls data from several .xls files into three worksheets in one file, processes that data, then depending on the value of a specified cell, sends up to three e-mails; each e-mail sent has one of the worksheets attached as an .xls file. All of this works fine.

I now want to make the e-mail sending process a two-way street: I want to make the outgoing e-mail attachment, in 'ThisWorkbook' module, contain 'BeforeSave Events' code that, after the user modifies the .xls file (explains why he/she received such e-mail), saves such modified .xls file to a specified location, and sends a return e-mail with such modified file as an attachment. Part of the problem in doing this is how to create the outgoing attachment; when it is saved, it wants to execute the 'BeforeSave Events' code.

I would appreciate any suggestions.
TIA,
Chuckles123

Hi

Diffecult, you don't know ?

Which mail program use the user ?
Is he enable macro's when he open the file
He will get a security warning

You can add code to the new workbook you create with Chip's code
See http://www.cpearson.com/excel/vbe.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chuckles123" <Chuckles123.1ul4ei_1125417937.7216@excelforum-nospam.com> wrote in message
news:Chuckles123.1ul4ei_1125417937.7216@excelforum-nospam.com...
>
> My main macro pulls data from several .xls files into three worksheets
> in one file, processes that data, then depending on the value of a
> specified cell, sends up to three e-mails; each e-mail sent has one of
> the worksheets attached as an .xls file. All of this works fine.
>
> I now want to make the e-mail sending process a two-way street: I want
> to make the outgoing e-mail attachment, in 'ThisWorkbook' module,
> contain 'BeforeSave Events' code that, after the user modifies the .xls
> file (explains why he/she received such e-mail), saves such modified
> xls file to a specified location, and sends a return e-mail with such
> modified file as an attachment. Part of the problem in doing this is
> how to create the outgoing attachment; when it is saved, it wants to
> execute the 'BeforeSave Events' code.
>
> I would appreciate any suggestions.
> TIA,
> Chuckles123
>
>
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
> View this thread: http://www.excelforum.com/showthread...hreadid=400471
>

The outgoing e-mails work fine. They do not use MS Outlook; they are sent via our SMTP Server utilizing MS CDO For Exchange 2000 Library as a Refrence. The e-mails are being received by the users into their MS Outlook e-mail box.

Security warnings for macros I do not think will be a problem. My macros are digitally signed using 'selfcert.exe' and I may have each user 'accept' this signature.

Below is my strategy so far:

My main macro pulls data from several .xls files into three worksheets in one file, processes that data, then depending on the value of a specified cell, sends up to three e-mails; each e-mail sent has one of the worksheets attached as an .xls file. All of this works fine.

Now, I want to utilize an .xls file (to be created) that will be stored on a shared drive. This file will contain, in the 'ThisWorkbook' module, 'BeforeSave Events' code that saves the file to a specified location and also sends an e-mail, with the Active Workbook as an attachment.

So, I need to create code that will copy each of the three worksheets created by my main macro (depending on the value of the specified cell, there will be 0, 1, 2, or 3 workbooks created). Each copy will be to an empty workbook that contains 'BeforeSave Events' code in the 'ThisWorkbook' module. I also need to insert code for such 'ThisWorkbook' module; I think I can do this. <-- only one tricky spot -- I need to pass a variable from my main macro to this 'ThisWorkbook' module (they will be in separate Projects).

A second tricky spot -- the 'BeforeSave Events' code wants to execute immedi-
ately after saving the first worksheet as an .xls file; this is the file meant to be the outgoing e-mail attachment. Help.

A little rationale for this: the main macro sends the 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 e-mail with the modified .xls file as an attachment.

Chuckles123

You can create a template (real template) workbook with the code and copy a worksheet in it and save it and mail

Use this in the events

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ThisWorkbook.Path <> "" Then
'your code
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chuckles123" <Chuckles123.1ul9yh_1125425147.8824@excelforum-nospam.com> wrote in message
news:Chuckles123.1ul9yh_1125425147.8824@excelforum-nospam.com...
>
> The outgoing e-mails work fine. They do not use MS Outlook; they are
> sent via our SMTP Server utilizing MS CDO For Exchange 2000 Library as
> a Refrence. The e-mails are being received by the users into their MS
> Outlook e-mail box.
>
> Security warnings for macros I do not think will be a problem. My
> macros are digitally signed using 'selfcert.exe' and I may have each
> user 'accept' this signature.
>
> Below is my strategy so far:
>
> My main macro pulls data from several .xls files into three worksheets
> in one file, processes that data, then depending on the value of a
> specified cell, sends up to three e-mails; each e-mail sent has one of
> the worksheets attached as an .xls file. All of this works fine.
>
> Now, I want to utilize an .xls file (to be created) that will be stored
> on a shared drive. This file will contain, in the 'ThisWorkbook'
> module, 'BeforeSave Events' code that saves the file to a specified
> location and also sends an e-mail, with the Active Workbook as an
> attachment.
>
> So, I need to create code that will copy each of the three worksheets
> created by my main macro (depending on the value of the specified cell,
> there will be 0, 1, 2, or 3 workbooks created). Each copy will be to an
> empty workbook that contains 'BeforeSave Events' code in the
> 'ThisWorkbook' module. I also need to insert code for such
> 'ThisWorkbook' module; I think I can do this. <-- only one tricky spot
> -- I need to pass a variable from my main macro to this 'ThisWorkbook'
> module (they will be in separate Projects).
>
> A second tricky spot -- the 'BeforeSave Events' code wants to
> execute immedi-
> ately after saving the first worksheet as an .xls file; this is the
> file meant to be the outgoing e-mail attachment. Help.
>
> A little rationale for this: the main macro sends the 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 e-mail with the modified .xls file as
> an attachment.
>
> Chuckles123
>
>
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
> View this thread: http://www.excelforum.com/showthread...hreadid=400471
>

I tried your suggested code.
Before my first post, I was using your 'Private Sub ...' code, in my 'ThisWorkbook' module of my 'template'.
Concerning your 'If ThisWorkbook.Path <> "" Then' code, will not this always result in a 'True' answer? (because there will always be a path to the .xls template file)

I want my template to do two things:

- to save the modified .xls attachment to a specified location; and
- to send an e-mail with the same modified file as an attachment.

I am having some difficulty coming up with an appropriate 'If' stmt to test for whether the .xls attachment is coming from the main macro or whether it is coming from an user explaining why he/she received such attachment. If it is coming from an user, then I do not want the template to 'do' the two things listed above (the module will be first fired by the main macro when the outgoing attachment is created).

Also, I would like to pass a variable from the main macro to the template. Any ideas?

Thanks again,
Chuckles123

Sentence SHOULD BE:

If it is coming from MAIN MACRO, then I do not want the template to 'do' the two things listed above (the module will be first fired by the main macro when the outgoing attachment is created).

Chuckles123

Here is a small example

If you open the workbook template with code it don't have a path so you can save it after you copy the sheet
into this template workbook without fire the events.

Create a template workbook (xlt) with all the code and save it as a template with the name test.

Run this code in your workbook
It open the template workbook, copy the first sheet in it and save/close the file

Sub test()
Dim wb1 As Workbook
Dim wb2 As Workbook
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(Application.TemplatesPath & "\test.xlt")
wb1.Worksheets(1).Copy after:= _
wb2.Sheets(wb2.Sheets.Count)
' do other things if you want

wb2.SaveAs "C:\ron.xls"
wb2.Close False

' Run your mail code
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chuckles123" <Chuckles123.1ulnug_1125443119.7871@excelforum-nospam.com> wrote in message
news:Chuckles123.1ulnug_1125443119.7871@excelforum-nospam.com...
>
> I tried your suggested code.
> Before my first post, I was using your 'Private Sub ...' code, in my
> 'ThisWorkbook' module of my 'template'.
> Concerning your 'If ThisWorkbook.Path <> "" Then' code, will not this
> always result in a 'True' answer? (because there will always be a path
> to the .xls template file)
>
> I want my template to do two things:
>
> - to save the modified .xls attachment to a specified location; and
> - to send an e-mail with the same modified file as an attachment.
>
> I am having some difficulty coming up with an appropriate 'If' stmt to
> test for whether the .xls attachment is coming from the main macro or
> whether it is coming from an user explaining why he/she received such
> attachment. If it is coming from an user, then I do not want the
> template to 'do' the two things listed above (the module will be first
> fired by the main macro when the outgoing attachment is created).
>
> Also, I would like to pass a variable from the main macro to the
> template. Any ideas?
>
> Thanks again,
> Chuckles123
>
>
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
> View this thread: http://www.excelforum.com/showthread...hreadid=400471
>

Ron,

This is not easy. I have spent many hours on this. Thanks very much for your help.

Your modified code is working, except for the File SaveAs in the .xlT file (ThisWorkbook module). And I am still dealing with the issue of passing a variable to the .xlT file -- this step may not be necessary.

Below is my code for the 'ThisWorkbook' module in the .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 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

'E-MAIL CODE YET TO BE INSERTED

Application.Quit
End

Else
'DO NOTHING
End If

End Sub

NOTES RE: ABOVE:
- I am using 'ThisWorkbook' here because the macro code is in this file;
- Everything works except the SaveAs when the user is modifying the .xls file attachment and e-mailing such modified file -- the SaveAs crashes Excel <-- Help;
- If ThisWorkbook.Path <> "" is working fine when the main macro is copying, and saving, worksheets to the .xlT file.

Below is my modified code for my main macro (stored in an .xlA file):

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

NOTES RE: ABOVE:
- Because this is an .xlA file, I am using 'ActiveWorkbook';
- I moved the location of the .xlT file;
- I am working with 3 worksheets; the above code is within a For i = 1 To 3 loop.

As I mentioned earlier, I have an issue with the variable 'myDate' in the .xlT file;
if I could edit the name of the modified .xlS file (replacing the text 'ACCT ADJUST' with the text 'COMMENTS'), it would eliminate the need to pass the 'myDate' variable. Any ideas?

Thanks again,
Chuckles123

How do you know that this folder exist on the users machine
myPath2 = "R:\PAS Income\PAS AND-OR ACCT ADJUSTMENTS\"

Don't use
Application.Quit

What if the users have other files open ?

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chuckles123" <Chuckles123.1uowip_1125594330.7363@excelforum-nospam.com> wrote in message
news:Chuckles123.1uowip_1125594330.7363@excelforum-nospam.com...
>
> Ron,
>
> This is not easy. I have spent many hours on this. Thanks very much
> for your help.
>
> Your modified code is working, except for the File SaveAs in the .xlT
> file (ThisWorkbook module). And I am still dealing with the issue of
> passing a variable to the .xlT file -- this step may not be necessary.
>
> Below is my code for the 'ThisWorkbook' module in the .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 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
>
> 'E-MAIL CODE YET TO BE INSERTED
>
> Application.Quit
> End
>
> Else
> 'DO NOTHING
> End If
>
> End Sub
>
> NOTES RE: ABOVE:
> - I am using 'ThisWorkbook' here because the macro code is in this
> file;
> - Everything works except the SaveAs when the user is modifying the
> xls file attachment and e-mailing such modified file -- the SaveAs
> crashes Excel <-- Help;
> - If ThisWorkbook.Path <> "" is working fine when the main macro is
> copying, and saving, worksheets to the .xlT file.
>
> Below is my modified code for my main macro (stored in an .xlA file):
>
> 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
>
> NOTES RE: ABOVE:
> - Because this is an .xlA file, I am using 'ActiveWorkbook';
> - I moved the location of the .xlT file;
> - I am working with 3 worksheets; the above code is within a For i = 1
> To 3 loop.
>
> As I mentioned earlier, I have an issue with the variable 'myDate' in
> the .xlT file;
> if I could edit the name of the modified .xlS file (replacing the text
> 'ACCT ADJUST' with the text 'COMMENTS'), it would eliminate the need to
> pass the 'myDate' variable. Any ideas?
>
> Thanks again,
> Chuckles123
>
>
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
> View this thread: http://www.excelforum.com/showthread...hreadid=400471
>

The R:\ drive is a shared drive. All of the users work at this location. In my main macro, the first line of code is 'Application.Visible = False'. This macro is run as a 'Scheduled Task' early in the morning. The objective is for nothing to appear on the users' screens (the single exception is buttons appearing and disappearing on the XP Taskbar as files are opened and closed). The only user interaction is via e-mails that are generated by the main macro and by the .xlT file.

Furthermore, running of the main macro is a separate instance of Excel and Application.Quit does not close .xlS files that users have open (I think).

JingleRock

Hi Chuckles123

Ahhhaa

I will create a working example after this weekend for testing and post it here.
You can try it then.

Must go now (weekend out with my friends)

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chuckles123" <Chuckles123.1upaef_1125612330.7148@excelforum-nospam.com> wrote in message
news:Chuckles123.1upaef_1125612330.7148@excelforum-nospam.com...
>
> The R:\ drive is a shared drive. All of the users work at this
> location. In my main macro, the first line of code is
> 'Application.Visible = False'. This macro is run as a 'Scheduled Task'
> early in the morning. The objective is for nothing to appear on the
> users' screens (the single exception is buttons appearing and
> disappearing on the XP Taskbar as files are opened and closed). The
> only user interaction is via e-mails that are generated by the main
> macro and by the .xlT file.
>
> Furthermore, running of the main macro is a separate instance of Excel
> and Application.Quit does not close .xlS files that users have open (I
> think).
>
> JingleRock
>
>
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
> View this thread: http://www.excelforum.com/showthread...hreadid=400471
>

Ron,

I hope you enjoyed the long weekend.

If I 'comment-out' my SaveAs stmt in the .xlT file, save it, and re-run the main macro, everything works perfectly.

What do you think about the feasibility of renaming:
'ACCT ADJUST - TFI 09-02-05.xlS' as
'COMMENTS - TFI 09-02-05.xlS' within the .xlT file?
If renaming works, then it would not be necessary to pass the variable 'myDate' from the main macro (.xlA file) to the .xlT file.

Chuckles123

P.S.: JingleRock is another username that I sometimes use.

hi Chuckles123

To be sure :
Is your main macro working now ?

Do the user have problems with the saveas in the Excel workbook that
you send to them if you use the SaveAs line

Is it working if you run the code in a macro to test ?
Is it crash after the SaveAs (is it saved in the folder ?)

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chuckles123" <Chuckles123.1uqr6i_1125680727.7663@excelforum-nospam.com> wrote in message
news:Chuckles123.1uqr6i_1125680727.7663@excelforum-nospam.com...
>
> Ron,
>
> I hope you enjoyed the long weekend.
>
> If I 'comment-out' my SaveAs stmt in the .xlT file, save it, and re-run
> the main macro, everything works perfectly.
>
> What do you think about the feasibility of renaming:
> 'ACCT ADJUST - TFI 09-02-05.xlS' as
> 'COMMENTS - TFI 09-02-05.xlS' within the .xlT file?
> If renaming works, then it would not be necessary to pass the variable
> 'myDate' from the main macro (.xlA file) to the .xlT file.
>
> Chuckles123
>
> P.S.: JingleRock is another username that I sometimes use.
>
>
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
> View this thread: http://www.excelforum.com/showthread...hreadid=400471
>

Ron,

My 'main macro' stored in a .xlA file (includes a procedure named 'Auto_Open') has worked, and continues to work, fine.

In the .xlT file, I 'commented-out' the SaveAs stmt, and added a Call to an e-mail procedure in Module1. I saved it (it consistently trys to add the digit '1' to the name of the file preceding .xlT, when saving), ran the main macro, opened an e-mail that the users would receive, clicked-on the Save icon, and an e-mail was sent and the current instance of Excel was shut down -- perfect.

In response to your question, when Excel crashes, the file is saved in the appropriate folder. So, my SaveAs stmt is sick -- any ideas?

Re: the variable 'myDate' -- because I have a procedure named 'Auto_Open' in my .xlA file, the macro goes berserk if I establish a Reference to the .xlA file in my .xlT file (the .xlA file is where 'myDate' is defined).

Chuckles123

Ron,

I opened a blank workbook and inserted a new module; I then pasted code from my .xlT file, including the SaveAs stmt without the leading apostrophe, and 'single-stepped' thru the code -- it worked fine.

So, there must be some negative interaction between the SaveAs stmt, the .xlT file, and maybe the Auto_Open procedure in the .xlA file.

Chuckles123

Hi

It is very diffecult to test this for me.

I suggest that you create a new thread with a basic example and maybe Tom, Dave or ? see your problem.

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chuckles123" <Chuckles123.1uy32n_1126023246.0568@excelforum-nospam.com> wrote in message
news:Chuckles123.1uy32n_1126023246.0568@excelforum-nospam.com...
>
> Ron,
>
> I opened a blank workbook and inserted a new module; I then pasted code
> from my .xlT file, including the SaveAs stmt without the leading
> apostrophe, and 'single-stepped' thru the code -- it worked fine.
>
> So, there must be some negative interaction between the SaveAs stmt,
> the .xlT file, and maybe the Auto_Open procedure in the .xlA file.
>
> Chuckles123
>
>
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
> View this thread: http://www.excelforum.com/showthread...hreadid=400471
>

Most of the above comments are synthesized in new thread.

Jingle123