Excel - Be notified by mail when shared workbook is updated

March 2017


Issue

I have an Excel workbook stored on a network drive and used by multiple users. I need a macro that will send a notification (by mail) each time someone updates the the Excel sheet. How can this be implemented?

Solution

This macro will automatically send an email to any address/addresses whenever the workbook is saved:

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



Thanks to shark2000br for this tip.

Related


Published by deri58.
This document, titled "Excel - Be notified by mail when shared workbook is updated," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).