Turn .BODY outlook email into HTMLBODY using VBA

[nealmaher]

Hi Excel Badasses,



The below code which works well to generate the email alerts on the 90th day anniversary of the hire date. BUT, the only thing I need help with is the “subject body”

I want to turn the outlook email subject body into HTMLBODY so I can bold the cell values to distinguish from the rest of the script. I don’t know how to do that!

Currently I have it in regular body as you can see in the highlighted orange below.

Could someone please help me turn it into HTMLBODY mode .

**Please note that I bolded where it needs to be bolded.

Thanks for the help!


Sub datemudit()

Dim MYAPP As Outlook.Application, MYMAIL As Outlook.MailItem

Dim mydate1 As Date

Dim mydate2 As Long

Dim datetoday1 As Date

Dim datetoday2 As Long

Dim x As Long

lastrow = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For x = 6 To lastrow

mydate1 = Cells(x, 8).Value

mydate2 = mydate1

Cells(x, 15).Value = mydate2

datetoday1 = Date

datetoday2 = datetoday1

Cells(x, 16).Value = datetoday2

'If mydate2 - datetoday2 >= 90 Then

If datetoday2 - mydate2 = 90 Then


Set MYAPP = New Outlook.Application

Set MYMAIL = MYAPP.CreateItem(olMailItem)

MYMAIL.To = Cells(x, 11).Value & ";" & Cells(x, 12).Value

With MYMAIL

.Subject = "Employee Referral Bonus Award" & "-" & Cells(x, 1).Value


'.Body = "Dear " & Cells(x, 10).Value & "," & vbCrLf & vbCrLf & "Hope you are doing well!" & vbCrLf & vbCrLf & "This is to notify you that employee " & Cells(x, 1).Value & " is now eligible to recieve a referral bonus award in the amount of " & Cells(x, 6).Value & " " & Cells(x, 7).Value & vbCrLf & Cells(x, 1).Value & " is being rewarded for referring candidate " & Cells(x, 2).Value & " for the position of " & Cells(x, 3).Value & " in the " & Cells(x, 5).Value & " location." & vbCrLf & "Please coordinate with your local Payroll department to process this award payment for the next payroll cycle" & "." & vbCrLf & vbCrLf & "Please reply back if you have any questions" & "." & vbCrLf & vbCrLf & "Sincerely,"

.Display

'.Send

End With

Cells(x, 13) = "Yes"

Cells(x, 13).Font.ColorIndex = 21

Cells(x, 13).Font.Bold = True


'Cells(x, 13).Value = mydate2 - datetoday2

Cells(x, 14).Value = datetoday2 - mydate2

End If

Next

Set MYAPP = Nothing

Set MYMAIL = Nothing

MsgBox " Code Excecuted!"
End Sub

 

[Micron]

Switch .Body to .Htmlbody
Please post code within code tags (vba button on posting toolbar) to maintain indentation and readability (you do indent your code I hope). Hopefully you'd eliminate most of the double spacing as well. TBH I usually skip over posts like that but I figured this issue should be easy to correct.

 

[RoryA]

Duplicate of this thread. Please don't post the same question more than once. This thread is now closed.