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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.· 2 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.· 2 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.· 8 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.· 6 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.· 10 months agoCan i CC more than person in this? Thanks
- To post as a guest, your comment is unpublished.· 9 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)