Send Each Recipient Their Own Excel Report with Python

A single report emailed to everyone is simple. The more useful version — each regional manager gets only their region, each client only their account, each store manager only their store — is also the riskier one: the failure mode is not a missing email but someone receiving figures they should never have seen. This page, part of Emailing Excel Reports with smtplib, builds a per-recipient mailing that checks its mapping before sending anything, builds each workbook in memory, survives a rejected address, can be rerun safely and verifies what went out.

When everyone receives the same file, Send an Excel Report to Multiple Recipients is all you need.

Prerequisites

pip install pandas openpyxl

sales_2026-09.xlsx has September revenue by region and customer. recipients.xlsx maps each region to a manager's name and address; one address will be rejected by the mail server. SMTP settings come from environment variables — SMTP_HOST, SMTP_PORT, SMTP_TLS, and in production SMTP_USER and SMTP_PASSWORD. The examples run against a local test server, set up as in Test Report Emails with a Local SMTP Server.

Step 1 — Check the mapping before anything else

Every problem in the mapping is a potential leak or a missed recipient, and every one can be caught before a single message is sent:

import re
import pandas as pd

sales = pd.read_excel("sales_2026-09.xlsx")
recipients = pd.read_excel("recipients.xlsx")

problems = []
dupes = recipients[recipients["Region"].duplicated(keep=False)]
problems += [f"region {r} has more than one recipient" for r in dupes["Region"].unique()]
problems += [f"region {r} in the data has no recipient" for r in sorted(set(sales["Region"]) - set(recipients["Region"]))]
problems += [f"recipient region {r} has no data" for r in sorted(set(recipients["Region"]) - set(sales["Region"]))]
problems += [f"bad address {e!r}" for e in recipients["Email"] if not re.fullmatch(r"[^@\s]+@[^@\s]+\.[A-Za-z]{2,}", str(e))]
if problems:
    raise SystemExit("mapping problems:\n  " + "\n  ".join(problems))
print("mapping OK:", len(recipients), "recipients for", sales["Region"].nunique(), "regions")

A region with two recipients is ambiguous, a region with none means someone is missed, and a recipient whose region has no data would get an empty report — or, with a careless filter, all of it. Stopping on any of them is the right default.

Step 2 — Build each workbook in memory and preview the plan

Build every recipient's workbook as bytes, without writing files to disk, and check each one contains only the recipient's own rows. Print the plan, so a person can confirm it before the first run:

import io

def build_workbook(rows):
    buf = io.BytesIO()
    with pd.ExcelWriter(buf, engine="openpyxl") as writer:
        rows.to_excel(writer, sheet_name="Sales", index=False)
    return buf.getvalue()

plan = []
for r in recipients.itertuples(index=False):
    rows = sales[sales["Region"] == r.Region]
    data = build_workbook(rows)
    check = pd.read_excel(io.BytesIO(data))
    assert set(check["Region"]) == {r.Region}, f"{r.Email} workbook contains other regions"
    plan.append({"name": r.Name, "email": r.Email, "region": r.Region, "rows": len(rows),
                 "total": rows["Revenue"].sum(), "data": data})

print(pd.DataFrame(plan).drop(columns="data").to_string(index=False))

The assertion re-reads each workbook and confirms it holds exactly one region — a direct check on the thing that matters, rather than trusting the filter. In a scheduled run, save the plan table to the log; the first time a new mapping is used, run with sending switched off and have someone read it.

Step 3 — Send over one connection, one recipient at a time

Open one SMTP connection for the whole batch — logging in once per message is slow and some servers throttle it — and send each message separately, so each recipient sees only their own address. Catch failures per recipient and carry on:

import os
import smtplib
import time
from email.message import EmailMessage

XLSX = ("application", "vnd.openxmlformats-officedocument.spreadsheetml.sheet")

def make_message(p, period="September 2026"):
    msg = EmailMessage()
    msg["From"] = "reports@example.com"
    msg["To"] = f'{p["name"]} <{p["email"]}>'
    msg["Subject"] = f'{p["region"]} sales — {period}'
    msg.set_content(f'Hello {p["name"].split()[0]},\n\nYour {p["region"]} sales for {period} are attached: '
                    f'{p["rows"]} customers, {p["total"]:,.0f} in total.\n')
    msg.add_attachment(p["data"], maintype=XLSX[0], subtype=XLSX[1], filename=f'sales_{p["region"]}_2026-09.xlsx')
    return msg

results = []
with smtplib.SMTP(os.environ["SMTP_HOST"], int(os.environ["SMTP_PORT"]), timeout=30) as smtp:
    if os.environ.get("SMTP_TLS", "1") == "1":
        smtp.starttls()
    if os.environ.get("SMTP_USER"):
        smtp.login(os.environ["SMTP_USER"], os.environ["SMTP_PASSWORD"])
    for p in plan:
        try:
            smtp.send_message(make_message(p))
            results.append({"email": p["email"], "status": "sent", "detail": ""})
        except smtplib.SMTPRecipientsRefused as exc:
            code, reason = next(iter(exc.recipients.values()))
            results.append({"email": p["email"], "status": "refused", "detail": f"{code} {reason.decode()}"})
        except smtplib.SMTPException as exc:
            results.append({"email": p["email"], "status": "error", "detail": str(exc)})
        time.sleep(0.2)

print(pd.DataFrame(results).to_string(index=False))

The server refused Cai's address with a 550 "mailbox does not exist"; the other three were sent. SMTPRecipientsRefused carries the server's code and reason for each refused address, which is exactly what the person fixing the mapping needs. The short pause between messages keeps well under the sending limits most providers apply; large batches need a longer one, or a transactional email service.

Step 4 — Log sends so a rerun does not resend

If the job stops halfway — a network drop, a crash — rerunning it should send the remaining messages without repeating the ones already delivered. Append every result to a log keyed by period and address, and skip recipients already marked sent:

from pathlib import Path

log_path = Path("send_log.csv")
period = "2026-09"
log = pd.DataFrame(results).assign(period=period, at=pd.Timestamp("2026-10-01 07:00"))
log.to_csv(log_path, mode="a", header=not log_path.exists(), index=False)

already = pd.read_csv(log_path)
sent_before = set(already.loc[(already["period"] == period) & (already["status"] == "sent"), "email"])
remaining = [p for p in plan if p["email"] not in sent_before]
print("sent:", sorted(sent_before))
print("a rerun would send to:", [p["email"] for p in remaining])

A rerun would only try Cai again — after someone corrects the address in the mapping, it would go to the new address, because the log is keyed on the address that was actually sent to. Failures stay in the log too, so the end-of-run summary can list them.

Step 5 — Verify what went out

For the test run, the local server kept every message — _handler and _ctl are the capturing handler and controller from the test server setup — so check each one went to one recipient and carries only their region. In production, run the same check against a staging server before switching the job to the real one:

from email import message_from_bytes
from email.policy import default

for rcpts, raw in _handler.messages:
    msg = message_from_bytes(raw, policy=default)
    part = next(msg.iter_attachments())
    regions = set(pd.read_excel(io.BytesIO(part.get_content()))["Region"])
    print(rcpts, msg["Subject"], sorted(regions))
_ctl.stop()

Three messages, each to a single address, each with exactly its own region. That check is what makes it safe to add recipients and regions over time without re-reading every message by hand.

Common pitfalls

• Sending with all recipients in To. Everyone sees everyone else's address; send one message each.
• An unchecked mapping. Duplicate or missing regions leak or drop data; validate before sending.
• Trusting the filter. Re-read each built workbook and check it holds only the recipient's rows.
• One failure stopping the batch. Catch errors per recipient and report them at the end.
• Resending on rerun. Keep a send log keyed by period and address.
• Writing temporary files. Build workbooks in memory; files left on disk are another place data can leak.

Performance notes

Building workbooks is fast; sending is limited by the mail server. One connection for the batch avoids repeated logins. For hundreds of recipients, respect the provider's rate limits and consider a transactional email API, which also reports bounces that arrive after the SMTP conversation has ended.

Conclusion

Validate the recipient mapping — one recipient per group, no groups without a recipient, valid addresses — before sending anything. Build each recipient's workbook in memory, re-read it to confirm it contains only their rows, and preview the plan. Send one message per recipient over one SMTP connection, catching refusals per recipient, log every result so a rerun skips people already sent to, and check the captured or staged messages to prove nobody received someone else's data.

Frequently asked questions

How do I email each person a different Excel file with Python? Group the data by the column that decides who sees what, build a workbook for each group in memory with io.BytesIO, and send one message per recipient over a single smtplib connection, attaching that recipient's bytes.

How do I stop one bad address from failing the whole batch? Wrap each send in try/except for smtplib.SMTPRecipientsRefused and other SMTPException errors, record the failure in the send log, and carry on with the next recipient. Report the failures at the end.

How do I avoid emailing someone the wrong region''s data? Validate the mapping before sending — every region has exactly one recipient, every recipient maps to a region that exists — print or save a preview of who gets which file, and after building each workbook check it contains only that recipient's rows.

What if the job crashes halfway through the list? Keep a send log keyed by period and recipient. On a rerun, skip anyone already logged as sent, so the people who received the report do not get it twice and the rest still get theirs.

Related

• Up one level: Emailing Excel Reports with smtplib — sending reports by email.
• Split One Excel Sheet into Multiple Files by Value — the split without the email.
• Test Report Emails with a Local SMTP Server — the test server used here.
• Build a Sales Commission Report — per-rep statements to distribute this way.