My main macro pulls data from several .xls files into three worksheet
in one file, processes that data, then depending on the value of
specified cell, sends up to three e-mails; each e-mail sent has one o
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 wan
to make the outgoing e-mail attachment, in 'ThisWorkbook' module
contain 'BeforeSave Events' code that, after the user modifies the .xl
file (explains why he/she received such e-mail), saves such modifie
.xls file to a specified location, and sends a return e-mail with suc
modified file as an attachment. Part of the problem in doing this i
how to create the outgoing attachment; when it is saved, it wants t
execute the 'BeforeSave Events' code.

I would appreciate any suggestions.
TIA,
Chuckles12

--
Chuckles12
-----------------------------------------------------------------------
Chuckles123's Profile: http://www.excelforum.com/member.php...fo&userid=1494
View this thread: http://www.excelforum.com/showthread.php?threadid=40047

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" wrote in message
...

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


--
Chuckles123
------------------------------------------------------------------------
Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
View this thread: http://www.excelforum.com/showthread...hreadid=400471

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" wrote in message
...

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


--
Chuckles123
------------------------------------------------------------------------
Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
View this thread: http://www.excelforum.com/showthread...hreadid=400471

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


--
Chuckles123
------------------------------------------------------------------------
Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
View this thread: http://www.excelforum.com/showthread...hreadid=400471

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" wrote in message
...

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