Sending an email when a file is opened.

[misterg]

Quick question for the guru's...

I need to be notified when a spreadsheet is opened.
Emailing me would be excellent.

I have come up with the following code but it doesnt seem to work:

Set myOlApp = CreateObject("Outlook.Application")
Set myitem = myOlApp.CreateItem(olMailItem)
With myitem
.to = "email address"
.Subject = "File opened"
.body = "The file has now been opened by the sender of the email"
End With
End Sub

This would be placed in the Open section of This Workbook.

It doesnt seem to work however...
I am integrating with Outlook 2000 from Excel 97.
Any clues?

G

[dk]

Hi,

2 things:-

First, have you set a reference to the Microsoft Outlook object library? If not, go to Tools, References in the VB Editor and check the Outlook library.

Secondly, you have to call the Send method to actually send the message i.e.






Private Sub Workbook_Open()

Set myOlApp = CreateObject("Outlook.Application")

Set MyItem = myOlApp.CreateItem(olMailItem)

With MyItem

*** .To = "anyone@anywhere.com"

*** .Subject = "File opened"

*** .Body = "The file has now been opened by the sender of the email"

*** .Send*******
'You need this bit

End With

End Sub

HTH,
Dan

[misterg]

Dan,

Many thanks for refining the code...works a treat!

Cheques in the post!

Thanks

G