Need to update email body in next line-CodePudding

I wanted to update email body but entire body is comming in one line, how to add blank line 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

CodePudding user response：

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

CodePudding user response：

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