How to send email reminder or notification if workbook is updated in Excel?
In some cases, you may need to trigger an email notification to a specified recipient if there are data updated in the workbook you have sent. This article will show you method to achieve it.
Please do as follows to send email reminder or notification if the workbook is updated.
1. In the workbook, open a specified worksheet you will send email notification based on the updated data inside.
2. Right-click the sheet tab, and select View Code from the right clicking menu. See screenshot:
2. In the Microsoft Visual Basic for Applications window, copy and paste the following code into the code window. See screenshot:
VBA code: Send email reminder or notification if workbook is updated
Private Sub Worksheet_Change(ByVal Target As Range) 'Updated by Extendoffice 2017/9/14 Dim xOutApp As Object Dim xMailItem As Object Dim xName As String Dim xYesOrNo As Integer On Error Resume Next Set xOutApp = CreateObject("Outlook.Application") Set xMailItem = xOutApp.CreateItem(0) xYesOrNo = MsgBox("Want to attach updated workbook in email?", vbInformation + vbYesNo, "KuTools For Excel") If xYesOrNo = 6 Then ActiveWorkbook.Save If xYesOrNo = 6 Then xName = ActiveWorkbook.FullName With xMailItem .To = "Email Address" .cc = "" .Subject = "email notification test" .Body = "Hi," & Chr(13) & Chr(13) & "File is now updated." If xYesOrNo = 6 Then .Attachments.Add xName .Display End With xMailItem = Nothing xOutApp = Nothing End Sub
Note: Replace the Email Address with the recipient email address in line .To = "Email Address". And change the Cc, Subject as well as body fields in the VBA code as you need.
3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.
4. After editing a cell in the worksheet, a Kutools for Excel dialog box will pops up as below screenshot shown. If you want to attach the updated workbook in the email, please click the Yes button. If not, click the No button.
5. Then an email is created automatically with or without the updated workbook attached. And all specified fields are also listed in the email. Please click the Send button to send it.
Note: The VBA code is only working when you use Outlook as your email program.
- How to automatically send email based on cell value in Excel?
- How to send an email through Outlook when workbook is saved in Excel?
- How to send email if a certain cell is modified in Excel?
- How to send email if button is clicked in Excel?
- How to send email if due date has been met in Excel?