How to Send an Email with Body from Excel Using a Macro

Step 1 – Set the Object Reference to Microsoft Outlook

Go to the Developer tab on the Ribbon.
Click Visual Basic.

In the visual basic window, choose Tools and select References option.

In the References window, check Microsoft Outlook 16.0 Object Library and click OK to save.

Step 2 – Create a Subprocedure

Go to the Insert tab in the Visual Basic for Applications window.
Choose Module.

In the visual basic editor, enter the subprocedure.

Sub SendEmailFromExcelWithBody()
End Sub

Step 3 – Declare a Variable Referring to the Outlook Object

Enter the following code to declare a variable named emailExcel and refer to the outlook object.

Dim emailExcel As Outlook.Application

Step 4 – Create a New Object Instance

Enter the following code to create an instance of a new object.

Set emailExcel = New Outlook.Application

Step 5 – Declare a Variable for the Email Sending Property

Enter this code to use the MailItem object (outlook) by declaring a new variable named emailItemObj.

Dim emailItemObj As Outlook.MailItem

To refer this variable to the previous one as CreateItem, enter the code below.

Set emailItemObj = emailExcel.CreateItem(olMailItem)

Step 6 – Assign a Targeted Email Address

Enter this code to assign the targeted email address, using the “To” property.

emailItemObj.To = "[email protected]"

Step 7: Use the CC Property in the Macro Code

Enter this code to send the carbon copy of the email using the CC property.

emailItemObj.CC = "[email protected]"

Step 8 – Use the BCC Property in a Macro Code

Enter the code below to send a carbon copy of the email to other addresses using the BCC property

emailItemObj.BCC = "[email protected]"

Step 9 – Write the Email Subject

Enter the code below to write the subject using the MailItem.Subject property.

emailItemObj.Subject = "Email from Excel with Body"

Read More: Macro to Send Email from Excel

Step 10: Write the Email Body

Enter the code below and write the body of the email using the MailItem.HTMLBody property.

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 the MailItem.BodyFormat property (outlook) to olFormatHTML, the body text of the email will be in HTML format if the following code is entered.

emailItemObj.BodyFormat = olFormatHTML
emailItemObj.HTMLBody = "<HTML><BODY> <p>Hello Subscriber,<br> New article published from Exceldemy </p></BODY></HTML>"

Step 11: Send the Email

Enter this code send the email using the Send method.

emailItemObj.Send

This is the 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 using the Attachments.Add method (outlook). The source of the attachment file is the only argument we need to include. This is the code:

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

Get FREE Advanced Excel Exercises with Solutions!

How to Send an Email with Body from Excel Using a Macro – Easy Steps