VBA & Macro Problems

How to Populate Outlook Email Templates with Excel Data Using VBA

Muhammad Talha Oct 15, 2026 869 views

Question details

The user wants to automate the creation of personalized emails by mapping spreadsheet data to an existing Outlook template using VBA.

Populate Outlook Email Templates with Excel Data Using VBA

Product: Excel, Outlook
Device & OS: not provided
Scenario: Automating bulk personalized email generation using spreadsheet data (including formatted figures and percentages) and an existing Outlook (.oft) or Stylized HTML template.
Observed behavior: The user needs a functional macro that loops through data rows, formats numerical fields appropriately, populates the designated template via CreateItemFromTemplate, and dispatches the messages.

Before you start

Ensure your email design is saved locally as an Outlook Template (.oft) file, and verify that your Excel dataset is organized with clear column headers (e.g., Email, Name, Figures, Targets) before running the automation script.

Solution 1Recommended

Use a VBA Macro to Loop and Populate Templates

Implement a VBA macro utilizing the CreateItemFromTemplate method to read spreadsheet rows and inject data into an Outlook template automatically.

This solution requires enabling the Developer tab in Excel. The script initializes an Outlook application instance, opens your predefined .oft template, and loops through your active data rows. It maps Excel cell values (like recipient addresses and formatted numbers) to the respective email fields before sending.

Open the VBA Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) editor. Click on 'Insert' in the top menu and select 'Module' to create a new blank script window.

Paste the Automation Script

Copy and paste the macro code that defines your Outlook and Excel objects. Ensure you include the loop framework: 'For i = 2 To lastRow'. Inside the loop, use 'Set olMail = olApp.CreateItemFromTemplate("C:\path\to\your\template.oft")' to call the template.

Map Your Excel Columns to Email Fields

Within the loop, assign the Excel cell values to your email properties. Set 'olMail.To = xlSheet.Cells(i, 1).Value' for recipients. Use the 'Format' function for numbers, e.g., 'Format(xlSheet.Cells(i, 4).Value, "0.0%")' to ensure percentages display correctly in the email body.

Execute the Macro

Use 'olMail.Send' to send emails automatically, or replace it with 'olMail.Display' if you wish to review each email manually. Press F5 or run the macro from the Developer ribbon to begin processing the rows.

Use a VBA Macro to Loop and Populate Templates

Handling Stripo or HTML Templates: If you are using a stylized template from Stripo, you must first export it from Stripo to Outlook, or save it as an Outlook-compatible HTML/.oft file, so the CreateItemFromTemplate method can recognize the formatting.

Free Microsoft Office alternative

Manage Spreadsheet Data Seamlessly with WPS Office

While automating Outlook explicitly requires the Microsoft ecosystem, WPS Office provides a free, highly compatible, and lightweight alternative for all your spreadsheet preparation and data management needs.

1. Download the Installer: Visit the official WPS Office website and download the free installation package tailored for your operating system.
2. Install WPS Office: Run the downloaded installer and follow the quick on-screen instructions to set up the software on your computer.
3. Open Your Data File: Launch WPS Spreadsheet and easily open your existing Excel workbooks to manage and format your mailing list data.

Fully compatible with Microsoft Excel (.xlsx, .xls) and CSV formatsLightweight architecture ensures fast processing of large mailing listsFamiliar tabbed interface allows for a zero-learning-curve transitionBuilt-in advanced data formatting and filtering tools

microsoft office alternative - wps office

Frequently Asked Questions

How do I save an Outlook email as an .oft template?

Open Outlook and draft a new email, formatting the layout and static text as desired. Leave the dynamic fields blank. Click 'File' > 'Save As', and select 'Outlook Template (*.oft)' from the 'Save as type' dropdown menu. Note the folder path where you saved it.

Why is my VBA code failing with a 'User-defined type not defined' error?

This error occurs because the Outlook Object Library is not enabled in Excel. In the VBA editor, go to 'Tools' > 'References', scroll down the list, and check the box next to 'Microsoft Outlook XX.X Object Library', then click OK.

Can I review the emails before they are sent?

Yes. In your VBA script, locate the line that says 'olMail.Send'. Change this command to 'olMail.Display'. This will pop up each generated email on your screen, allowing you to manually verify the populated data and send them individually.

Excel & Data Problems