How to Send Email from Excel with Body Using a Macro (with Easy Steps)

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.

emailItemObj.Send

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

Notes

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,

emailItemObj.Attachments.Add ("D:\Exceldemy\workbook_1.xlsx")

Conclusion

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.

Get FREE Advanced Excel Exercises with Solutions!