How to Auto Send an Outlook Email Notification when a Specific Excel Worksheet Is Updated

Every time when you update a specific Excel worksheet, if you always need to send an email notification to specific recipients, you can utilize the method introduced in this article.

Some users ask for a solution to let MS Excel automatically send an Outlook email notification to specific persons every time when they update a specific worksheet, in that they often forget to send such an email. Now, in the followings, we’ll guide how to achieve this function with VBA code.

Auto Send an Email when a Specific Excel Worksheet Is Updated

1. In the first place, get access to the source Excel workbook that is containing the specific worksheet.
2. Then, in MS Excel window, head to ‘File” menu and select “Options”.
3. Next, in the “Excel Options” window, switch to “Trust Center” tab and click on the “Trust Center Settings” button.
4. Subsequently, in the new window, shift to “Macro Settings” tab, under which you should select “Enable all macros”.
5. After that, turn to “Privacy Options” tab and enable the “Remove personal information from file properties on save” feature.
6. Then, click several “OK” to save these settings.
7. After backing to the Excel workbook, access the specific worksheet.
8. Right click on the worksheet name in the bottom sidebar and choose “View Code”.
9. After that, copy the following VBA code into the project of the specific sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nConfirmation As Integer
    Dim objNewWorkbook As Excel.Workbook
    Dim objNewWorksheet As Excel.Worksheet
    Dim objOutlookApp As Object
    Dim objMail As Object
 
    nConfirmation = MsgBox("Do you want to send an email notification about the sheet updating now?", vbInformation + vbYesNo, "Mail Sheet Updates")
 
    If nConfirmation = vbYes Then
       ActiveWorkbook.Save

       On Error Resume Next
       Set objOutlookApp = CreateObject("Outlook.Application")
       Set objMail = objOutlookApp.CreateItem(olMailItem)
  
       'Change the email details as per your needs
       With objMail
           .To = "test@datanumen.com"
           .Subject = "Email Notifying Sheet Updates"
           .Body = "Hi," & vbCrLf & vbCrLf & "The worksheet " & Chr(34) & ActiveWorkbook.Sheets(1).Name & Chr(34) & " in this Excel workbook attachment is updated."
           'Attach this workbook
           .Attachments.Add ActiveWorkbook.FullName
           .Send
      End With
    End If
End Sub

10. After that, you should save this workbook as Excel Macro-Enabled workbook.

• First, close the “Microsoft Visual Basic for Applications” window.
• Then, click “File” in the left upper corner.
• Next, choose “Save As”.
• In the dialog box, opt for “Excel Macro-Enabled Workbook” in “Save as type” field.
• Lastly, click “Save” button.

11. Finally, you can close this workbook.
12. Now, you can open the previously saved Excel macro-enabled workbook.
13. When you make any changes in the specific worksheet, you’ll get a message asking if to send an email notification.
14. When you click “Yes” button, an Outlook email will be sent out the predefined recipients.
15. This email will look like the screenshot below:

Repair Damaged Outlook Data File

Maybe you’ve ever encountered Excel file corruption. Actually, Outlook data file is same vulnerable as Excel file. For instance, improperly closing Outlook can lead to PST file corruption readily. Therefore, it’s essential to take actions to safeguard Outlook file, including making regular data backups as well as preparing a robust PST fix tool, such as DataNumen Outlook Repair. This utility is powerful enough to repair PST issues and recover PST data.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql fix and outlook repair software products. For more information visit www.datanumen.com