Hello Excellers welcome to another #MacroMonday blog post in my series of articles. Do you need to email a report from Excel Automatically? Maybe you want to email a colleague whenever you change a worksheet. Whatever the reason, VBA can help. In this post, we will show you how to use VBA to send an email from Excel. Let’s get started!
Step 1. Set The Object Reference To Microsoft Office Library.
Microsoft Outlook is a separate application from Excel. So, to reference Outlook to send the email using Excel VBA, a reference to the Outlook object library is necessary for the macro to work. So, the first step is to object reference to Microsoft Outlook 16.0 Object Library. Once this step is completed, the OutlookObject is available to reference in the macro.
- VBA | Tools | References
- Select Microsoft Outlook 16.0 Object Library
- Hit Ok
Step 2. Inserting A New Module And Naming The Macro
The second step in this macro is to insert a new module to store the VBA code. There are two options. The first option is to insert the new module into the current workbook or your personal macro workbook. As this macro is particular to this workbook, it makes sense to insert the new module there. If this code is to be used on multiple workbooks, then storing it in your personal macro workbook makes it available to re-use at any time in Excel. But, this macro will send an email update to colleagues on a regular report, so I insert the module into the current workbook. The name of this macro is SendMondayEmail.
Sub SendByEmail()
End Sub
The End Sub line of code is automatically inserted into the code window as the macro is named. Write all other lines of code between these two lines.
Step 3. Declaring Variables | Set Instance Of Object
So, the next step is to declare any variables for this macro. This declaration means that Excel allocates some memory to store these values. In this macro, the Outlook Application is declared and the actual email. Finally, the attachment to send is declared as a string. After each of the first two Dim statements, the instance of each object is also set. This makes sense, as we are writing a macro to send emails from Excel using VBA.
So, the email app will launch, and a new email will be ready to write.
Dim EmailApp As Outlook.Application
Set EmailApp = New Outlook.Application
Dim EmailItem As Outlook.MailItem
Set EmailItem = EmailApp.CreateItem(olMainItem)
Dim AttachSource As String
Step 4. Writing The Email Components
It is time to write the mail components to get the recipients, the body of the message and add that ever-so-important email attachment. The first part of the mail is to TO property, which is the email address. At this stage, add any CC and blank CC if required. In this example, I have used the same mail address for demonstration purposes for the VBA code. The Subject is set as well as the email message in the body section of the email. Finally, the code for attaching the current workbook is set.
EmailItem.To = “test@gmail.com”
EmailItem.CC = “test@gmail.com”
EmailItem.BCC = “test@gmail.com”
EmailItem.Subject = “MondayReport”
EmailItem.HTMLBody = “Hi,” & vbNewLine & vbNewLine & “Please find attached Monday Report” & _
vbNewLine & vbNewLine & _
“Regards,”
AttachSource = ThisWorkbook.FullName
Step 5. Sending The Email.
The last line of code (except the End Sub already entered by Excel at the beginning of the macro) is to send the email. Once Excel sends the email, the code ends. Feel free to take the code and test yourself to send your email using Excel VBA.
Sub SendMondayEmail()
Dim EmailApp As Outlook.Application
Set EmailApp = New Outlook.Application
Dim EmailItem As Outlook.MailItem
Set EmailItem = EmailApp.CreateItem(olMainItem)
Dim AttachSource As String
EmailItem.To = “test@gmail.com”
EmailItem.CC = “test@gmail.com”
EmailItem.BCC = “test@gmail.com”
EmailItem.Subject = “MondayReport”
EmailItem.HTMLBody = “Hi,” & vbNewLine & vbNewLine & “Please find attached Monday Report” & _
vbNewLine & vbNewLine & _
“Regards,”
AttachSource = ThisWorkbook.FullName
EmailItem.Send
End Sub
In this blog post, we showed you how to use Excel VBA to send an email with attachment. This can be a very helpful tool if you need to regularly send emails with attachments. We hope you found this tutorial helpful and that it will make your life a little easier. If you have any questions or feedback, please don’t hesitate to reach out to us. And be sure to sign up for our newsletter so you never miss another useful tip!
More Excel and VBA Tips.
So, if you want to read all of my macro and Excel tips then feel free to bookmark by FormulaFriday and MacroMonday pages. They are updated every week.
Also please join thousands of other Excellers, whoc receive my free Excel Newsletter. Three Free Excel tips very month direct to your inbox. No Spam.
Finally, if you like to watch Excel tip videos then head over to my YouTube channel with over one hundred Excel videos. Subcribe and get access immediately to my full playlist.
