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

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.

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.