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.
Send an email through Outlook when workbook is saved with VBA code
Send an email through Outlook when workbook is saved with VBA code
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 SubNote: 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.
Related articles:
- 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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
