How to Send 150 Personalized Emails/Day with n8n + Google Sheets (and Auto-Mark “sent”)

This guide shows you how to build an outbound email drip in n8n that pulls contacts from Google Sheets, sends 5 emails per run, and updates the matching sheet rows to sent = Yes. It’s throttled to business hours and scales cleanly.

What you’ll build (at a glance)

pgsqlCopyEditSchedule Trigger (Cron: business hours)
   → Get row(s) in sheet   (email ≠ empty AND sent = No)
     → Loop Over Items (Split in Batches, batch=5)
        ├─(loop)→ Send email (SMTP)
        └─(loop)→ Update row in sheet (match on email)

Tip: In n8n, every node runs once per item. With batch=5, downstream nodes run 5 times per execution.

Prerequisites

Google Sheet with columns: name, company, email, sent, phone
SMTP account (e.g., info@yourdomain.com)
n8n installed and reachable
SPF/DKIM/DMARC set on your domain (recommended)

Step 1 — Prepare your Google Sheet

Ensure headers exactly match: name | company | email | sent | phone.
Fill sent with No for contacts you want to email.
(Optional) Lowercase all emails to avoid case-matching issues.

Step 2 — Create the workflow in n8n

A) Schedule Trigger (business hours)

Mode: Custom (Cron)
Expression: markdownCopyEdit0 */20 8-17 * * * Runs every 20 minutes from 8am–5pm. With batch size 5, that’s up to ~150/day.

B) Get row(s) in sheet (Google Sheets)

Resource: Sheet within document
Operation: Get Row(s)
Document: your spreadsheet
Sheet: Sheet1
Filters:
- email → is not empty
- sent → equals → No
(Optional) Set Limit = 50 to keep memory low; batching still controls sends.

C) Loop Over Items (Split in Batches)

Batch Size: 5
Wire: Use the loop output for downstream nodes.
Do not connect the “next batch” port—this ensures only 5 send per run.

Step 3 — Send the email

Node: Email (SMTP)

Host: your SMTP server (e.g., mail.capelitefinance.net)
Port: 587
Secure: OFF (STARTTLS)
User/Pass: your SMTP credentials
From: info@yourdomain.com
To: bashCopyEdit={{ $json.email }}
Subject: keep simple while testing.
Text (example): bashCopyEditHello {{ $json.name || 'there' }}, We’re offering a revolving business line of credit for {{ $json.company || 'your business' }}. The pre-qualification takes ~3 minutes. Reply STOP to unsubscribe. — Xtream Solutions for CapElite Finance

If you saw “invalid syntax” earlier, it’s caused by an unclosed {{ }}. Use the fx preview to confirm no red errors.

Step 4 — Update the matching row to `sent = Yes`

You have two clean options. Use one (we recommend Option 1 for simplicity).

Option 1 (simple): Reference the original item directly

After Send email, add Update row in sheet and pull the match value from the Loop Over Items node (the item before Send).

Resource: Sheet within document
Operation: Update Row
Document/Sheet: same file / Sheet1
Mapping Column Mode: Map Each Column Manually
Column to match on: email
Values to Update → email (using to match): javascriptCopyEdit={{ $('Loop Over Items').item.json.email }}
Values to Update → sent: Yes

This expression ensures each of the 5 items updates its own row (not just the first one).

Option 2 (explicit): Use a Merge (Pass-through)

If you want to guarantee the update happens only after the send completes:

Add Merge between Send and Update.
Merge → Mode: Pass-through
Output: Pass-through Input 2
- Input 1: from Send email
- Input 2: from Loop Over Items (the original batch)

Now Update row in sheet receives the original row JSON (including email) after the message was sent. Configure Update exactly as in Option 1 (match on email, set sent=Yes).

Step 5 — Test smoothly

Put 2–3 test rows with sent = No.
Click Execute workflow once:
- Loop Over Items → Output shows Loop Branch (5 items) (or fewer if you only provided 2–3).
- Send email → Check it succeeds (view node output).
- Update row → Verify the 2–3 rows flip to Yes in the sheet.
Activate the workflow.

Optional Hardening

A) Skip invalid/blocked domains

Add a Function node after Get Rows:

jsCopyEditconst bad = ['hotmail.com','outlook.com']; // while you delist/route these
return items.filter(i => {
  const e = (i.json.email || '').toLowerCase();
  return e && !bad.some(d => e.endsWith(`@${d}`));
});

B) Error path & retry

Turn Continue On Fail off on Email while testing, on in production.
Add a second Update node on the error path to set sent = Error and log error_message.

C) Personalization

Add dynamic snippets: {{ $json.company }}, {{ $json.name }}
Rotate 2–3 subject lines for better deliverability.

Deliverability Checklist (worth it)

SPF: include your sending host/IP.
DKIM: sign mail; verify DKIM-Signature exists in headers.
DMARC: start with p=none and a rua= address to receive reports.
Warm-up: start with ~50/day, grow to 150/day.
Unsubscribe: include a clear opt-out line.
Microsoft (Outlook/Hotmail): if blocked (S3140), use the Microsoft delisting portal or relay Microsoft-bound mail via SES/SendGrid/Mailgun until delisted.

Troubleshooting Quick Wins

Emails not arriving
- Check n8n Send email node output for errors.
- On your server: cssCopyEditsudo journalctl -u postfix -f postqueue -p
- If outbound port 25 is blocked by your VPS, configure Postfix to relay via SES/SendGrid/Mailgun on 587.
Update node says “Unable to parse range … D0”
- You tried to update row 0. Switch to match on email, or ensure you pass a valid row_number (≥1).
- Using the expression javascriptCopyEdit={{ $('Loop Over Items').item.json.email }} eliminates row index problems entirely.
It sends more than 5
- You wired Done → Send. Use the Loop output.
- Leave the next batch port unconnected.
- Cron at 0 */20 8-17 * * * gives ~30 runs/day × 5 = ~150.

Copy/Paste Expressions

To (Email node):

bashCopyEdit={{ $json.email }}

Match by email (Update node):

javascriptCopyEdit={{ $('Loop Over Items').item.json.email }}

Cron (business hours):

markdownCopyEdit0 */20 8-17 * * *

Want us to set it up for you?

Xtream Solutions can deploy this workflow on your server (or ours), wire up a reputable SMTP relay, add analytics, and integrate replies/unsubscribes back into your CRM. If you’d like a done-for-you build, we’ll scope it in one quick call.

Ask ChatGPT

How to Send 150 Personalized Emails/Day with n8n + Google Sheets (and Auto-Mark “sent”)