How to add blank line in email body?

khyati dedhia 0
Viewed 52

I want to update email body but entire body is coming in one line.
enter image description here

How to add blank line in email body like below?

Dear Kalpesh,

Your enthusiasm and ability to demonstrate & deliver your set goals has resulted in a significant increase in productivity and profitability.

Sub CreateEmails()

    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = Worksheets("Sheet1")

    Dim lastRow As Long
    With sourceWorksheet
         lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With

    Dim OutlookApp As Object
    Set OutlookApp = CreateObject("Outlook.Application")

    Dim rowIndex As Long

    For rowIndex = 2 To lastRow 'start at the second row

        Dim MItem As Object
        Set MItem = OutlookApp.CreateItem(0)

        Dim strbody As String
    
        strbody = "Dear" & " " & sourceWorksheet.Cells(rowIndex, "A").Value & "," & vbNewLine & _
          "Your enthusiasm and ability to demonstrate & deliver your set goals has resulted in a significant increase in productivity and profitability."
              
        With MItem
            .To = sourceWorksheet.Cells(rowIndex, "E").Value
            .CC = sourceWorksheet.Cells(rowIndex, "F").Value
            'Will pull in all email address in Row C as separate emails
            .Subject = "MIP Rating - Sep'22"
            .HTMLBody = strbody
            .display

        End With[![enter image description here][1]][1]
    Next rowIndex
End Sub
outlook email vba excel
2 Answers

You need to apply html format and set the BodyFormat property of the mail item to olFormatHTML.

Dim body_ As String
    body_= "<p> Dear " & sourceWorksheet.Cells(rowIndex, "A").Value & "</p>" & _
           "<p> Your enthusiasm and ability to demonstrate & deliver your set goals has resulted in a significant increase in productivity and profitability.</p>" 

.BodyFormat = 2 'olFormatHTML
.HTMLBody = "<html><head></head><body>" & body_ & "</body></html>"
Kostas K.0

While you are using HTML body then Use break tag <br> instead of VBNewLine. See below code.

So, change will be made to this line

strbody = "Dear" & " " & sourceWorksheet.Cells(rowIndex, "A").Value & ", <br><br>" & _
          "Your enthusiasm and ability to demonstrate & deliver your set goals has resulted in a significant increase in productivity and profitability."

Full Macro will be-

Sub CreateEmails()
    Dim sourceWorksheet As Worksheet
    Dim lastRow As Long
    Dim OutlookApp As Object
    Dim rowIndex As Long
    Dim MItem As Object
    Dim strbody As String
    
    Set sourceWorksheet = Worksheets("Sheet1")
    With sourceWorksheet
         lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With
    
    Set OutlookApp = CreateObject("Outlook.Application")
    For rowIndex = 2 To lastRow 'start at the second row
    Set MItem = OutlookApp.CreateItem(0)
    strbody = "Dear" & " " & sourceWorksheet.Cells(rowIndex, "A").Value & ", <br><br>" & _
              "Your enthusiasm and ability to demonstrate & deliver your set goals has resulted in a significant increase in productivity and profitability."
              
        With MItem
            .To = sourceWorksheet.Cells(rowIndex, "E").Value
            .CC = sourceWorksheet.Cells(rowIndex, "F").Value
            'Will pull in all email address in Row C as separate emails
            .Subject = "MIP Rating - Sep'22"
            .HTMLBody = strbody
            .display

        End With
    Next rowIndex
    End Sub
Harun24hr0
Related