I want to update email body but entire body is coming in one line.
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
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>"
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