Turn .BODY subject body in outlook 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

 

[ExcelToDAX]

I would type out what you need statically in a website like this one, and then update the text with your variables after.

 

[nealmaher]

I would type out what you need statically in a website like this one, and then update the text with your variables after.

Thanks for the reply, but i don't think what the website is generating is correct

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Turn .BODY subject body in outlook into HTMLBODY using VBA
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[nealmaher]

Sorry , that was done by mistake. i did not mean to post the question twice.

 

[ExcelToDAX]

Thanks for the reply, but i don't think what the website is generating is correct

What did it output as? You just have to include the bolding by yourself. That was meant for creating line breaks and such.

 

[nealmaher]

This is how the output looked like:

<p> Dear Cells(x, 10).value, Hope you are doing well! 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 &amp; Cells(x, 7).Value. 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 &amp; location. Please coordinate with your local Payroll department to process this award payment for the next payroll cycle Please reply back if you have any questions</p>

 

[ExcelToDAX]

your .HTML body would look like this:

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

 

[nealmaher]

your .HTML body would look like this:

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

THANK YOU VERY MUCH SIR!

 

[alansidman]

also crossposted at: https://forums.excelguru.ca/threads/turn-body-subject-body-in-outlook-into-htmlbody-using-vba.11