Excel VBA for Outlook Emails with Outlook Template and added Excel Data but HTML Tags don't render the right Font Style or Size

[annyn]

Hi, newbie here. First time asking for help from anywhere so please bear with me while I learn the rules. The following code does not throw an error but my displayed email shows the boilerplate from my template in Calibri 10 (which is what I want) but the data pulled from Excel renders as Times New Roman 12 even though I surrounded the references with the HTML Calibri font and font size of 10 pt. In a related question, can anyone explain why whenever I try to use Ron's RangetoHTML Function, my template boilerplate shows up in Outlook as a Table? Thanks in advance.

VBA Code:

Sub OpenAndModifyTemplate()
Dim vOL As Object
Dim vItem As Object
Dim vStr As String

Set vOL = CreateObject("Outlook.Application")
Set vItem = vOL.CreateItemFromTemplate("C:\Users\Microsoft\Templates\LH.oft")

With vItem
.Display
.Subject = "EID"
.To = ActiveSheet.Range("J2").Value
        
.HTMLBody = "<font face=""calibri"" style=""font-size:10pt;"">" & ActiveSheet.Range("F2").Value & "<br>" & ActiveSheet.Range("E2").Value & "<br><br>" & "Dear " & ActiveSheet.Range("N2").Value & ":" & .HTMLBody & "</font>"
.Display
End With

Set vItem = Nothing
Set vOL = Nothing
End Sub

 

[smozgur]

Welcome to the MrExcel Message Board!

It should be normally working but try not to use the font tag, it is a deprecated element.
Instead, try using the div or p tags and style the content by using the style attribute as shown below:

.HTMLBody = "<p style=""font-size:10pt;font-family:Calibri"">" & _
            ActiveSheet.Range("F2").Value & "<br>" & _
            ActiveSheet.Range("E2").Value & "<br><br>" & _
            "Dear " & ActiveSheet.Range("N2").Value & ":" & _
            .HTMLBody & _
            "</p>"