sending e-mail notice after any updating in spreadsheets

[Guest]

I have a workbook with many spreadsheets (160).
How could I create some code to be notified by e-mail (MOffice Outlook) that
some changes made there (preferably with referring to a specific spreadsheet).

Thanks

 

[Ron de Bruin]

Hi Alex

Start here
http://www.rondebruin.nl/mail/change.htm

Post back if you need more help

 

[Guest]

Thanks a lot, Ron.
It's working perfectly.

How could I send this notice only if the changes in the workbook is being
saved (not on change event - because it triggers the code on any changes
made)?

How could I refer to a specific spreadsheet (I have 160) in this workbook
about the changes?

Thanks

 

[Ron de Bruin]

Hi Alex

Try this in the Thisworkbook module of your test workbook
It will only run the code if you save the file if you have changed a cell in "Sheet1"


Public shchange As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If shchange = True Then
MsgBox "your code"
End If
shchange = False
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Sheet1" Then shchange = True
End Sub

 

[Guest]

Thanks again, Ron.

Could you please clarify why when I sending e-mail (after .Send) and
choosing 'No' from the Microsoft Office Outlook message window: "A program is
trying to automatically send e-mail on your behalf ..." I'm getting the
run-time error message "Application-defined or object defined error".

I did before the similar code for e-mail sending and it was Ok on it. The
difference was only it wasn't a late binding.

Thanks

 

[Ron de Bruin]

Hi Alex

You can use a On Error Resume Next to avoid this error

On Error Resume Next
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc


Or from
http://www.rondebruin.nl/mail/prevent.htm

Instead of .Send in the code examples you can use this two lines.
SendKeys is not always reliable, but I have good results with it.
Note: the S is from Send, if you not use a English version you must change this letter.

..Display
Application.SendKeys "%S"

Now you know why I like mailing with CDO code.

 

[Guest]

Thanks a lot, Ron.

Hi Alex

You can use a On Error Resume Next to avoid this error

On Error Resume Next
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc


Or from
http://www.rondebruin.nl/mail/prevent.htm

Instead of .Send in the code examples you can use this two lines.
SendKeys is not always reliable, but I have good results with it.
Note: the S is from Send, if you not use a English version you must change this letter.

..Display
Application.SendKeys "%S"

Now you know why I like mailing with CDO code.