Control text formatting (e.g. font, color, etc.) when using Excel VBA to automate emails via Outlook

[Roland Hoelscher]

The Excel VBA code below is part of an Excel VBA tool I'm developing. It sends emails to multiple recipients. The sheet "Recipients" has the list of email recipients. The sheet "PROSP EMAIL TEXT" has the text for the body of the message. This way both the list of recipients and the body for each message is dynamic.
I have not found a way to format the text in the body of each email, the font, font size, bold, color, etc.? How can I control the text format?
Thank you in advance for the help.

Sub SendProspectEmails()
Dim objOutlookApp As Object
Set objOutlookApp = CreateObject("Outlook.Application")
'
Dim objMailItem As Object
Set objMailItem = objOutlookApp.CreateItem(0)
'
Dim intCountOfEmails As Integer
Dim strKogSendEmails As String
Dim intRowCountSendEmails As Integer
intCountOfEmails = 0
strKogSendEmails = "YES"
intRowCountSendEmails = 2
'
Do While strKogSendEmails = "YES"
' the two "if statements" which follow are two business rules for dynamically including or excluding entries in the recipient list
If Workbooks(strThisVbaWorkbookName).Worksheets("Recipients").Cells(intRowCountSendEmails, 2).Value = "ACTIVE" Then ' if recipient is set to "ACTIVE"
If Workbooks(strThisVbaWorkbookName).Worksheets("Recipients").Cells(intRowCountSendEmails, 7).Value = "YES" Then ' if recipient is set to "Include = YES"
Set objMailItem = objOutlookApp.CreateItem(0)
objMailItem.To = Worksheets("RECIPIENTS").Cells(intRowCountSendEmails, 5).Value
objMailItem.Subject = Worksheets("PROSP EMAIL TEXT").Cells(2, 3).Value
objMailItem.Body = _
Worksheets("PROSP EMAIL TEXT").Cells(3, 3).Value & " " _
& Worksheets("Recipients").Cells(intRowCountSendEmails, 3).Value & " " _
& Worksheets("Recipients").Cells(intRowCountSendEmails, 4).Value & "," & vbNewLine & vbNewLine _
& Worksheets("PROSP EMAIL TEXT").Cells(4, 3).Value & vbNewLine _
& Worksheets("PROSP EMAIL TEXT").Cells(5, 3).Value & "," & vbNewLine _
& vbNewLine & vbNewLine & vbNewLine _
& Worksheets("PROSP EMAIL TEXT").Cells(10, 3).Value & "," & vbNewLine _
& Worksheets("PROSP EMAIL TEXT").Cells(11, 3).Value & "," & vbNewLine _
& Worksheets("PROSP EMAIL TEXT").Cells(12, 3).Value & "," & vbNewLine _
& Worksheets("PROSP EMAIL TEXT").Cells(13, 3).Value & "," & vbNewLine _
& Worksheets("PROSP EMAIL TEXT").Cells(14, 3).Value & "," & vbNewLine
'
objMailItem.Send
'
intCountOfEmails = intCountOfEmails + 1
'
If strDetailedLoggingYesNo = "YES" Then
lngNextLogRecord = Workbooks(strThisVbaWorkbookName).Worksheets("SETTINGS").Cells(5, 3).Value
strLogMessageText = "Prospect Email sent to REF: " _
& Workbooks(strThisVbaWorkbookName).Worksheets("RECIPIENTS").Cells(intRowCountSendEmails, 1).Value & ", " _
& Workbooks(strThisVbaWorkbookName).Worksheets("RECIPIENTS").Cells(intRowCountSendEmails, 3).Value & " " _
& Workbooks(strThisVbaWorkbookName).Worksheets("RECIPIENTS").Cells(intRowCountSendEmails, 4).Value & ", " _
& Workbooks(strThisVbaWorkbookName).Worksheets("RECIPIENTS").Cells(intRowCountSendEmails, 5).Value
strLogMessageType = "N"
intNotificationMessageCount = intNotificationMessageCount + 1
WriteLogRecord
End If
End If
End If
'
intRowCountSendEmails = intRowCountSendEmails + 1
If Len(Workbooks(strThisVbaWorkbookName).Worksheets("RECIPIENTS").Cells(intRowCountSendEmails, 1).Value) = 0 Then
strKogSendEmails = "NO"
End If
'
Loop
'
MsgBox ("All prospect emails have been sent!" & vbCrLf & "Number of emails sent = " & intCountOfEmails)

 

[Trevor G]

When you want to use formatting in the body of an email you would need to look at .HTMLbody rather than .body. There are lots of threads on this and plenty of examples via Google searching.

A good website to use is W3Schools.com you'll find lots of HTML Tags and you'll be able to test examples in there website.

 

[Roland Hoelscher]

When you want to use formatting in the body of an email you would need to look at .HTMLbody rather than .body. There are lots of threads on this and plenty of examples via Google searching.

A good website to use is W3Schools.com you'll find lots of HTML Tags and you'll be able to test examples in there website.

Thank you Trevor. I've discovered using .HTMLBody (instead of .Body) and using the html tags. My challenge is how to make an Excel VBA email tool dynamic - so a user can change the text and formatting each time . . . and with a user friendly UI. But I see now that is a design and algorithm challenge which I need to solve separately.
Thank you again.
Roland