Build a Dynamic Email Generator in Excel Using Templates

Email is one of the most powerful tools for communication. Automating email generation can save time and ensure consistency in communication. Excel VBA can create a dynamic email generator that allows you to automatically generate personalized emails from templates.

In this article, we’ll create a dynamic email generator with templates in Excel.

Prerequisites

• Microsoft Excel (2016 or newer).
• Basic understanding of Excel.
• Access to the VBA editor (Developer tab enabled).

Step 1: Setting Up Your Excel Workbook

Create the Data Sheet

• Add a new sheet named Data.
• Create columns for your recipient information:
  • First Name
  • Last Name
  • Email
  • Company
  • Department
  • Role
  • Meeting Topic
  • Action Item
  • Sender Name

Create the Template Sheet

• Create another sheet named Template.
• Create the following columns:
  • Template ID
  • Template Name
  • Subject Line
  • Email Body

Step 2: Creating Email Templates

Create an email template that dynamically inserts values using placeholders such as {{FirstName}}, {{Company}}, {{Department}}, {{Role}}, etc.

Template 1:

Let’s create a template for automated onboarding emails.

Dear {{FirstName}},
Welcome to {{Company}}! We're excited to have you on board.
Your account has been set up with the following details:
Department: {{Department}}
Role: {{Role}}
Best regards,
HR Team

This template is used for automated onboarding emails, where actual values replace the placeholders before sending, ensuring a personalized message for each recipient.

Explanation:

• {{FirstName}}: Placeholder for the employee’s first name.
• {{Company}}: Placeholder for the company’s name.
• {{Department}}: Placeholder for the department where the new employee is assigned.
• {{Role}}: Placeholder for the employee’s job role.

Template 2:

We created another template for post-meeting communications.

Hi {{FirstName}},
Thank you for your time during our discussion about {{Meeting Topic}}. As discussed, I'm following up on {{Action Item}}.
Let me know if you have any questions.
Best regards,
{{Sender Name}}

Step 3: Insert the VBA Code

To open the VBA editor:

• Go to the Developer tab >> select Visual Basic.
• Click Insert >> select Module.
• Copy and paste the following code:

Option Explicit
Public Sub GenerateEmails()
 Dim ws As Worksheet
 Dim templateWs As Worksheet
 Dim lastRow As Long
 Dim i As Long
 Dim emailBody As String
 Dim subjectLine As String
 Dim templateID As Long
 
 ' Set references to worksheets
 Set ws = ThisWorkbook.Sheets("Data")
 Set templateWs = ThisWorkbook.Sheets("Templates")
 
 ' Find last row with data
 lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 
 ' Get template ID from user
 templateID = InputBox("Enter the Template ID number:", "Select Template")
 
 ' Get template text
 subjectLine = GetTemplate("Subject_Line", templateID)
 emailBody = GetTemplate("Email_Body", templateID)
 
 ' Convert special characters to proper line breaks
 emailBody = Replace(emailBody, "\n", vbNewLine)
 
 ' Create Outlook items
 Dim outlookApp As Object
 Dim emailItem As Object
 
 Set outlookApp = CreateObject("Outlook.Application") 
 ' Loop through each row of data
 For i = 2 To lastRow
 ' Create new email
 Set emailItem = outlookApp.CreateItem(0)
 
 With emailItem
 ' Replace placeholders with actual data
 .Subject = ReplaceFields(subjectLine, i, ws)
 .Body = ReplaceFields(emailBody, i, ws) ' Changed from .HTMLBody to .Body
 .To = ws.Cells(i, 3).Value ' Email address in column C
 .Display ' Display email (change to .Send to send automatically)
 End With
 Next i
 
 Set outlookApp = Nothing
End Sub
Private Function GetTemplate(field As String, templateID As Long) As String
 Dim templateWs As Worksheet
 Dim templateRow As Range
 
 Set templateWs = ThisWorkbook.Sheets("Templates")
 
 ' Find the template row
 Set templateRow = templateWs.Columns(1).Find(What:=templateID, LookIn:=xlValues, LookAt:=xlWhole)
 
 If Not templateRow Is Nothing Then
 Select Case field
 Case "Subject_Line"
 GetTemplate = templateWs.Cells(templateRow.Row, 3).Value
 Case "Email_Body"
 GetTemplate = templateWs.Cells(templateRow.Row, 4).Value
 End Select
 End If
End Function
Private Function ReplaceFields(text As String, rowNum As Long, ws As Worksheet) As String
 Dim result As String
 result = text 
 ' Replace all field placeholders with actual data
 result = Replace(result, "{{FirstName}}", ws.Cells(rowNum, 1).Value)
 result = Replace(result, "{{LastName}}", ws.Cells(rowNum, 2).Value)
 result = Replace(result, "{{Company}}", ws.Cells(rowNum, 4).Value)
 result = Replace(result, "{{Department}}", ws.Cells(rowNum, 5).Value)
 result = Replace(result, "{{Role}}", ws.Cells(rowNum, 6).Value)
 result = Replace(result, "{{Meeting Topic}}", ws.Cells(rowNum, 7).Value)
 result = Replace(result, "{{Action Item}}", ws.Cells(rowNum, 8).Value)
 result = Replace(result, "{{Sender Name}}", ws.Cells(rowNum, 9).Value)
 
 ReplaceFields = result
End Function

Step 4: Run the Email Generator

• On the Developer tab >> click Macros.
• Select GenerateEmails >> click Run.

• In the Message Box >> enter the Template ID: 1.
• It will send all the mail following template 1.

• Again Run the code.
• In the Message Box >> enter Template ID: 2.

It will send all the mail following template 2.

• Review the generated emails before sending them.
• If review is not required, use .Send property instead of .Display to send all the emails.

Customization Tips

Adding New Placeholder Fields:

• Add a new column in the Data sheet.
• Update the ReplaceFields function in the VBA code to include the new field.
• Add the new placeholder to your email templates using the format {{FieldName}}.

HTML Formatting:

You can include HTML formatting in your email templates:

<p style="color: blue;">This text will be blue</p>
<strong>This text will be bold</strong>

Conclusion

This tutorial will help you set up dynamic email generation in Excel with predefined templates and VBA automation. You can use these templates for business communications, client follow-ups, and automated reminders. This email generator will work as a foundation, you can customize it based on your specific requirements. Just create the template, update the data sheet, and placeholder in the VBA code. Remember to test thoroughly before using it in production environments.