Once a workbook is modified send an email notification 2

[Avdotcom]

I want to have an email notification sent once a "public" workbok is modified and saved.

Does Excel already have this feature built-in or do I need to use VBA?

Thank you,

Avdotcom

 

[RobBroekhuis]

You need to use VBA. Probably easiest using the
workbook_beforeclose and workbook_beforesave event handlers. You can use a public variable to keep track of things:

public BookChanged as boolean

sub workbook_beforesave
if not thisworkbook.saved then BookChanged=true
end sub

sub workbook_beforeclose
if not thisworkbook.saved or BookChanged then
..send an email
end if
end sub

The code for sending an email you can easily grab from many recent posts on this forum.
Rob

 

[Beeps]

I had some time and tried this out. Worked pretty good on my set up, you may need to tweak some stuff and add your own email addresses of course but put this in the worksheet module:
***********************************************************
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Dim Email, cc, ref, origin, destination, notes, strBody As String

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem



'**********************************************************************
Email = "" '[red]insert primary email addresses here [/red]
cc = " " 'insert emails here


Set objOutlook = New Outlook.Application
Set objEmail = objOutlook.CreateItem(olMailItem)



'Body of email***********************************************************
strBody = "User " & Application.UserName & " has modified " & Application.Activeworkbook.Name
strBody = strBody & " at " & Now()



'Address and Send the email
With objEmail
.To = Email
.cc = cc
.Subject = "Excel File " & Application.Activeworkbook.Name & " has been modified"
.Body = strBody
.Send
End With

'Cleanup
Set objEmail = Nothing
Set objOutlook = Nothing

End Sub

That should do it. Please let me know if you're still having problems or questions. Thanks

 

[Beeps]

Oh yeah, make sure you set your references to the Outlook object model

 

[scottsek]

Excel has a feature built-in that will allow you send the workbook, which I don't think you want. You just want to send a "note" that it's been saved. If so, you'll have to use VBA.

If you use VBA, there's a pseudo-cheating way that uses the Excel send feature to send a new (and therefore smaller than your public book) to the recipients. With this method, you could put a short message in the "Subject". Since you didn't mention which email program you're using, I wanted to try to find a non-Outlook solution.

If you use Outlook, you could have the workbook "reference" Outlook and use Outlook to send a short email with whatever you want to say.

Here's the code for the first solution which needs to be placed in the "ThisWorkbook" module in the VBA editor (if you don't know how to do this, post again and I'll give you more detailed instructions).:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'fakeBook is the book that will be sent, it's just
'a blank workbook
Dim fakeBook As Workbook
'asks the user if they want to save and send email
response = MsgBox("Do you want to save and send email notification", vbYesNo)
'if they answer no, then abort save
If response = vbNo Then
Cancel = True
Else
'create the fake workbook
Set fakeBook = Workbooks.Add
'send to recipient with msg in subj line
fakeBook.SendMail "yourrecipient@whereever.com", Me.Name & " has been changed and saved."
'close the fake workbook
fakeBook.Close savechanges:=False
End If
End Sub

If you have any questions, please post again.

HTH,
Scott

 

[RobBroekhuis]

The approach by Beeps should work. I would still put the email generation bit in the _beforeclose handler, to allow the user to do intermediate saves without generating an email for each save, as in my post above.
Rob