How to send an email through Outlook when workbook is saved in Excel?
This article is talking about sending an email through Outlook when a specific workbook is saved in Excel. Please do as the tutorial shows.
For sending an email through Outlook when the workbook is saved in Excel, please do as follows.
1. Please save the workbook as an Excel Macro-Enabled Workbook at first. Click File > Save As. In the Save As dialog box, select a folder to save the workbook, name it in the File name box, select Excel Macro-Enabled Workbook from the Save as type drop-down list, and then click the Save button. See screenshot:
2. Open the Excel Macro-Enabled Workbook you have saved just now, press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, please double click ThisWorkbook in the left bar, then copy and paste the below VBA code into the ThisWorkbook code window. See screenshot:
VBA code: Send email when workbook is saved
Private Sub Workbook_AfterSave(ByVal Success As Boolean) 'Updated by Extendoffice 20181102 Dim xOutApp As Object Dim xMailItem As Object Dim xName As String On Error Resume Next Set xOutApp = CreateObject("Outlook.Application") Set xMailItem = xOutApp.CreateItem(0) xName = ActiveWorkbook.FullName With xMailItem .To = "Email Address" .CC = "" .Subject = "The workbook has been saved" .Body = "Hi," & Chr(13) & Chr(13) & "File is now updated." .Attachments.Add xName .Display '.send End With Set xMailItem = Nothing Set xOutApp = Nothing End Sub
Note: Please 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 simultaneously to close the Microsoft Visual Basic for Applications window.
From now on, when you update the workbook and save it, an email will be created automatically with updated workbook attached. Please click the Send button to send the email. See screenshot:
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 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?
- How to send email reminder or notification if workbook is updated in Excel?