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

[Chuckles123]

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

 

[Ron de Bruin]

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

 

[Chuckles123]

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

 

[Ron de Bruin]

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

 

[Chuckles123]

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]

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

 

[Ron de Bruin]

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

 

[Chuckles123]

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

 

[Ron de Bruin]

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 ?

 

[Chuckles123]

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

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

JingleRoc

 

[Ron de Bruin]

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)

 

[Chuckles123]

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.

 

[Ron de Bruin]

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 ?)

 

[Chuckles123]

Ron,

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

In the .xlT file, I 'commented-out' the SaveAs stmt, and added a Cal
to an e-mail procedure in Module1. I saved it (it consistently trys t
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 curren
instance of Excel was shut down -- perfect.

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

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

Chuckles12

 

[Chuckles123]

Ron,

I opened a blank workbook and inserted a new module; I then pasted cod
from my .xlT file, including the SaveAs stmt without the leadin
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 Autpen procedure in the .xlA file.

Chuckles12

 

[Ron de Bruin]

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.

 

[Chuckles123]

Most of the above comments are synthesized in new thread.

Jingle12