| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| General mail failure when sending e-mail from Excel | Excel Discussion (Misc queries) | |||
| Sending E-mail | Excel Worksheet Functions | |||
| Sending e-mail - Ron, you still there? | Excel Programming | |||
| Help sending mail... | Excel Programming | |||
| Help sending mail... | Excel Programming | |||
Linear Mode
