VBA & Macro Problems

How to Add Previous Month and Year to Outlook Email Subject via Excel VBA

Jason MillerJason Miller Nov 2, 2026 868 views

Question details

The user needs an Excel VBA macro to generate an Outlook email with a subject containing the previous month and current year, while formatting the email body with HTML and preserving the user's default signature.

How to Add Previous Month and Year to Outlook Email Subject via Excel VBA
Product
Excel, Outlook
Device & OS
not provided
Scenario
Automating customized Outlook email generation from Excel using VBA.
Observed behavior
The current script generates a plain-text email without advanced formatting, and the subject needs to dynamically and accurately reflect the previous month and year.
Before you start

Ensure that the Microsoft Outlook Object Library is enabled in your Excel VBA editor's References menu before running the macro, as this is required for Outlook automation.

Solution 1Recommended

Use DateSerial and HTMLBody in VBA

This is the most robust method to format the previous month in the subject line, apply rich text styling to the email body, and keep the default Outlook signature.

By leveraging the HTMLBody property instead of the plain-text Body property, you can utilize standard HTML markup to change fonts, text sizes, colors, and emphasis.

Using DateSerial ensures accurate date calculations across all edge cases (such as leap years or January roll-overs).

1
Open the VBA Editor

Press Alt + F11 in Excel to open the Visual Basic for Applications (VBA) editor, and create a new Module.

2
Format the Subject Line

Set your email subject using the formula: .Subject = "Direct ledger allocations through " & Format(DateSerial(Year(Date), Month(Date), 0), "mmmm yyyy") & " attached". The zero argument in DateSerial correctly calculates the last day of the previous month.

3
Retrieve the Default Signature

To retain the user's default email signature, use the .Display command on your MailItem object before assigning any text to the .HTMLBody property. This action pre-loads the default signature into the HTML format.

4
Apply HTML Formatting

Assign your message content using HTML tags and append the existing signature. For example: .HTMLBody = "<p><span style='color:blue;font-size:14pt;'><b>Hello,</b></span></p>" & "<br>" & "Please see attached." & .HTMLBody

Use DateSerial and HTMLBody in VBA
Important: Using the .Display method before modifying .HTMLBody is a crucial step; if you assign the HTMLBody string first, the macro will completely overwrite the default Outlook signature.
Automate with WPS Office

Automate Tasks with WPS Spreadsheets Macros

WPS Office provides robust VBA support, allowing you to write macros and automate tasks like generating emails directly from your spreadsheets, just as you would in Microsoft Excel.

  1. 1. Open WPS Spreadsheets: Launch WPS Office and open your current macro-enabled workbook.
  2. 2. Access the Developer Tab: Go to the 'Tools' or 'Developer' tab on the top ribbon.
  3. 3. Open the VBA Editor: Click on 'Macros' or press Alt + F11 to access the built-in VBA editor.
  4. 4. Run the Script: Paste your Outlook automation script, ensure your object references are checked, and run it seamlessly.
Highly compatible with Microsoft Excel VBA syntaxSeamless integration with COM objects like OutlookLightweight installation with an intuitive interfaceCost-effective solution for advanced office automation
microsoft office alternative - wps office

Frequently Asked Questions

Why is my Outlook signature disappearing when using VBA?

When you assign a string directly to the `.Body` or `.HTMLBody` property in VBA, it overwrites all existing content, including the default signature. To prevent this, call `.Display` on the mail item first to load the signature into `.HTMLBody`, then prepend your new text to it (e.g., `MailItem.HTMLBody = "Your text" & MailItem.HTMLBody`).

How do I add line breaks in an HTML email body?

Because you are using the HTML format (`.HTMLBody`), standard VBA line breaks like `vbNewLine` or `vbCrLf` will not render properly in the email. Instead, you must use the HTML break tag `<br>` or wrap your paragraphs in `<p>` and `</p>` tags.

Can I change the font color of specific words in the email?

Yes. By utilizing the `.HTMLBody` property, you can wrap specific words in HTML span tags with inline CSS. For example, using `<span style="color:red;">Warning</span>` will change only that specific text to red.

Does this VBA macro require Outlook to be open?

If your macro uses `CreateObject("Outlook.Application")`, it will launch an Outlook background process if it isn't already running. However, having Outlook actively open ensures that default signatures and user profiles load reliably during execution.