Recently I ran across a situation where i had an Excel workbook that was on a shared drive, this file would get updated by different employees on any given time of the day. I need it a way to alert someone in management every time this file was amended. This was my solution:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim OutApp As Object Dim OutMail As Object Const SendTo As String = "<span style="color: #3366ff;">davidy@Initech.com</span>" Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With OutMail .To = SendTo .Subject = ThisWorkbook.Name & " has been amended" .Body = " <span style="color: #3366ff;">The Workbook has been updated</span>!" .Send End With Set OutMail = Nothing Set OutApp = Nothing End Sub
This Visual basic code sends an email via Outlook every time the workbook has been updated. For this code to fully work you will need to edit your macro settings in Excel.
Edit Macro Security Settings:
Office 2003 - Tools > Macro > Security.
Office 2007 - Click the Microsoft Office Button , and then click Excel Options. In the Trust Center category, click Trust Center Settings, and then click the Macro Settings category.
To make this VB code work correctly, you will need to set your Macro Security level to Medium or lower.
Inserting the Code:
Simply right click on the worksheet tab and click View Code. This will open the Microsoft Visual Basic screen.
Once you have the Microsoft VB screen open you can paste the code and begin editing it.
Don't forget to add your email address and if you would like to, you can customized the message that would be display on the body of the email. Just look at the highlighted parts on the picture above.