This article illustrates 11 easy steps to send an email from Excel with a body using a macro. We’ll be using the Microsoft Outlook under the hood by accessing all the properties through the Microsoft Outlook Object Library from Excel. Let’s follow the complete step-by-step guide to send your first email from Excel.
Send Email from Excel with Body Using a Macro: 11 Easy Steps
In this section, we have demonstrated how you can send Email from your excel worksheet with body using a macro code using 11 specific steps.
Step 01: Set Object Reference to Microsoft Outlook
We’ll send emails from Excel through the Microsoft Outlook services. For this, we need to access outlook properties via VBA code in the visual basic editor. Let’s follow the steps to activate the Microsoft Outlook Object Library.
- Go to the Developer tab from the Excel Ribbon.
- Click on the Visual Basic option.
- In the visual basic window, choose the References option under the Tools.
- From the References window, click the checkbox for the “Microsoft Outlook 16.0 Object Library” and click the OK button to save the changes.
Step 02: Create a Subprocedure
Now we need to start a subprocedure in the visual basic editor. Let’s open a new module with the following step.
- Go to the Insert tab in the Visual Basic for Applications window.
- Choose the Module.
- In the visual basic editor, write the subprocedure.
Sub SendEmailFromExcelWithBody() End Sub
Step 03: Declare a Variable Referring to Outlook Object
In this step, we’ll declare a variable named emailExcel to refer to the outlook object. The line of code is-
Dim emailExcel As Outlook.Application
Step 04: Create a New Object Instance
As we’ve declared an object variable earlier, we need to create an instance of a new object. To create one put the following line of code.
Set emailExcel = New Outlook.Application
Step 05: Declare Variable for Email Sending Property
To write a mail message, we need to use the MailItem object (outlook) by declaring a new variable named emailItemObj. The line of code is here below.
Dim emailItemObj As Outlook.MailItem
We also need to create a new instance for a new mail by referring this variable to the previous variable (outlook object variable) as CreateItem.
Set emailItemObj = emailExcel.CreateItem(olMailItem)
Step 06: Assign Targeted Email Address
With the above settings, we can start writing emails using the emailItemObj variable. To assign the targeted email address to whom the email will be sent, we’ll use the “To” property.
emailItemObj.To = "[email protected]"
Step 07: Use of CC Property in Macro Code
As we’ve seen in the last step, we have access to all the properties to write an email. With the CC property, we can send the carbon copy of the email to our desired email addresses.
emailItemObj.CC = "[email protected]"
Step 08: Use of BCC Property in Macro Code
Similarly, if we need to set BCC i.e., send a carbon copy of the email to some other addresses, we can use the BCC property.
emailItemObj.BCC = "[email protected]"
Step 09: Write the Email Subject
Here, we’ll use MailItem.Subject (outlook) that represents the subject of the outlook item. The line of the code is-
emailItemObj.Subject = "Email from Excel with Body"
Read More: Macro to Send Email from Excel
Step 10: Write the Email Body
With the help of MailItem.HTMLBody property (outlook), we’ll write the body of the email.
emailItemObj.HTMLBody = "Dear Subscriber,"& vbNewLine & _ "New article published form Exceldemy on how to send email from Excel _ with body using a macro."
Note: By setting up the MailItem.BodyFormat property (outlook) to olFormatHTML, we can have the body text of the email in HTML format. For this, we need to add this line.
emailItemObj.BodyFormat = olFormatHTML emailItemObj.HTMLBody = "<HTML><BODY> <p>Hello Subscriber,<br> New article published from Exceldemy </p></BODY></HTML>"
Step 11: Send the Email
Finally, we have to call the Send method to send the email to the targeted address.
This is our final code,
Sub SendEmailFromExcelWithBody() Dim emailExcel As Outlook.Application Set emailExcel = New Outlook.Application Dim emailItemObj As Outlook.MailItem Set emailItemObj = emailExcel.CreateItem(olMailItem) emailItemObj.To = "[email protected]" emailItemObj.CC = "[email protected]" emailItemObj.BCC = "[email protected]" emailItemObj.Subject = "Email from Excel with Body" emailItemObj.HTMLBody = "Dear Subscriber," & vbNewLine & _ "New article published form Exceldemy." & vbNewLine & _ "How to send email from Excel with body using a macro" 'To use the HTML format in Body Text 'emailItemObj.BodyFormat = olFormatHTML 'emailItemObj.HTMLBody = "<HTML><BODY> <p>Hello Subscriber,<br> New article published from Exceldemy </p></BODY></HTML>" emailItemObj.Send End Sub
Read More: Send Email from Excel VBA without Outlook
We can add an attachment with the email using the Attachments.Add method (outlook). The source of the attachment file is the only argument we need to put inside the method. For example,
Now, we know how to send an email from Excel with body text using a Macro. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.