Send a Weekly Digest of Several Reports in One Email

A team that automates its reporting soon has a problem of its own making: every Monday morning brings five or six separate emails — sales, stock, debtors, open orders, web traffic — each with an attachment, each sent by a different job at a slightly different time. People stop opening them. A weekly digest fixes that by sending one email that summarises every report in a few lines, with the headline figure from each, and attaches or links the full workbooks for anyone who wants the detail. It also makes failures visible: when one of the reports was not produced, the digest says so, rather than relying on someone noticing an email that never arrived. This page builds a digest from a small manifest of the week's reports: it reads a headline figure from each workbook, assembles one HTML email with a plain-text version, keeps attachments under a size limit, flags missing reports and writes a preview before anything is sent. It belongs to Emailing Excel Reports with smtplib.

Prerequisites

pip install pandas openpyxl

Four report jobs have written their workbooks for week 41 of 2026 into one folder; a fifth, open orders, failed and wrote nothing. Each workbook has a Summary sheet from which the digest takes its headline. The message is built with the standard library's email package; sending it is the same as for any report email.

Step 1 — A manifest that says what to expect

from pathlib import Path
import pandas as pd

WEEK = "2026-W41"
folder = Path("reports") / WEEK
MANIFEST = [
    {"title": "Weekly sales", "file": "weekly_sales.xlsx", "owner": "Sales ops",
     "headline": lambda df: f"Revenue £{df['Revenue'].sum():,.0f} across {len(df)} regions"},
    {"title": "Stock below reorder level", "file": "stock_levels.xlsx", "owner": "Warehouse",
     "headline": lambda df: f"{int(df['Below reorder'].sum())} SKUs below reorder level"},
    {"title": "Aged debtors", "file": "aged_debtors.xlsx", "owner": "Credit control",
     "headline": lambda df: f"£{df['Amount'].sum():,.0f} owed, £{df.loc[df['Bucket'] == '90+', 'Amount'].sum():,.0f} over 90 days"},
    {"title": "Open orders", "file": "open_orders.xlsx", "owner": "Customer service",
     "headline": lambda df: f"{len(df)} open orders"},
    {"title": "Web traffic", "file": "web_traffic.xlsx", "owner": "Marketing",
     "headline": lambda df: f"{int(df.loc[0, 'Value']):,} sessions"},
]

items = []
for m in MANIFEST:
    path = folder / m["file"]
    item = {**m, "path": path, "exists": path.exists()}
    if item["exists"]:
        try:
            item["text"] = m["headline"](pd.read_excel(path, sheet_name="Summary"))
            item["size"] = path.stat().st_size
        except Exception as e:
            item["exists"], item["text"] = False, f"could not be read ({type(e).__name__})"
    else:
        item["text"] = "not produced this week"
    items.append(item)
for i in items:
    print(f"{i['title']:28} {'OK ' if i['exists'] else 'MISSING'} {i['text']}")

The manifest is the list of reports the digest expects, not whatever happens to be in the folder. Each entry names the report, its file, who owns it and how to turn its summary sheet into one headline sentence. Driving the digest from the manifest is what lets it notice absences: the open-orders report was not written this week, and the digest says not produced this week instead of silently leaving it out. A report that exists but cannot be read — a corrupt file, a renamed sheet — is treated the same way, with the reason. The headline functions are deliberately small; they read the figure each report's owner considers most important, and anything more belongs in the report itself.

Step 2 — Attach what fits, link the rest

LIMIT = 3 * 1024 * 1024                              # total attachment budget for the email
SHARE = "https://intranet.example.com/reports"       # where every report is also published
budget = LIMIT
for i in sorted((i for i in items if i["exists"]), key=lambda i: i["size"]):
    if i["size"] <= budget:
        i["delivery"], budget = "attached", budget - i["size"]
    else:
        i["delivery"] = "link"
    i["url"] = f"{SHARE}/{WEEK}/{i['file']}"
print({i["title"]: (i.get("delivery"), round(i.get("size", 0) / 1024)) for i in items})

Mail servers reject or quietly drop messages above a size limit, often between 10 and 25 MB, and a digest that bundles every workbook can exceed it easily. The attachments are therefore allocated from a fixed budget, smallest first, so the most reports fit; anything that does not fit is linked instead, from the location where every report is published anyway. Here the three small workbooks, about 5 KB each, are attached and the web traffic workbook, 4.7 MB because of its raw data sheet, gets a link. Attachments are encoded in base64, which makes them about a third larger in the message than on disk, so the budget is set well below the real limit: in a test with the web traffic workbook attached, the 4.7 MB file produced a 6.4 MB email. For the general approach, see Email a Download Link Instead of an Excel Attachment.

Step 3 — One email, HTML and plain text

from email.message import EmailMessage
from html import escape

rows_html = []
for i in items:
    status = ("✓" if i["exists"] else "⚠")
    detail = (f'attached' if i.get("delivery") == "attached" else
              f'<a href="{escape(i["url"])}">open</a>' if i["exists"] else f'ask {escape(i["owner"])}')
    colour = "#222" if i["exists"] else "#C62828"
    rows_html.append(f'<tr><td style="padding:6px 10px">{status}</td><td style="padding:6px 10px"><b>{escape(i["title"])}</b></td>'
                     f'<td style="padding:6px 10px;color:{colour}">{escape(i["text"])}</td><td style="padding:6px 10px">{detail}</td></tr>')
missing = [i["title"] for i in items if not i["exists"]]
html = (f'<div style="font-family:Arial,sans-serif;font-size:14px">'
        f'<p>Weekly reports for <b>{WEEK}</b>: {len(items) - len(missing)} of {len(items)} produced.</p>'
        f'<table style="border-collapse:collapse">{"".join(rows_html)}</table>'
        f'<p style="color:#888;font-size:12px">All reports are also at {escape(SHARE)}/{WEEK}/</p></div>')
text = "\n".join([f"Weekly reports for {WEEK}: {len(items) - len(missing)} of {len(items)} produced.", ""] +
                 [f"- {i['title']}: {i['text']}" + (f" ({i['url']})" if i.get('delivery') == 'link' else "") for i in items])

msg = EmailMessage()
msg["Subject"] = f"Weekly reports {WEEK}" + (f" — {len(missing)} missing" if missing else "")
msg["From"], msg["To"] = "reports@example.com", "management@example.com"
msg.set_content(text)
msg.add_alternative(html, subtype="html")
for i in items:
    if i.get("delivery") == "attached":
        msg.add_attachment(i["path"].read_bytes(), maintype="application",
                           subtype="vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename=i["file"])
Path("digest_preview.eml").write_bytes(bytes(msg))
print(msg["Subject"], "|", round(len(bytes(msg)) / 1024), "KB")
print(text)

The message body is a short table: a tick or a warning sign, the report's name, its headline, and whether the workbook is attached or linked — or, for a missing report, who to ask. The opening line counts how many of the expected reports were produced, and the subject line says when something is missing, so the gap is visible even in an inbox list. Every value inserted into the HTML goes through html.escape, because report titles and figures can contain characters such as & that would otherwise break the markup. The plain-text alternative carries the same information for mail clients and notification previews that do not show HTML. The message is written to an .eml file as a preview — 24 KB with the three small workbooks attached — sending it is one call to an SMTP connection, as in Send Excel Report to Multiple Recipients in Python.

When to send it, and to whom

A digest works best on a fixed schedule after the last report job is due to finish, with a short grace period: if the reports normally finish by 06:30, send the digest at 07:00, and anything not there by then is reported as missing. The recipients are people who want the overview — managers, the leadership team — while each report's owners keep receiving their own report in full. Owners also get the most value from the missing line, so it is worth sending them a copy of the digest, or a separate alert, when their report is the one that failed; the pattern in Detect a Scheduled Report That Did Not Run covers that.

Common pitfalls

• Listing whatever is in the folder. Drive the digest from a manifest so absences show.
• Attaching everything. Keep a size budget and link the large files.
• Unescaped values in HTML. Pass every inserted value through html.escape.
• HTML only. Add a plain-text alternative with the same content.
• Missing reports buried. Say so in the subject line and in the opening sentence.

Conclusion

A weekly digest replaces a stream of report emails with one message: a manifest of the reports expected, a headline read from each workbook's summary sheet, attachments allocated from a size budget with large files linked instead, and an HTML table with a plain-text alternative. Missing or unreadable reports are named in the subject and the body, so a failed job is noticed the same morning, and the message is previewed as an .eml before it is sent.

Frequently asked questions

How do I combine several reports into one email in Python? Collect the reports from a manifest, build one HTML body with a section per report, and attach the files to a single EmailMessage.

How do I keep an email with attachments under the size limit? Allocate attachments from a fixed budget, smallest first, allowing for base64 growth, and link any file that does not fit.

How do I show missing reports in a digest? Drive the digest from a list of expected reports and mark any whose file is absent or unreadable.

Why add a plain-text version to an HTML email? Some mail clients and previews show only text, and spam filters treat HTML-only messages with suspicion.

Related

• Up one level: Emailing Excel Reports with smtplib — sending reports by email.
• Email an Excel Report with an HTML Summary Body — HTML email in depth.
• Personalise Report Emails with Jinja2 Templates — templated bodies.
• Attach Multiple Excel Files to One Email in Python — attachments.