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.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
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?
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 days agoCan i CC more than person in this? Thanks
To post as a guest, your comment is unpublished.· 10 months 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)