Hi, I just had this problem and poked around until I found a solution, so here I am passing it on to you. This will automatically send an email to any address/addresses whenever the workbook is saved. The only problem I have with it is that if you save it, you get the prompt, and when you close, excel automatically prompts you again. As long as you don't do a redundant save then you won't get a redundant auto email notification :)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim answer As String
answer = MsgBox("This is where you put the text to prompt the user if he wants to save or not" vbYesNo, "here is the title of that box")
If answer = vbNo Then Cancel = True
If answer = vbYes Then
'open outlook type stuff
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
'add recipients
'newmsg.Recipients.Add ("Name Here")
newmsg.Recipients.Add ("sampleemailaddresshere@email.com")
'add subject
newmsg.Subject = "Subject line of auto email here"
'add body
newmsg.Body = "body of auto email here"
newmsg.Display 'display
newmsg.Send 'send message
'give conformation of sent message
MsgBox "insert confirmation box test here", , "title of confirmation box"
End If
'save the document
'Me.Worksheets.Save
End Sub
This works like a charm in Office 2010 as well.
I'm glad I stumbled upon this thread after long attempts at trying to close in on my solution. Sorry to dig up this old thread, but can you please tell this n00b in more detail how to enable notification mails when an excel file is updated?
Highly appreciated, Cheers!
This is an event "Workbook_BeforeSave" that is defined in "ThisWorkbook" module and is triggered when workbook is saved.. So if you open your excel file, then press ALT + F11 at the same time, you would be opening VB editor. If you press CTRL + R at same time, you would see the project explorer. There you should see that says "ThisWorkbook". You need to use the code there