This article will show you 2 different methods on how to generate an email body with multiple lines with the VBA macro in Excel.
VBA to Generate Multiple Lines in Email Body in Excel: 2 Methods
Following this section, you will learn how to generate multiple lines in an email body and how to send an email with multiple lines to multiple email addresses with the VBA macro in Excel.
1. Embed VBA Macro to Generate Multiple Lines in Email Body in Excel
This section will show you how to send an email with multiple lines in the email body with VBA macro in Excel. The steps to execute that are given below.
Steps:
- At first, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- After that, in the pop-up code window, from the menu bar, click Insert -> Module.
- Then, copy the following code and paste it into the code window.
Sub MultipleLinesEmail()
Dim iApp As Object
Dim iMail As Object
Dim iBody As String
Set iApp = CreateObject("Outlook.Application")
Set iMail = iApp.CreateItem(0)
iBody = "Hello World!" & vbNewLine & vbNewLine & _
             "This is ExcelDemy." & vbNewLine & _
             "A Great place to learn," & vbNewLine & _
             "a lot about Excel" & vbNewLine & _
             "Please visit. Thank you."
On Error Resume Next
 With iMail
 .To = "[email protected]"
 .cc = ""
 .Subject = "Testing Email"
 .Attachments.Add ""
 .HTMLBody = iBody
 .Display
 End With
 On Error GoTo 0
 Set iMail = Nothing
 Set iApp = Nothing
 End SubYour code is now ready to run.
- Next, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.
Finally, after successful code execution, you will be auto-generated to the outlook email application and can view the email with the multiple lines provided in the code.
VBA Code Explanation
Sub MultipleLinesEmail()At first, name the sub-procedure of the macro.
Dim iApp As Object
Dim iMail As Object
Dim iBody As StringThen, define the variables.
Set iApp = CreateObject("Outlook.Application")
Set iMail = iApp.CreateItem(0)Next, create an object of the Outlook application in the defined variables.
iBody = "Hello World!" & vbNewLine & vbNewLine & _
"This is ExcelDemy." & vbNewLine & _
"A Great place to learn," & vbNewLine & _
"a lot about Excel" & vbNewLine & _
"Please visit. Thank you."After that, store the multiple lines in the variable. Don’t forget to insert vbNewLine to have a line break after each line.
On Error Resume NextIf any error occurs, go to the next statement.
With iMail
.To = "[email protected]"
.cc = ""
.Subject = "Testing Email"
.Attachments.Add ""
.HTMLBody = iBody
.Display
End WithThen, declare the recipient of the email, the email subject, attachments if added, multiple lines in the body – everything will be displayed.
On Error GoTo 0Disable the error handling in the current procedure.
Set iMail = Nothing
Set iApp = NothingIf any error occurs during the above procedures, nothing will happen after running the macro.
End SubFinally, end the sub-procedure of the macro.
Read More: Send Email from Excel VBA without Outlook
2. Insert VBA to Send Email with Multiple Lines to Multiple Email Addresses
Suppose you have a number of email addresses in your worksheet, and you want to send an email with multiple lines in the body to all of those email addresses.
Follow this section to learn how to do that with VBA macro.
Steps:
- Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- Then, copy the following code and paste it into the code window.
Sub MultipleRangesEmail()
Dim iSubject As String
Dim iSender As String
Dim iReceiver As String
Dim iMailCC As String
Dim iMailBCC As String
Dim iBody As String
Dim iObject As Variant
Dim iSingleMail As Variant
iSubject = "Testing Email"
iSender = "[email protected]"
iMailCC = ""
iMailBCC = ""
Dim iRng As Range
Dim iCell As Range
Dim i As Long
Set iRng = Range("B2:B300")
i = 2
For Each iCell In iRng
   If Cells(i, "B").Value = "" Then
   Else
       iReceiver = Worksheets("Sheet1").Cells(i, "B").Value
       iBody = "Hello World!" & vbNewLine & vbNewLine & _
             "This is ExcelDemy." & vbNewLine & _
             "A Great place to learn," & vbNewLine & _
             "a lot about Excel" & vbNewLine & _
             "Please visit. Thank you."
Set iObject = CreateObject("Outlook.Application")
Set iSingleMail = iObject.CreateItem(0)
With iSingleMail
   .Subject = iSubject
   .To = iReceiver
   .cc = iMailCC
   .BCC = iMailBCC
   .Body = iBody
   .Send
   End With
End If
i = i + 1
Next
On Error GoTo debugs
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End SubYour code is now ready to run.
- After that, Run the macro as we showed you in the above section.
As a result, you will be auto-generated to the outlook email application and can send the email with the multiple lines provided in the code to the multiple email addresses.
VBA Code Explanation
Sub MultipleRangesEmail()At first, name the sub-procedure of the macro.
Dim iSubject As String
Dim iSender As String
Dim iReceiver As String
Dim iMailCC As String
Dim iMailBCC As String
Dim iBody As String
Dim iObject As Variant
Dim iSingleMail As VariantThen, define the variables.
iSubject = "Testing Email"
iSender = "[email protected]"
iMailCC = ""
iMailBCC = ""After that, declare the email subject and the email of the sender.
Dim iRng As Range
Dim iCell As Range
Dim i As LongAgain, define variables for iteration.
Set iRng = Range("B2:B300")Then, store the range that holds the email addresses. In our case, Column B is carrying the email addresses. You must write the range according to your dataset.
i = 2
For Each iCell In iRng
If Cells(i, "B").Value = "" Then
Else
iReceiver = Worksheets("Sheet1").Cells(i, "B").ValueAfter that, starts iteration with the FOR loop throughout the whole Column B of the “Sheet1” worksheet.
iBody = "Hello World!" & vbNewLine & vbNewLine & _
"This is ExcelDemy." & vbNewLine & _
"A Great place to learn," & vbNewLine & _
"a lot about Excel" & vbNewLine & _
"Please visit. Thank you."After that, store the multiple lines in the variable. Don’t forget to insert vbNewLine to have a line break after each line.
Set iObject = CreateObject("Outlook.Application")
Set iSingleMail = iObject.CreateItem(0)Next, create an object of the Outlook application in the defined variables.
With iSingleMail
.Subject = iSubject
.To = iReceiver
.cc = iMailCC
.BCC = iMailBCC
.Body = iBody
.Send
End WithThen, initiate email sending with the email subject to the recipient with multiple lines in the body.
End IfNow, leave the IF condition.
i = i + 1
NextKeep continuing the iteration until all the cells are covered.
On Error GoTo debugsIf any error occurs, then go to the debug statement.
debugs:
If Err.Description <> "" Then MsgBox Err.DescriptionThe debug statement – error message in the MsgBox.
End SubFinally, end the sub-procedure of the macro.
Read More: How to Apply Macro to Send Email from Excel with Attachment
Key Point to Consider
If you get the “Run-time error ‘-2147467259 (80004005)’: Outlook does not recognize one or more item” error after running the macro, then the Outlook email option is probably turned off for your Excel workbook. Make sure to turn it on before executing the macro.
Download Workbook
You can download the free practice Excel workbook from here.
Conclusion
To conclude, this article showed you 2 different methods on how to generate an email body with multiple lines with the VBA macro in Excel. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.
Related Articles
- Send Reminder Email Automatically from an Excel Worksheet Using VBA
- Excel Macro to Send Email Automatically
- Excel Macro: Send Email to an Address in Cell
- Macro to Send Email from Excel
- How to Send Email from Excel with Body Using a Macro
- Print to PDF and Email Using VBA in Excel
- How to Use Excel VBA to Paste Range into Email Body
