Send an HTML formatted email from Excel Macro

jeffmoseler

Well-known Member
Joined
Jul 16, 2004
Messages
540
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! :)

Code:
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,319
You have to use inline styles within the HTML tags, as shown in this example:
Code:
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.
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,486
Messages
5,831,968
Members
430,099
Latest member
rdhoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top