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

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

Steps:

  • Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

Opening VBA to Generate Multiple Lines in Email Body in Excel

  • In the pop-up code window, from the menu bar, click Insert -> Module.

  • 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.

VBA to Generate Multiple Lines in Email Body in Excel

  • Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. Click on the small Run icon in the sub-menu bar to run the macro.

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."

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.


Method 2 – Insert VBA to Send Email with Multiple Lines to Multiple Email Addresses

Steps:

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • 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.

VBA to Send Multiple Lines in Email Body in Excel

  • Run the macro as we showed you in the above section.

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

Initiate email sending with the email subject to the recipient with multiple lines in the body.

End If

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, go to the debug statement.

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

The debug statement – error message in the MsgBox.

End Sub

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.


Download Workbook

You can download the free practice Excel workbook from here.


Related Articles

 

 

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo