Precise Control of HTML Email Font Type and Size in VBA: A Technical Implementation

Keywords: VBA | HTML Email | Font Control

Abstract: This article explores how to precisely control the font type and size of email bodies when sending HTML-formatted emails via Outlook automation in Excel VBA. Traditional methods using the tag's size attribute are limited to discrete values of 1-7, failing to meet exact font size requirements. By analyzing the best answer's technical solution, the article details the use of CSS styles (style attribute) with font-size:11pt and font-family:Calibri to achieve precise font control. It also discusses the fundamental differences between HTML tags and CSS styles in email formatting, providing complete code examples and implementation steps.

Introduction

In scenarios involving automated email sending through Excel VBA and Outlook integration, formatting the email body is a common requirement. Users often need precise control over font type, size, and style to ensure professionalism and readability. Traditional VBA email generation methods typically use simple HTML tags, such as the tag, but these have limitations, particularly in font size control.

Limitations of Traditional Methods

In the original question, the user employed the following code snippet to set the email body:

strbody = "<FONT SIZE = 3>Good Morning;<p>We have completed our main aliasing process for today.  All assigned firms are complete.  Please feel free to respond with any questions.<p>Thank you."

Here, the SIZE attribute of the tag only accepts integer values from 1 to 7, each corresponding to a predefined font size. For example, SIZE=3 results in a relatively small font, while SIZE=4 is noticeably larger. This discrete, predefined sizing cannot meet user demands for exact font sizes, such as 11 points. Moreover, the tag is deprecated in modern HTML standards, with its functionality replaced by CSS (Cascading Style Sheets).

Advantages of CSS Styles

The best answer provides a CSS-based solution:

strbody = "<BODY style=font-size:11pt;font-family:Calibri>Good Morning;<p>We have completed our main aliasing process for today.  All assigned firms are complete.  Please feel free to respond with any questions.<p>Thank you.</BODY>"

This method centers on using the style attribute of the HTML <BODY> tag to apply CSS styles. Specifically:

font-size:11pt: Sets the font size precisely to 11 points. Here, "pt" is the typographic unit "point," where 1pt is approximately 1/72 of an inch, offering continuous and adjustable font size control.
font-family:Calibri: Sets the font type to Calibri, a common sans-serif font known for good readability.

Compared to the tag, CSS styles provide more precise and flexible control. Users can specify exact font size values (e.g., 11pt, 12px) rather than being limited to a few predefined options. Additionally, CSS supports more font properties, such as font-weight, font-style, and line-height, further enhancing formatting capabilities.

Implementation Steps and Code Example

Below is a complete VBA code example demonstrating how to integrate CSS styles into the email generation process:

Private Sub CommandButton1_Click()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim strUser As String
    Dim signature As String
    Dim sTo As String
    Dim sCC As String
    
    ' Build recipient list
    Set emailRng = Worksheets("Send Email").Range("D3:I6")
    For Each cl In emailRng
        sTo = sTo & ";" & cl.Value
    Next
    sTo = Mid(sTo, 2)
    
    ' Build CC list
    Set emailRngCC = Worksheets("Send Email").Range("D8:I11")
    For Each cl In emailRngCC
        sCC = sCC & ";" & cl.Value
    Next
    sCC = Mid(sCC, 2)
    
    ' Create Outlook objects
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    ' Retrieve signature
    With OutMail
        .Display
    End With
    signature = OutMail.HTMLBody
    
    ' Set email body with CSS styles
    strbody = "<BODY style='font-size:11pt; font-family:Calibri;'>" & _
              "Good Morning;<p>We have completed our main aliasing process for today. " & _
              "All assigned firms are complete. Please feel free to respond with any questions.<p>" & _
              "Thank you.</BODY>"
    
    ' Send email
    With OutMail
        .SentOnBehalfOfName = ""
        .To = sTo
        .CC = sCC
        .BCC = ""
        .Subject = "Data Morning Alias Process - COMPLETE"
        .HTMLBody = strbody & signature
        .Display
    End With
End Sub

In this example, the key improvement lies in constructing the strbody variable. We use the <BODY> tag and apply CSS styles via the style attribute. Note that the style attribute value is enclosed in single quotes to avoid conflicts with double quotes in the VBA string. The font size is set to 11pt, and the font family to Calibri. These settings apply to all text within the <BODY> tag, including content within paragraph tags ().

Technical Details and Considerations

Several technical details should be noted during implementation:

Style Inheritance: In HTML, child elements inherit styles from parent elements. Thus, applying styles to the <BODY> tag ensures consistent font settings across the entire email body. For finer control, styles can be applied to specific tags like <div> or .
Unit Selection: CSS supports multiple font size units, including pt (points), px (pixels), em (relative units), and % (percentage). In email contexts, pt and px are most common. The pt unit relates to physical dimensions, suitable for print and cross-device consistency; the px unit relates to screen pixels, ideal for display. Users should choose units based on specific needs.
Font Fallbacks: In the font-family property, multiple fonts can be specified as fallbacks to ensure readability if the primary font is unavailable on the recipient's device. For example: font-family: Calibri, Arial, sans-serif;. This way, if Calibri is not available, the system tries Arial, then any available sans-serif font.
Outlook Compatibility: Outlook has limited support for HTML and CSS, especially in older versions. Therefore, it is advisable to use simple CSS properties and inline styles (via the style attribute), avoiding complex selectors and external stylesheets. Testing display effects across different Outlook versions is crucial.

Extended Applications

Beyond font size and type, CSS styles can control other appearance properties of emails. For example:

Color: Use color: #333333; to set text color.
Background: Use background-color: #f0f0f0; to set background color.
Alignment: Use text-align: center; to set text alignment.
Margins and Padding: Use margin: 10px; padding: 5px; to control element spacing.

By combining these properties, users can create highly customized email templates to meet various business needs.

Conclusion

When generating HTML emails in VBA, using CSS styles instead of traditional tags significantly enhances control precision and flexibility over email body formatting. By setting font-size:11pt and font-family:Calibri, users can easily achieve exact font size and type control. This approach not only addresses the discrete size limitations in the original problem but also lays the groundwork for more complex email formatting requirements. In practice, thorough testing for compatibility across different email clients is recommended, along with considering font fallback mechanisms to ensure optimal readability.

Copyright Notice: All rights in this article are reserved by the operators of DevGex. Reasonable sharing and citation are welcome; any reproduction, excerpting, or re-publication without prior permission is prohibited.