How to Add Previous Month and Year to Outlook Email Subject via Excel VBA
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.

- 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.
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.
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).
Press Alt + F11 in Excel to open the Visual Basic for Applications (VBA) editor, and create a new Module.
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.
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.
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

Alternative Date Calculation Method
Use a simpler date subtraction approach to calculate the previous month, suitable if you don't want to use DateSerial.
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. Open WPS Spreadsheets: Launch WPS Office and open your current macro-enabled workbook.
- 2. Access the Developer Tab: Go to the 'Tools' or 'Developer' tab on the top ribbon.
- 3. Open the VBA Editor: Click on 'Macros' or press Alt + F11 to access the built-in VBA editor.
- 4. Run the Script: Paste your Outlook automation script, ensure your object references are checked, and run it seamlessly.

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.




