Get Email notifications when Excel workbook has been amended.

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 &amp; " 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.

Macro Security in Excel 2003

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.

Click View Code to open the Microsoft Visual Basic screen

Once you have the Microsoft VB screen open you can paste the code and begin editing it.

 

A view of the Microsoft Visual Basic screen

 

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.

 


Tagged under: excel, email notification, amended, updated, macro, visual basic, office