[SOLVED] sending e-mail notice after any updating in spreadsheets

[Alex]

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


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



"Alex" <Alex@discussions.microsoft.com> wrote in message news:EE2B9324-A1CF-4390-B475-463A8AE31587@microsoft.com...
>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

[Alex]

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" <Alex@discussions.microsoft.com> wrote in message news:EE2B9324-A1CF-4390-B475-463A8AE31587@microsoft.com...
> >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

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" <Alex@discussions.microsoft.com> wrote in message news:B2ED9898-8B90-49AF-B06F-798235DEF31B@microsoft.com...
> 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" <Alex@discussions.microsoft.com> wrote in message news:EE2B9324-A1CF-4390-B475-463A8AE31587@microsoft.com...
>> >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
>>
>>
>>

[Alex]

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" <Alex@discussions.microsoft.com> wrote in message news:B2ED9898-8B90-49AF-B06F-798235DEF31B@microsoft.com...
> > 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" <Alex@discussions.microsoft.com> wrote in message news:EE2B9324-A1CF-4390-B475-463A8AE31587@microsoft.com...
> >> >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

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" <Alex@discussions.microsoft.com> wrote in message news:F9EF22D7-30A7-4EA4-BC1A-0571836779AA@microsoft.com...
> 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" <Alex@discussions.microsoft.com> wrote in message news:B2ED9898-8B90-49AF-B06F-798235DEF31B@microsoft.com...
>> > 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" <Alex@discussions.microsoft.com> wrote in message news:EE2B9324-A1CF-4390-B475-463A8AE31587@microsoft.com...
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>

[Alex]

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" <Alex@discussions.microsoft.com> wrote in message news:F9EF22D7-30A7-4EA4-BC1A-0571836779AA@microsoft.com...
> > 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" <Alex@discussions.microsoft.com> wrote in message news:B2ED9898-8B90-49AF-B06F-798235DEF31B@microsoft.com...
> >> > 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" <Alex@discussions.microsoft.com> wrote in message news:EE2B9324-A1CF-4390-B475-463A8AE31587@microsoft.com...
> >> >> >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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Chuckles123]

Ron,
I am trying to use Outlook to send an e-mail from code in an .xlA file. Two issues:

- I am trying to prevent the pop-up with the exploding progress bar and the question: 'Do you want to allow this?' I saw your post plus your link and tried 'On Error Resume Next' -- that did not work. I also tried Application.SendKeys "%S" -- that did not work. I also tried SendKeys with "{TAB}{TAB}{ENTER}" in an attempt to respond to the pop-up (after the progress bar stops) -- that did not work. Any more ideas?

- I want to attach a workbook file, possibly the ActiveWorkbook, to the e-mail. I tried 'With .Attachments.Add ...' -- that did not work. Any ideas?

TIA,
Chuckles123