Send an HTML formatted email from Excel Macro

[jeffmoseler]

I am a super newbie to HTML. Trying to get an email formatted the way I want it to be so I'm trying to learn it a little bit. I think it is pretty self explanitory from the code. The problem I am having is that the font seems to default to either calibri or times depending on where it is in the body. How can I tell HTML to assign it a certain font. I have looked online but there seems to be a bunch of different ways to do it and all the ones I tried don't work. It would be nice if there was a list of HTML formatting commands that worked with Excel VBA. Any suggestions on how to clean this up are welcome as well. Just remember I am fairly new to this!

Sub SendUpdateEmail()
    Dim vDriverList, vTractorList, vTrailerList, vFinancials, vGLLossRuns, vALLossRuns, vPDLossRuns, vCGLossRuns, vPRLossRuns, vIFTAs As Boolean
    If Range("Q111") = "P" Or Range("Q111") = "X" Then vDriverList = False Else vDriverList = True
    If Range("Q111") = "P" Or Range("Q111") = "X" Then vTractorList = False Else vTractorList = True
    If Range("Q111") = "P" Or Range("Q111") = "X" Then vTrailerList = False Else vTrailerList = True
    If Range("Q111") = "P" Or Range("Q111") = "X" Then vFinancials = False Else vFinancials = True
    If Range("Q111") = "P" Or Range("Q111") = "X" Then vGLLossRuns = False Else vGLLossRuns = True
    If Range("Q111") = "P" Or Range("Q111") = "X" Then vALLossRuns = False Else vALLossRuns = True
    If Range("Q111") = "P" Or Range("Q111") = "X" Then vPDLossRuns = False Else vPDLossRuns = True
    If Range("Q111") = "P" Or Range("Q111") = "X" Then vCGLossRuns = False Else vCGLossRuns = True
    If Range("Q111") = "P" Or Range("Q111") = "X" Then vPRLossRuns = False Else vPRLossRuns = True
    If Range("Q111") = "P" Or Range("Q111") = "X" Then vIFTAs = False Else vIFTAs = True
    
    vOpen = "Thank you for the opportunity to provide you with a proposal for your truck insurance this year. In order to get you a quote we are in need of the following information:,"
    If vDriverList = True Then vDL = "<br>" & "Driver List" & "<br>" _
     & "  " & "Name of driver" & "<br>" _
     & "  " & "Date of Hire" & "<br>" _
     & "  " & "Driver's License Number" & "<br>" _
     & "  " & "Years Driving Experience" & "<br>"

    If vTractorList = True Then vTL = "Tractor List" & "<br>" _
     & "  " & "Year" & "<br>" _
     & "  " & "Make" & "<br>" _
     & "  " & "VIN Number" & "<br>" _
     & "  " & "Value" & "<br>"
     
    sHTML = vOpen & vDL & vTL

    
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
    With OutMail
    .Display
    End With
        Signature = OutMail.HTMLBody


    strbody = vDL

    With OutMail
        .SentOnBehalfOfName = ""
        .To = sTo
        .CC = sCC
        .BCC = ""
        .Subject = "Update on your Quote"
        .HTMLBody = sHTML & Signature
        .Display
    End With
    
End Sub

 

[John_w]

You have to use inline styles within the HTML tags, as shown in this example:

Sub Send_Email()

    Dim OutApp As Object, OutMail As Object
    Dim sHTML As String
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    sHTML = "< p>< span style='font-family: arial,helvetica,sans-serif; font-size: medium;'>This text is Arial 14 font."
    sHTML = sHTML & "< p>< strong>< span style='font-family: helvetica; font-size: small;'>This text is Helvetica 12 font in bold."
    sHTML = Replace(sHTML, "< ", "<")
    
    On Error Resume Next

    With OutMail
        .Display
        .To = "email.address@email.com"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .HTMLBody = sHTML & Replace("< br>", "< ", "<") & .HTMLBody
        .Send
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

I generated this HTML using Free Online HTML Editor and replaced embedded double quotes in the HTML with single quotes, since HTML allows either. Also, I had to insert a space after each < character to prevent the forum rendering the code as HTML, and use Replace to remove these spaces. This workaround is only necessary when posting the code to this forum.

Also, the HTML body text is correctly rendered in the email only if you don't use MS Word as the Outlook HTML editor.