sending e-mail notice after any updating in spreadsheets
 

Hi Alex

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

Post back if you need more help


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



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

sending e-mail notice after any updating in spreadsheets
 

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" wrote:

Hi Alex

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

Post back if you need more help


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



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

sending e-mail notice after any updating in spreadsheets
 

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



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



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

Hi Alex

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

Post back if you need more help


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



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

sending e-mail notice after any updating in spreadsheets
 

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" wrote:

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



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



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

Hi Alex

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

Post back if you need more help


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



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

sending e-mail notice after any updating in spreadsheets
 

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.


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



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

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



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



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

Hi Alex

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

Post back if you need more help


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



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

sending e-mail notice after any updating in spreadsheets
 

Thanks a lot, Ron.

"Ron de Bruin" wrote:

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.


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



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

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



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



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

Hi Alex

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

Post back if you need more help


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



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