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?
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 ("email@example.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"