Automation Recipe: Personalized Enrollment Confirmation Emails at Scale

What This Builds

After open enrollment closes, you typically have hundreds of employees who need a confirmation email listing their specific plan elections for 2026. Sending these manually — or not sending them at all — is a common gap. This automation reads your enrollment data from a Google Sheet (exported from your benefits platform), generates a personalized confirmation email for each employee listing their exact elections, and sends it automatically. What would take an afternoon of copy-paste email work happens in under 5 minutes, with every employee getting a professional confirmation that also serves as a record-keeping document.

Prerequisites

• A Make account (make.com — free plan works for this use case)
• Google Sheets with your enrollment data exported from your benefits platform
• Gmail account (or Outlook with Make's Outlook integration)
• Claude Pro or ChatGPT Plus (optional — for generating the email template)
• Time to build: 1.5-2 hours
• Cost: Make free plan; Gmail free

The Concept

Make is like a conveyor belt that processes one employee at a time. You set up a Google Sheet with one row per employee (name, email, their elected plans). Make reads through the sheet row by row, builds a personalized email for each person by filling in their specific elections into a template, and sends it via Gmail. It's the same as if you manually wrote 300 personalized emails — except it takes 5 minutes and you only do the work once.

Build It Step by Step

Part 1: Prepare Your Enrollment Data Spreadsheet

Export your enrollment data from your benefits platform (bswift, Benefitfocus, PlanSource, Workday, etc.) into a Google Sheet. Your sheet needs at minimum:

Add a Column H: Confirmation Sent (leave blank — Make will update this)

Name the sheet tab "2026 Enrollments."

If your benefits platform exports don't include employee email addresses, you'll need to add them from your HRIS export using a VLOOKUP (or ask Google Sheets AI to write the formula).

Part 2: Create Your Email Template

Before building the automation, create the email template you want to send. Use Claude or ChatGPT to generate a professional confirmation email template with placeholders:

Prompt for Claude:

Write a benefits enrollment confirmation email template for employees. Include placeholders for: employee first name, health plan, dental plan, vision plan, FSA/HSA election amount (if applicable), coverage tier, and coverage effective date. Professional, reassuring tone. End with HR contact info placeholder.

Your template will look something like:

Subject: Your 2026 Benefits Enrollment Confirmation — [First Name]

Hi [First Name],

Your 2026 benefits enrollment is confirmed. Here's a summary of what you elected during open enrollment:

Health Insurance: [Health Plan] — [Coverage Tier]
Dental Insurance: [Dental Plan]
Vision Insurance: [Vision Plan]
FSA/HSA: [FSA/HSA Election]

Your coverage begins January 1, 2026. You'll see your new payroll deductions on your first paycheck of the year.

If you have any questions or believe there's an error in your elections, please contact HR at [HR email] or [HR phone] by [correction deadline date].

Best regards,
[HR Name]
[Company] HR Team

Save this template in a Google Doc for easy reference while building Make.

Part 3: Set Up Make

1. Go to make.com and create a free account
2. Click Create a new scenario
3. You'll see a blank canvas with a + circle in the center

Part 4: Add the Google Sheets Trigger

1. Click the + circle to add your first module
2. Search for Google Sheets and select it
3. Choose the trigger: Search Rows
4. Connect your Google account and select:

   • Spreadsheet: your enrollment data file
   • Sheet: "2026 Enrollments"
   • Column to filter: Column H (Confirmation Sent)
   • Filter value: empty (you want rows where confirmation hasn't been sent yet)

What you should see: A module showing your Google Sheets connection and filter settings.

Part 5: Add the Gmail Send Email Action

1. Click + to the right of the Google Sheets module
2. Search for Gmail and select it
3. Choose the action: Send an Email
4. Connect your Google account

Configure the email fields using the data from your Google Sheet:

• To: Map to Column B (Employee Email)
• Subject: Type: Your 2026 Benefits Enrollment Confirmation — then click the variable icon and map to Column A (Employee Name)
• Content (HTML or plain text): Paste your email template, then replace each placeholder with the mapped spreadsheet column:

  • [First Name] → Column A (or a formula to extract first name only)
  • [Health Plan] → Column C
  • [Dental Plan] → Column D
  • [Vision Plan] → Column E
  • [FSA/HSA Election] → Column F
  • [Coverage Tier] → Column G
  • (Keep [HR email], [HR phone], [coverage date] as static text)

Part 6: Add the "Mark as Sent" Update

1. Click + after the Gmail module
2. Select Google Sheets → Update a Row
3. Map to the same row (use the Row Number from the trigger)
4. Set Column H (Confirmation Sent) to Sent - {{timestamp}}

This ensures the scenario doesn't re-send emails if you run it again.

Part 7: Test with One Row

1. Add a test employee row with your own email address
2. Click Run once (the play button in the bottom left)
3. Check your email — you should receive the confirmation with all the correct plan details filled in

What you should see: An email in your inbox with your name, plan details from the test row, and the sheet updated with "Sent" in Column H.

Troubleshooting: If the email doesn't arrive, check Make's execution log (the green/red indicators on each module). Common issues: Gmail authorization expired (re-connect), column mapping wrong (check which column letter maps to which data).

Part 8: Run for All Employees

Once the test is successful:

1. Click Run once again — Make will process all rows where Column H is empty (not yet sent)
2. For large employee populations, Make may take several minutes to send all emails

What you should see: Make processes through all rows, sending one email per employee. Column H updates with "Sent" for each row processed. Your Google Sheet becomes your audit log.

Real Example: End-to-End Workflow

Setup: You've exported 387 employee enrollment records from Benefitfocus into your Google Sheet. All 387 employees need a confirmation email.

What Make does in 8 minutes:

1. Reads Row 2 (first employee): Sarah Chen, sarah.chen@acme.com, Cigna PPO, Delta Dental, VSP, FSA $1,200, Employee + Spouse
2. Builds email: "Hi Sarah, Your 2026 benefits enrollment is confirmed. Health: Cigna PPO — Employee + Spouse..."
3. Sends email to sarah.chen@acme.com
4. Updates Column H: "Sent - 2026-01-15 2:34 PM"
5. Moves to Row 3. Repeats 386 more times.

What you do: Review the send log in Make for any failures (invalid email addresses will show as errors). Follow up manually on the 3-4 failed sends.

Time saved: What would have taken 4-6 hours of manual work takes 8 minutes of automation runtime and 20 minutes of setup (after the first time).

What to Do When It Breaks

• "Make isn't finding rows to process" → Check your filter settings — Column H should filter for "is empty." Make sure no cells in Column H have spaces or other hidden characters.
• "Emails are sending but plan details are blank" → Your column mapping is off. Go back to the Gmail module and re-map each variable to the correct column letter.
• "Some employees got wrong plan details" → Your enrollment export may have had merged rows or formatting issues. Check the problematic rows in your Google Sheet for data inconsistencies.
• "Make ran out of operations" → The free Make plan has 1,000 operations per month. If you have 400 employees, you need ~1,200 operations (3 modules × 400 rows). Upgrade to the Core plan ($9/month) or run in two batches split across months.

Variations

• Simpler version: Use Google Apps Script (free, built into Google Sheets) to run the same mail merge — no Make account needed. Go to Extensions → Apps Script and paste a script that sends Gmail based on your sheet data. Requires basic JavaScript familiarity.
• Extended version: Add a fourth module that creates a PDF summary of the employee's elections and attaches it to the confirmation email — useful for employees who want a printed record of their benefits.

What to Do Next

• This week: Build the automation and test with 5 employee records (use your own email and 4 colleagues willing to test)
• After open enrollment: Run for all employees within 2 weeks of enrollment closing
• Advanced: Schedule the scenario to run automatically on December 1 each year — it sends confirmations automatically without you needing to trigger it manually

Advanced guide for Benefits Administrator professionals. Make.com free plan supports this automation with limits — verify operation count before running for large employee populations.