Send Personalized Emails Directly from Excel
- savvyanalytiqs
- Apr 28
- 4 min read
If you've ever found yourself manually emailing dozens of managers about missing performance reviews or spending hours copying and pasting names into reminder messages about goal-setting deadlines, this blog post is for you. What once consumed an entire afternoon now takes mere minutes, and the personalization actually improves when automated.
The Problem With Manual Reminder Emails
Every HR professional knows the drill: quarterly goal-setting, annual performance reviews, compensation cycles, benefit enrollments—each process inevitably requires follow-up with employees who haven't completed their tasks. The traditional approach looks something like this:
Run a report from your HRIS to identify non-compliant employees or managers
Sort and filter the data to group by department or manager
Write individual emails or create a template you customize for each recipient
Copy/paste employee names and relevant details into each message
Send dozens of emails, one painful click at a time
This approach isn't just tedious—it's error-prone. It's far too easy to accidentally include the wrong names, send to the wrong manager, or miss people entirely. Plus, the manual process often means you're less likely to follow up as frequently as you should.
The Excel-to-Email Solution
Imagine instead: You press a single button, and Excel automatically sends personalized reminder emails to every manager with their specific list of employees who haven't completed their goals. Each email addresses the manager by name, lists only their direct reports, and includes the relevant deadlines and links. You can even add relevant attachments. No copy-paste errors, no tedious clicking, and no wasted afternoon.
The Basic Setup: A Simple Reminder System
1. Prepare Your Data
Start with a spreadsheet that contains:
Manager names and email addresses
Employee names and emails
Status indicators (Completed/Not Completed)
Any other relevant information (deadlines, links, etc.)
Manager | Mgr Email | Employee | Emp Email | Goal Status |
Caroline Giovanni | Felicity Edwards | Not Started | ||
Caroline Giovanni | Emanuel Pennin | In Progress | ||
Eli Ives | Elena Dursa | Not Started | ||
Eli Ives | Cecilia Takaou | Completed |
2. Create a VBA Macro to Send Emails
The heart of this solution is a VBA macro that will:
Identify which managers have team members with incomplete goals
Generate personalized emails listing only their direct reports who need to take action
Display the emails for your review before sending
While you could program the emails to send automatically, I personally prefer to review them first. This allows me to make last-minute adjustments—for example, if I recently spoke with a manager who was completing their reviews that day, or if an employee is leaving the organization and the reminder isn't needed.
SAMPLE CODE: This will work for a file on "Sheet1" in your workbook with columns arranged exactly as shown in the table above. To implement:
Open the VBA editor by pressing Alt+F11 in Excel
Insert a new module (right-click in the Project Explorer → Insert → Module)
Copy and paste the code below
Update with your desired subject line and your actual file path for any attachments
Once you have this basic code working, you can enhance it to create more sophisticated reminders. For example, you could customize the message based on how close the deadline is or which specific steps each employee has completed.
Sub sendemail()
'set dimensions
Dim datafile As Worksheet: Set datafile = Sheets("Sheet1")
Dim mailapp As Object
Dim mymail As Object
Dim count As Long: count = datafile.Cells(Rows.count, 1).End(xlUp).Row
Dim subject As String
Dim attach As String
Dim body As String
'Add helper columns
With datafile
.Range("F2:F" & count).FormulaR1C1 = "=COUNTIF(R2C2:RC2,RC2)"
End With
'Send emails
For i = 2 To count
'Create email components
If datafile.Range("E" & i).Value <> "Completed" Then
subject = "Performance Review Reminder: Due 12/20"
attach = "C:\Users\savvy\OneDrive\Savvy Analytics\Performance Review Guide.pdf"
receiver = datafile.Range("B" & i).Value
body = datafile.Range("A" & i).Value & ",<br> Performance reviews need to be completed for the following employees: <br>" & datafile.Range("C" & i).Value
Do Until datafile.Range("F" & i + 1).Value = 1 Or i = count
i = i + 1
body = body & "<br>" & datafile.Range("C" & i)
Loop
'Create emails
'open new email & set account
Set mailapp = CreateObject("Outlook.Application")
Set mymail = mailapp.CreateItem(olMailItem)
'draft email
With mymail
.Display
.To = receiver
.cc = cc
.subject = subject
Signature = .htmlbody
.htmlbody = body & "<BR>" & Signature
.Attachments.Add attach
End With
End If
Next i
'Delete helper column
datafile.Range("F:F").Delete
End Sub3. Making the Tool User-Friendly
To make this solution accessible to your entire HR team (even those who aren't code-savvy), add a simple button to your spreadsheet:
Add a shape
Right click and select "Assign macro"
Select your macro
Click OK
Now, anyone on your team can simply press the button to create all reminder emails in one go.
Real-World Applications
This approach is incredibly versatile and can be adapted for numerous HR processes:
Performance Review Reminders: Send managers a list of team members who haven't completed their self-assessments, or remind employees directly about upcoming review deadlines.
Learning & Development Tracking: Automatically notify managers about team members with overdue compliance training or required certifications.
Benefits Enrollment Follow-Up: Send personalized reminders to employees who haven't completed their benefits selection within the enrollment period.
Compensation Review Status: Keep executives updated on which managers have not yet submitted their proposed compensation adjustments.
Final Thoughts
Email automation from Excel is one of those solutions that delivers an immediate and dramatic return on investment. What used to consume hours of tedious work now happens in seconds, with fewer errors and more consistent follow-up.
The real benefit goes beyond time savings. When you're not dreading the manual work of sending dozens of reminders, you'll follow up more consistently, which typically leads to higher completion rates for whatever process you're managing. More completed performance reviews, more on-time goal submissions, and more engaged employees—all from spending a little time setting up automation.
留言