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
>