VBA & Macro Problems

How to Change Outlook Email Font and Size with Excel VBA

Adam DavisAdam Davis Nov 4, 2026 870 views

Question details

The user needs to ensure consistent font formatting (such as Arial, 11-point) when generating automated Outlook email messages using Excel VBA scripts.

How to Change Outlook Email Font and Size with Excel VBA
Product
Excel
Device & OS
not provided
Scenario
Creating automated Outlook email messages via Excel VBA scripts.
Observed behavior
Different parts of the generated email message display inconsistent font sizes due to mixed HTML formatting or partial style applications in the VBA code.
Before you start

Ensure you have basic familiarity with the Excel VBA editor and basic HTML/CSS tags. Verify that Microsoft Outlook is installed and configured on your computer, as the VBA script relies on the Outlook Object Library to generate emails.

Solution 1Recommended

Use HTML Tags in VBA to Define Font Family and Size

The most reliable way to enforce consistent fonts in automated Outlook emails is to build the email body entirely using HTML formatting within your VBA code.

When VBA generates an Outlook email, relying on plain text or default body properties often results in mismatched fonts. By using the .HTMLBody property, you can explicitly wrap your text in HTML span or paragraph tags styled with specific fonts and sizes to ensure universal consistency across the message.

1
Open the VBA Editor

In Excel, press Alt + F11 to launch the Visual Basic for Applications (VBA) Editor, and insert a new module for your script.

2
Declare Outlook Objects

Set up your Outlook application and MailItem objects by declaring them (e.g., Dim OutApp As Object, Dim OutMail As Object) and initializing the Outlook application instance.

3
Apply the .HTMLBody Property

Instead of using the .Body property, use .HTMLBody for your mail item. Construct your email string starting with an inline CSS style definition, such as <body style='font-family:Arial;font-size:11pt;'>

4
Wrap Dynamic Variables in HTML

When inserting values pulled from Excel cells into the email body, ensure they are wrapped within <p> or <span> tags that inherit the main body styling, preventing default Outlook fonts from taking over.

5
Remove Global Error Suppression

Ensure you are not using 'On Error Resume Next' globally in your script. Remove this line so that any formatting errors or missing object references throw an alert instead of failing silently.

Use HTML Tags in VBA to Define Font Family and Size
Testing HTML Output: Use 'Debug.Print .HTMLBody' in your VBA code to output the generated HTML string to the Immediate Window. This allows you to review the exact HTML code and catch missing closing tags.
Free Microsoft Office alternative

Looking for a Lightweight Office Suite with Excellent VBA Compatibility?

If you frequently work with macros and spreadsheets but want an application that is faster and more resource-efficient, consider WPS Office. It provides robust support for your existing VBA macros in a highly compatible environment.

  1. 1. Download WPS Office: Visit the official WPS Office website and download the free installer for your operating system.
  2. 2. Enable VBA Support: Once installed, ensure the VBA module is enabled within WPS Spreadsheet to start running your existing macro scripts.
  3. 3. Open Your Spreadsheets: Open your .xlsm files directly in WPS Spreadsheet. The familiar interface lets you edit and execute macros effortlessly.
Fully compatible with Microsoft Excel (.xlsx, .xlsm) formatsBuilt-in support for VBA macros and advanced spreadsheet formulasLightweight application that runs smoothly on most operating systemsFree to use with a familiar, easy-to-navigate tabbed interface
QA img-9

Frequently Asked Questions

Why does my automated Outlook email look different than the text in my Excel cells?

Excel VBA extracts the plain text values from your cells, ignoring the visual formatting applied in the worksheet. To make the email reflect specific formatting like font face and size, you must explicitly define those properties using HTML and CSS tags within the VBA .HTMLBody property.

Can I use CSS classes instead of inline styles in my VBA email macro?

Yes, you can include a <style> block within the <head> section of your .HTMLBody string. However, because email client rendering engines can be unpredictable, using inline styles (e.g., <span style='font-size:11pt;'>) is generally considered the most reliable method for ensuring font consistency.

Why should I avoid 'On Error Resume Next' in my email macro?

Using 'On Error Resume Next' globally hides all VBA runtime errors. If there is a problem creating the Outlook application object or compiling the HTML string, the macro will fail silently, leaving you with an unsent email and no error message to help you troubleshoot.

How do I test my Excel to Outlook email macro without actually sending messages?

Instead of using the .Send method at the end of your VBA mail item block, use the .Display method. This will generate the email and open it in a standard Outlook draft window, allowing you to visually inspect the font formatting before it is sent.