Queue and Retry Failed Report Emails

A report job that builds a workbook and emails it in the same breath has a fragile last step. If the mail server is unreachable for ten minutes, the email is simply lost — or, if the job is wrapped in a retry, the whole report is rebuilt and some recipients get it twice. If one address on the list has been deleted, the server's rejection can abort delivery to everyone after it. And the next morning nobody can say who actually received the report. The pattern that solves all three is old and simple: an outbox. The job writes one row per recipient into a small database and finishes; a separate step delivers whatever is due, records the result for each recipient, retries temporary failures later and gives up cleanly on permanent ones. This page builds that with SQLite and smtplib and tests it against a local SMTP server that misbehaves on purpose. It belongs to Emailing Excel Reports with smtplib.

Prerequisites

pip install openpyxl aiosmtpd   # aiosmtpd only for the local test server

SQLite and smtplib are in the standard library. The outputs below come from a local aiosmtpd server on port 8025 that accepts most recipients, rejects one address permanently (550 User unknown) and makes another wait (451 try again later) twice before accepting it — the mix of responses a real mail server produces. Setting up such a server is covered in Test Report Emails with a Local SMTP Server.

Step 1 — The outbox table

One row per message and recipient, with its delivery state:

import sqlite3
from datetime import datetime, timedelta

SCHEMA = """
CREATE TABLE IF NOT EXISTS outbox (
    id           INTEGER PRIMARY KEY,
    recipient    TEXT NOT NULL,
    subject      TEXT NOT NULL,
    body         TEXT NOT NULL,
    attachment   TEXT,
    status       TEXT NOT NULL DEFAULT 'pending',     -- pending, sent, failed
    attempts     INTEGER NOT NULL DEFAULT 0,
    next_attempt TEXT NOT NULL,
    last_error   TEXT,
    sent_at      TEXT,
    UNIQUE (recipient, subject)
)"""

db = sqlite3.connect("outbox.db")
db.execute(SCHEMA)

def enqueue(db, recipients, subject, body, attachment, now):
    with db:
        cur = db.executemany(
            "INSERT OR IGNORE INTO outbox (recipient, subject, body, attachment, next_attempt) VALUES (?, ?, ?, ?, ?)",
            [(r, subject, body, attachment, now.isoformat()) for r in recipients])
    return cur.rowcount

t0 = datetime(2026, 10, 2, 7, 0)
team = ["finance@example.com", "ops@example.com", "old.manager@example.com", "ceo@example.com"]
subject = "Sales report — September 2026"
print("queued:", enqueue(db, team, subject, "The September sales report is attached.", "sales_2026-09.xlsx", t0))
print("queued on rerun:", enqueue(db, team, subject, "The September sales report is attached.", "sales_2026-09.xlsx", t0))

The UNIQUE (recipient, subject) constraint with INSERT OR IGNORE makes enqueueing idempotent: if the report job runs twice — a manual rerun, a scheduler hiccup — the second run adds nothing, so nobody gets the report twice. Include the reporting period in the subject (or add a separate report-key column) so next month's report is a new message. The attachment is stored as a path, not as bytes: the workbook stays on disk until delivery, and the table stays small. The report job's work ends here; it does not wait for the mail server.

Step 2 — Deliver what is due, one recipient at a time

The delivery step opens one SMTP connection, sends each due row separately, and classifies every failure:

import smtplib
from email.message import EmailMessage
from pathlib import Path

MAX_ATTEMPTS, BASE_DELAY = 5, timedelta(minutes=5)

def build(row):
    msg = EmailMessage()
    msg["From"], msg["To"], msg["Subject"] = "reports@example.com", row["recipient"], row["subject"]
    msg.set_content(row["body"])
    if row["attachment"]:
        path = Path(row["attachment"])
        msg.add_attachment(path.read_bytes(), maintype="application",
                           subtype="vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename=path.name)
    return msg

def defer(db, row, error, now):
    attempts = row["attempts"] + 1
    status = "failed" if attempts >= MAX_ATTEMPTS else "pending"
    db.execute("UPDATE outbox SET attempts=?, status=?, next_attempt=?, last_error=? WHERE id=?",
               (attempts, status, (now + BASE_DELAY * 2 ** (attempts - 1)).isoformat(), error, row["id"]))

def deliver_due(db, now, host="127.0.0.1", port=8025):
    db.row_factory = sqlite3.Row
    due = db.execute("SELECT * FROM outbox WHERE status='pending' AND next_attempt<=? ORDER BY id", (now.isoformat(),)).fetchall()
    if not due:
        return {}
    outcome = {}
    try:
        with smtplib.SMTP(host, port, timeout=30) as smtp, db:
            for row in due:
                try:
                    smtp.send_message(build(row))
                    db.execute("UPDATE outbox SET status='sent', attempts=attempts+1, sent_at=? WHERE id=?", (now.isoformat(), row["id"]))
                    outcome[row["recipient"]] = "sent"
                except smtplib.SMTPRecipientsRefused as e:
                    code, text = e.recipients[row["recipient"]]
                    if code >= 500:
                        db.execute("UPDATE outbox SET status='failed', attempts=attempts+1, last_error=? WHERE id=?",
                                   (f"{code} {text.decode()}", row["id"]))
                        outcome[row["recipient"]] = f"failed ({code})"
                    else:
                        defer(db, row, f"{code} {text.decode()}", now)
                        outcome[row["recipient"]] = f"retry later ({code})"
    except (OSError, smtplib.SMTPException) as e:
        with db:
            for row in due:
                if row["recipient"] not in outcome:
                    defer(db, row, f"server: {e}", now)
                    outcome[row["recipient"]] = "retry later (server)"
    return outcome

print(deliver_due(db, t0))

SMTPRecipientsRefused carries the server's code for the refused address. Codes in the 400s are temporary — mailbox busy, greylisting, rate limits — and are worth retrying. Codes in the 500s are permanent — the address does not exist, the domain refuses mail — and retrying only produces the same rejection, so the row is marked failed at once. Sending each recipient separately keeps one bad address from affecting the others, and lets each row's state be recorded exactly. If the connection itself fails — server down, DNS failure, timeout — every row that was not yet delivered is deferred together. The first run delivers to finance and the CEO, fails the deleted address permanently and schedules ops for a retry in five minutes.

Step 3 — Run the delivery step on a schedule

Delivery runs every few minutes from cron or a scheduler; each run sends only what is due. Here the clock is moved forward by hand to show the backoff:

for minutes in [1, 6, 12, 20]:
    now = t0 + timedelta(minutes=minutes)
    print(f"t+{minutes:>2} min:", deliver_due(db, now) or "nothing due")

for row in db.execute("SELECT recipient, status, attempts, last_error FROM outbox ORDER BY id"):
    print(f"  {row['recipient']:<26} {row['status']:<8} attempts={row['attempts']}  {row['last_error'] or ''}")
print("server received:", sorted(handler.delivered))

At one minute nothing is due. At six minutes ops is tried again and deferred a second time — now for ten minutes, the doubled delay. At twelve minutes nothing is due; at twenty, ops is accepted. The final table shows exactly who received the report, after how many attempts, and why the deleted address failed; ops keeps its last error as a record of the earlier deferrals. The server received three messages, one per valid recipient, with no duplicates. With five attempts and a doubling delay starting at five minutes, a message is retried for about 75 minutes before being given up, which rides out most mail server restarts and network blips; adjust both numbers to the report's deadline.

Step 4 — Survive a server outage

Stop the mail server, queue next month's report and run delivery:

controller.stop()
enqueue(db, ["finance@example.com"], "Sales report — October 2026", "The October sales report is attached.", "sales_2026-09.xlsx", t0 + timedelta(hours=1))
print(deliver_due(db, t0 + timedelta(hours=1)))
print(db.execute("SELECT status, attempts, next_attempt FROM outbox WHERE subject LIKE '%October%'").fetchone()[:])
db.close()

The connection is refused, so the October row stays pending with one attempt recorded and a retry scheduled five minutes later. Nothing is lost and nothing needs rebuilding: when the server is back, the next scheduled delivery sends it. Rows that end up failed should be reported — an alert, or a daily summary to the report owner listing undeliverable addresses — as described in Send an Alert When an Excel Report Job Fails. Deleting old sent rows after a retention period keeps the table small while preserving a delivery record for as long as you need it.

Common pitfalls

• Sending inline from the report job. A mail outage loses the report or forces a rebuild.
• Retrying 5xx rejections. They are permanent; mark them failed and report them.
• One message to all recipients. One bad address can block the rest; send per recipient.
• Duplicate sends on rerun. Use a unique key and INSERT OR IGNORE.
• Silent failures. Alert on rows that end in failed.

Conclusion

Decouple report generation from delivery with an outbox: the job inserts one row per recipient with a unique key, so reruns cannot duplicate, and a scheduled delivery step sends each due row on its own. Mark accepted rows sent, retry 4xx and connection failures with a doubling delay up to a limit, mark 5xx rejections failed immediately, and alert on failures. The result survives mail outages, isolates bad addresses and leaves a record of who received what.

Frequently asked questions

How do I retry failed emails in Python? Store each message in a database table with a status and next-attempt time, and run a delivery step on a schedule that sends due rows and reschedules temporary failures with a growing delay.

Which SMTP errors should be retried? 4xx replies and connection failures are temporary and worth retrying; 5xx replies such as 550 User unknown are permanent and should be marked failed.

How do I avoid sending a report email twice? Give each message a unique key, such as recipient plus subject or report period, and insert with INSERT OR IGNORE so a rerun of the job adds nothing.

Why send to each recipient separately? So one refused address cannot stop delivery to the others, and each recipient's delivery state can be recorded exactly.

Related

• Up one level: Emailing Excel Reports with smtplib — sending reports from Python.
• Retry a Failed Excel Report Job in Python — in-process retries for other steps.
• Send Each Recipient Their Own Excel Report — per-recipient attachments.
• Make an Excel Report Job Safe to Rerun — idempotence for the whole job.