VBA to Generate Multiple Lines in Email Body in Excel (2 Methods)

This article will show you 2 different methods on how to generate an email body with multiple lines with the VBA macro in Excel.

Table of Contents hide

Download Workbook

2 Methods with VBA to Generate Multiple Lines in Email Body in Excel

1. Embed VBA Macro to Generate Multiple Lines in Email Body in Excel

2. Insert VBA to Send Email with Multiple Lines to Multiple Email Addresses

Key Point to Consider

Conclusion

Download Workbook

You can download the free practice Excel workbook from here.

Generate Multiple Lines in Email Body.xlsm

2 Methods with VBA to Generate Multiple Lines in Email Body in Excel

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 Sub

Your 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 String

Then, 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 Next

If any error occurs, go to the next statement.

With iMail
 .To = "[email protected]"
 .cc = ""
 .Subject = "Testing Email"
 .Attachments.Add ""
 .HTMLBody = iBody
 .Display
 End With

Then, declare the recipient of the email, the email subject, attachments if added, multiple lines in the body – everything will be displayed.

On Error GoTo 0

Disable the error handling in the current procedure.

Set iMail = Nothing
Set iApp = Nothing

If any error occurs during the above procedures, nothing will happen after running the macro.

End Sub

Finally, end the sub-procedure of the macro.

Similar Readings

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 Sub

Your 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 Variant

Then, 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 Long

Again, 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").Value

After 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 With

Then, initiate email sending with the email subject to the recipient with multiple lines in the body.

End If

Now, leave the IF condition.

i = i + 1
Next

Keep continuing the iteration until all the cells are covered.

On Error GoTo debugs

If any error occurs, then go to the debug statement.

debugs:
If Err.Description <> "" Then MsgBox Err.Description

The debug statement – error message in the MsgBox.

End Sub

Finally, end the sub-procedure of the macro.

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.

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.