Mail excel file on close (Excel 00)

Topic

New Reply

Ok, I have a procedure for the workbook_close event…it automatically opens an outlook message with the workbook attached. my problem is that my blank template gets sent, not the completed workbook. Where do I /how do I write the save procedure so that my completed file gets sent, and not my blank template? attached below is the code i have:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject(“Outlook.Application”)
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = “hazlettjm@gcemnf-wiraq.usmc.mil”
.CC = “”
.BCC = “”
.Subject = “Morning Report”
.Body = “”
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Reply | Quote

Replies

Are you sure this is a good idea? If it works, you’ll get an e-mail if the user opens and closes the workbook. You open your mail, open the attached workbook and close it, and hey presto, you send a mail to yourself, unless you disabled macros when opening the workbook.

The problem with your code is that it sends the saved version of the workbook, not the version that is open. To send the opened version, use

ActiveWorkbook.SendMail “hazlettjm@gcemnf-wiraq.usmc.mil”, “Morning Report”

Reply | Quote

[indent]

---

Are you sure this is a good idea? If it works, you’ll get an e-mail if the user opens and closes the workbook. unless you disabled macros when opening the workbook.

---

[/indent]
I have a MsgBox that shows on the opening of the workbook that says “If this has already been sent, close the email dialogue that will open upon closing this workbook.” Or something to that effect. This is necessary because the report must be sent by 8am daily, 7 days a week. Some of my clerks forget to mail it, and the kick in the face comes back at me. This report is only referenced once or twice a day at most, so it is practical to leave the email tag on there.
[indent]

---

You open your mail, open the attached workbook and close it, and hey presto, you send a mail to yourself,

---

[/indent]
As a remedy to “presto”, I added the line .Display, giving the user the option to close the email if it’s already been sent.

[indent]

---

The problem with your code is that it sends the saved version of the workbook, not the version that is open. To send the opened version, use

ActiveWorkbook.SendMail “hazlettjm@gcemnf-wiraq.usmc.mil”, “Morning Report”

---

[/indent]
I’m confused on this part. In my code, I have the line “.Attachments.Add ActiveWorkbook.FullName“. I figured that would work, however, it doesn’t.

Hope that clarifies

Reply | Quote

The SendMail instruction would replace the entire code you have:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.SendMail “hazlettjm@gcemnf-wiraq.usmc.mil”, “Morning Report”
End Sub

SendMail sends a copy of the workbook as currently displayed on your screen, including unsaved changes. The code you had doesn’t look at the displayed version of the workbook, it sends the version as last saved to disk.

Reply | Quote

Hans,
Is it possible to display the mail instead of “presto”?

Reply | Quote

No, that’s a downside of SendMail. You could ask the user whether the workbook should be sent:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox(“Do you want to send the workbook?”, vbYesNo + vbQuestion) = vbYes Then
ActiveWorkbook.SendMail “hazlettjm@gcemnf-wiraq.usmc.mil”, “Morning Report”
End If
End Sub

Reply | Quote

Genius. Sheer genius. Thanks.

Reply | Quote