title: How to change the font formatting on an email sent through Outlook via Excel VBA code?
body: <p>I have VBA code that uses Excel to send a number of emails through Outlook. </p>
  
  <p>It inputs the name of each individual contact into the email body (Dear So-and-so), the company name/invoice number into the subject line (Company ABC Invoice 123), attaches a different file for each contact (Invoice 123.pdf), marks the message as high importance, requests a read receipt, sends on behalf of a secondary email account I use for invoices rather than my personal email account, BCCs to the secondary email,  and adds an email signature at the bottom with a picture.</p>
  
  <p>Most of the code (if not all of it, actually) is from <a href="http://www.rondebruin.nl/win/s1/outlook/mail.htm" rel="nofollow noreferrer">this wonderful Ron de Bruin site</a>.</p>
  
  <p>I pasted together a lot of code to get where I am and don't know how I managed to get it to work at all.</p>
  
  <p>I tried numerous different "tags" in an attempt to change the font formatting. </p>
  
  <p>I tried changing Outlook's template settings and taking the code out in hopes that it would input with the "Outlook default" font.</p>
  
  <p>There are style tags in my code but they do not have much of an effect. It is set to Times New Roman Size 18. </p>
  
  <p>The "Dear So-and-so" inputs as Times New Roman Size 12.
  The email body inputs as Calibri 13.5.
  My signature inputs as whatever I set it to in the signature setup. </p>
  
  <p>I want it to all be the same font, size, color, etc.</p>
  
  <pre><code>Sub Mail_Outlook_With_Signature_Html_1()
  ' Working in Office 2000-2013
  Dim OutApp As Object
  Dim OutMail As Object
  Dim sh As Worksheet
  Dim strbody As String
  Dim cell As Range
  Dim FileCell As Range
  Dim rng As Range
  
  With Application
      .EnableEvents = False
      .ScreenUpdating = False
  End With
  
  Set sh = Sheets("Sheet1")
  
  Set OutApp = CreateObject("Outlook.Application")
  
  For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
  
    Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
  
    If cell.Value Like "?*@?*.?*" And _
      Application.WorksheetFunction.CountA(rng) &gt; 0 Then
  
        Set OutMail = OutApp.CreateItem(0)
  
        strbody = "&lt;P STYLE='font-family:Times New Roman;font-size:18'&gt;This is the message text for each message. This is all the same for each contact!&lt;/P&gt;"
  
        On Error Resume Next
  
        With OutMail
         .Display
         .To = cell.Value
         .CC = ""
         .BCC = "MyCompanyEmail@company.com"
         .Subject = Cells(cell.Row, "D").Value
         .HTMLBody = "Dear " &amp; Cells(cell.Row, "A").Value &amp; "," &amp; "&lt;br&gt;" &amp; "&lt;br&gt;" &amp; strbody &amp; .HTMLBody
         .Importance = 2
         .ReadReceiptRequested = True
         .SentOnBehalfOfName = """My Company Email Name"" &lt;MyCompanyEmail@company.com&gt;"
  
          For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
              If Trim(FileCell) &lt;&gt; "" Then
                  If Dir(FileCell.Value) &lt;&gt; "" Then
                      .Attachments.Add FileCell.Value
                  End If
              End If
          Next FileCell
  
           .Display
          End With
  
          On Error GoTo 0
  
          Set OutMail = Nothing
  
      End If
  
  Next cell
  
  Set OutApp = Nothing
  
  With Application
      .EnableEvents = True
      .ScreenUpdating = True
  End With
  
  End Sub
  </code></pre>
  
  <p><strong>Edit:</strong> Roland Shaw noticed that the font size did not have any clarifications as to what 18 was. 18pts? 18px? 18inches? So I changed the code to the following. </p>
  
  <pre><code>strbody = "&lt;P STYLE='font-family:Times New Roman;font-size:18pt'&gt;
  </code></pre>
  
  <p>This made the body of the text change formatting but it did not change the "Dear so-and-so" part of the email. Still hoping for some changes there.</p>
  
  <p><strong>2nd Edit</strong>: h4xpace suggested taking a look at <a href="http://msdn.microsoft.com/en-us/library/office/aa171418%28v=office.11%29.aspx" rel="nofollow noreferrer">this article</a> for some guidance. I already attempted to follow this guide before posting, but I could not get it to work. I went back and tried to just add what I THOUGHT was the relevant bits of code needed to change the body fonts. Additions and changes to code below: </p>
  
  <pre><code> With OutMail
      .Display
      .To = cell.Value
      .CC = ""
      .BCC = "MyCompanyEmail@company.com"
      .Subject = Cells(cell.Row, "D").Value
      **.BodyFormat = olFormatHTML**
      .HTMLBody = **"&lt;HTML&gt;&lt;BODY&gt;&lt;b&gt;"**Dear " &amp; Cells(cell.Row, "A").Value &amp; "," &amp; "&lt;br&gt;" &amp; "&lt;br&gt;" &amp; strbody &amp; .HTMLBody **&lt;/b&gt;&lt;/BODY&gt;&lt;/HTML&gt;"**
      .Importance = 2
      .ReadReceiptRequested = True
      .SentOnBehalfOfName = """My Company Email Name"" &lt;MyCompanyEmail@company.com&gt;"
  </code></pre>
  
  <p>This did not work either. </p>
  
  <p>I don't know enough about VBA to add the necessary pieces of code without altering what I already have. </p>
  
  <p><strong>Final changes</strong></p>
  
  <p>The code works as I initially intended it to now. The two main changes are to the following lines. On strbody I needed to add "pt" to the font-size. Also I needed to put the formatting information before any of the "message contents".</p>
  
  <pre><code>strbody = "&lt;P STYLE='font-family:TimesNewRoman;font-size:12.5pt;color: rgb(31,73,125)'&gt;This is the message text for each message. This is all the same for each contact!&lt;/P&gt;"
  
  .HTMLBody = "&lt;P STYLE='font-family:TimesNewRoman;font-size:12.5pt;color: rgb(31,73,125)'&gt;Dear " &amp; Cells(cell.Row, "A").Value &amp; "," &amp; "&lt;br&gt;" &amp; "&lt;br&gt;" &amp; strbody &amp; .HTMLBody
  </code></pre>