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?
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 4 months agoSorry, I posted this in the wrong article. I will post in the correct article
- To post as a guest, your comment is unpublished.· 4 months agoHello there, The code works great. However, when a cell changes to below the threshold point via a macro operation that copies and pastes the lower value into the designated cell, the code doesn't seem to trigger.
Also, I would like to apply this code to several cells. Can I copy and paste the code several times and change each copied version to fit each cells' needs? Is there a better way to do that. I can't figure that out. Thank you!
- To post as a guest, your comment is unpublished.· 10 months agoI copied and pasted the code exactly as-is from this page, but when I make a change to my document I get the message "Syntax Error" and this line highlights: Dim xOutApp As Object. Any help would be appreciated.
- To post as a guest, your comment is unpublished.· 8 months agoHi Judy,
Would you provide the Excel version you are using? Thank you for your comment.
- To post as a guest, your comment is unpublished.· 1 years agoCan i CC more than person in this? Thanks
- To post as a guest, your comment is unpublished.· 11 months agoHi Leah,
Please enter the email addresses directly in the .cc="" line and separate them with semicolon, such as .cc = "AAA@gmail.com;BBB@gmail.com"
- To post as a guest, your comment is unpublished.· 1 years agoIf anyone found this page wondering how to send an email from Excel using CDO, I threw together a Google Doc -- How to Send Email from Excel using Gmail (https://docs.google.com/document/d/1u5VLzCApU3k4-9Vp9LEfqyFZ6u9tAY0avNPYN_1FsN4/edit?usp=sharing) with code on GitHub gist (https://gist.github.com/bergerjac/7355d4e528fa6c64a02dc494f3d241a1)