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.
- 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.
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?
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.
To post as a guest, your comment is unpublished.· 4 months agoHow would this be handled for an Office 365 document. It is automatically saving.
To post as a guest, your comment is unpublished.· 1 years agoHi, nice article! One thing ive been trying to achieve with this is to attach the current state of the workbook to the email.
At the moment, it only sends the original state of the file and doesnt include any changes the user would have made.
Any ideas on how to implement this using a macro?
To post as a guest, your comment is unpublished.· 1 years agoHi Chris,
The code has been updated with the problem solved, please have a try. Thank you for your comment.
To post as a guest, your comment is unpublished.· 8 months agoHi Chris,
I have chanced upon the same issue.
Currently the codes are used in the "beforesave" module.
Which means that the email will send the spreadsheet that is before saved.
There is another module "aftersave".
I applied the code in this module and it worked like a charm.
To post as a guest, your comment is unpublished.· 1 years agoDear all, I would like to ask how to attach functional link to certain folder on server, if I paste the link, it appears in the workbook just like plain text and so it doensn't work in received e-mail, how can I turn it into link, so recepiants can click on it?
I would like to use this way instead sending enclosed excel file.
Thanks for advice
To post as a guest, your comment is unpublished.· 9 months agoI put this in the body of the email and it worked for me...
To post as a guest, your comment is unpublished.· 1 years agoHi Robert,
Sorry can’t help with this, welcome to post any question about Excel to our forum: https://www.extendoffice.com/forum.html. You will get more Excel supports from our professional or other Excel fans.
To post as a guest, your comment is unpublished.· 1 years agoGracias. Consulta: utilizando esta misma rutina, como podría enviar el correo a un correo específico dependiendo el valor de otra celda?
To post as a guest, your comment is unpublished.· 1 years agoHow to automate the email notification in VBA based on Date range, without having to see the pop up for security permission to allow VBA to send the email.