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.
Send email reminder or notification if workbook is updated with VBA code
Send email reminder or notification if workbook is updated with VBA code
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 SubNote: 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.
Related articles:
- 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?
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Spreadsheets: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
