Sending Email With VBA: Learn in 11 Simple and Easy Steps
Visual Basic for Applications or VBA is a powerful programming language yet easy and lets you automate Microsoft Excel to the fullest. One such automation is using VBA codes to integrate other Microsoft apps like Outlook, PowerPoint, and Word and increase productivity.
Sending email with VBA in Excel is one such integration that makes your life easier by automating spreadsheet or report mailing tasks. This tutorial will walk you through the steps of sending email with VBA from Excel in easy-to-follow steps.
Microsoft Outlook Object Library Activation
You need to set the send email object reference of Outlook since it’s an outside object for Excel VBA. Follow along with these steps:
- Save the existing Excel file as a Macro-enabled file by clicking on File > Save As > select a location and changing the Save as type.
- Press Alt+F11 to bring up the VBA code editor and click on Tools located on the top menu bar.
- From there, select References and then scroll the Available References list to find Microsoft Outlook 16.0 Object Library.
- Select the object and click Ok to exit the Available References list.
Sending Email With VBA: Write Codes and Execute
Now that you’ve enabled the Outlook object in Excel VBA, follow these steps to write the Excel macro and execute that:
- Make sure you’re on the VBA code editor interface. Now, look for the Insert tab on the top menu of Excel VBA.
- Click on Insert and then select Module to insert a module where you’ll be able to write VBA codes.
- Now, copy-paste the following VBA script into the newly-created module.
Sub sending_email_with_VBA() Dim EmailApp As Outlook.Application Dim Source As String Set EmailApp = New Outlook.Application Dim EmailItem As Outlook.MailItem Set EmailItem = EmailApp.CreateItem(olMailItem) EmailItem.To = "opsmanager@addictivetips.com" EmailItem.CC = "customercare@addictivetips.com" EmailItem.BCC = "databackup@addictivetips.com" EmailItem.Subject = "Customer order shipping status" EmailItem.HTMLBody = "Hi Team," & vbNewLine & vbNewLine & "PFA the spreadsheet for today's order status" & _ vbNewLine & vbNewLine & _ "Regards," & vbNewLine & _ "Tamal Das" Source = ThisWorkbook.FullName EmailItem.Attachments.Add Source EmailItem.Send End Sub
The above-mentioned code will send an email using your Outlook account to the email addresses mentioned in the code.
The email content is also available in the EmailItem.HTMLBody code line. Moreover, Excel VBA will automatically attach your source workbook to the email as an attachment.
You may make the following changes to personalize the addresses and email body text:
- Replace all the email addresses within the quote with your own Outlook contacts.
- Customize the EmailItem.Subject text inside the quote as you see fit.
- In the EmailItem.HTMLBody code, you can replace all the texts within the quotation mark using your own content.
- Now, run the VBA script by pressing F5.
For sending email with VBA successfully, you’ll need to configure Gmail account or the Outlook account in the standalone Outlook app of the Microsoft Office suite. Online browser-based Outlook won’t work with this VBA code.
Conclusion
Now that you’ve followed along with the aforementioned steps for sending email with VBA, it should be easier for you to automate mundane tasks in Excel. For example, let the spreadsheet file automatically send an email to you when collaborators update the worksheet. Also, send automated emails to customers when you update the order status column in Excel.
If you want more Excel automation using VBA, utilize the VBA timestamp coding to keep a log of spreadsheet changes.
