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
- In the first place, get access to the source Excel workbook that is containing the specific worksheet.
- Then, in MS Excel window, head to ‘File” menu and select “Options”.
- Next, in the “Excel Options” window, switch to “Trust Center” tab and click on the “Trust Center Settings” button.
- Subsequently, in the new window, shift to “Macro Settings” tab, under which you should select “Enable all macros”.
- After that, turn to “Privacy Options” tab and enable the “Remove personal information from file properties on save” feature.
- Then, click several “OK” to save these settings.
- After backing to the Excel workbook, access the specific worksheet.
- Right click on the worksheet name in the bottom sidebar and choose “View Code”.
- 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 = "firstname.lastname@example.org" .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
- 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.
- Finally, you can close this workbook.
- Now, you can open the previously saved Excel macro-enabled workbook.
- When you make any changes in the specific worksheet, you’ll get a message asking if to send an email notification.
- When you click “Yes” button, an Outlook email will be sent out the predefined recipients.
- 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.
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