How to get Excel to email changes upon Exit?

[chk]

Greetings

Has anyone managed to write a macro to get Excel to automatically
email the changes in a workbook to a list of email addresses upon
exiting the workbook? Or, any pointers to some resources that may
help?

A few people in my office need to regularly update a shared workbook
which tracks changes. We'd like to be email-notified of changes made
by anyone.

I've gone through Ron De Bruin's excellent page (Thanks, Ron!)
http://www.rondebruin.nl/sendmail.htm. The closest that fits my needs
is "Sending mail from Excel with CDO". But I think I need some
additional code to extract the "History" sheet to email; plus to
detect the Exit event to trigger the macro. Unfortunately, I'm no VB
expert and would be grateful for any help.

Thanks for any answers.

-Kek
Tropical Marine Science Institute, Singapore.

 

[Toby Erkson]

I can't help w/the History part and I'm no expert, either, but...

Use the workbook-level event "BeforeClose". So, in your VB Excel editor,
click on "Thisworkbook" and paste in the below code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Now, if the workbook hasn't been saved a "Do you want to save" window will
pop-up when the subroutine has FINISHED. Clicking Cancel leaves the workbook
open...not desirable! So, before the End Sub line you will want to save the
workbook thru VB (I recommend this method) OR...
make sure the end users know that they must save the workbook before exiting
it (never trust end users so this is NOT recommended ;-) OR...
set the Saved property to True (you wouldn't want to do this because the
workbook wouldn't save it's changes, it would only _think_ it did).

I used Ron's CDO page to help me send email to some users, bypassing Outlook's
security warning of sending email on behalf of someone else (very annoying for
automation tasks!).

HTH,
Toby Erkson
Oregon, USA

 

[Toby Erkson]

Not sure how experienced you are but just to be clear for others, the email
and saving code would be put into the BeforeClose subroutine ;-)

Toby Erkson
Oregon, USA

I can't help w/the History part and I'm no expert, either, but...

Use the workbook-level event "BeforeClose". So, in your VB Excel editor,
click on "Thisworkbook" and paste in the below code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

....

 

[Ron de Bruin]

You can use the Beforesave event to call the mail macro
See Chip Pearson's site about Events
http://www.cpearson.com/excel/events.htm

Post back if you need more help

 

[Toby Erkson]

Duh...Ron's is a better way, ignore mine

You can use the Beforesave event to call the mail macro
See Chip Pearson's site about Events
http://www.cpearson.com/excel/events.htm

Post back if you need more help

Toby Erkson
Oregon, USA

 

[chk]

Thanks very much, Toby and Ron for your very helpful suggestions.

I played around with just emailing the Active Sheet with the
BeforeClose event -- works for me quite nicely.

However, my quest is still incomplete. I still have problems with
Track Changes and the History sheet:

- After each Save, the History sheet disappears. How do I prevent
this? This is documented in the Help and I have to manually display
the History sheet via the Tools/TrackChanges/... menu every time. Is
there anyway to "permanently" retain the History sheet?

- The "SheetToHTML(sh As Worksheet)" seems to bomb with TrackChanges
turned on. I'm not sure but it appears as if the SheetToHTML function
cannot handle whatever Excel inserts in the workbook for TrackChanges.
I get an error popup window with just "400" or sometimes "[Microsoft
Visual Basic] Run-time error '1004': Application-defined or
object-defined error" in it.
Any cure for this?

- Instead of mailing the ActiveSheet, how do I modify the
CDO_Send_ActiveSheet_Body() code to mail a specific sheet?

Thanks for any responses.

-Kek
Singapore
Using MS Excel 2002 SP3
PS: I discovered a "Workbook_SheetChange" event which is triggered
upon every change in the worksheet, but this is not suitable for
obvious reasons.

 

[Ron de Bruin]

Hi chk

I never used Tools/TrackChanges so I can't help you with that.

- Instead of mailing the ActiveSheet, how do I modify the
CDO_Send_ActiveSheet_Body() code to mail a specific sheet?

Also on the CDO page
http://www.rondebruin.nl/cdo.htm#sheet


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

Thanks very much, Toby and Ron for your very helpful suggestions.

I played around with just emailing the Active Sheet with the
BeforeClose event -- works for me quite nicely.

However, my quest is still incomplete. I still have problems with
Track Changes and the History sheet:

- After each Save, the History sheet disappears. How do I prevent
this? This is documented in the Help and I have to manually display
the History sheet via the Tools/TrackChanges/... menu every time. Is
there anyway to "permanently" retain the History sheet?

- The "SheetToHTML(sh As Worksheet)" seems to bomb with TrackChanges
turned on. I'm not sure but it appears as if the SheetToHTML function
cannot handle whatever Excel inserts in the workbook for TrackChanges.
I get an error popup window with just "400" or sometimes "[Microsoft
Visual Basic] Run-time error '1004': Application-defined or
object-defined error" in it.
Any cure for this?

- Instead of mailing the ActiveSheet, how do I modify the
CDO_Send_ActiveSheet_Body() code to mail a specific sheet?

Thanks for any responses.

-Kek
Singapore
Using MS Excel 2002 SP3
PS: I discovered a "Workbook_SheetChange" event which is triggered
upon every change in the worksheet, but this is not suitable for
obvious reasons.

Duh...Ron's is a better way, ignore mine



Toby Erkson
Oregon, USA