Email a daily report with an attachment using Python smtplib

Ask IT two questions first

Before writing any code, find out which mail server and port your organisation allows for automated sending, and which account should send. Many companies block personal mailboxes from sending by script, require app passwords, or provide a shared account such as an MIS mailbox for exactly this purpose. A script that works on your laptop with your own login and stops the day you change your password is not a solution.

You will need four values: the SMTP host, the port (587 with STARTTLS is the common choice), the username and the password. Put them in environment variables, never in the file.

Building and sending the message

The branch figures below are synthetic, and the addresses use example.com, a domain reserved for documentation, so nothing reaches a real inbox. Unless SEND_REPORT=yes is set along with the server details, the script only writes outbox.eml.

import os
import smtplib
import ssl
from datetime import date
from email import policy
from email.message import EmailMessage
from email.parser import BytesParser
from pathlib import Path
import pandas as pd

REPORT_DATE = date(2026, 9, 26)

# 1. Build the report file. Synthetic figures.
df = pd.DataFrame({
    "branch": ["Noida Sec 18", "Ghaziabad", "Gurugram"],
    "sales": [412500, 187250, 356900],
    "invoices": [58, 31, 44],
})
report = Path(f"branch_sales_{REPORT_DATE:%Y%m%d}.csv")
df.to_csv(report, index=False, encoding="utf-8-sig")      # opens cleanly in Excel

# 2. Build the message. Recipients come from config, never from the data file.
cfg = {
    "host": os.environ.get("SMTP_HOST", ""),               # e.g. your mail provider's host
    "port": int(os.environ.get("SMTP_PORT", "587")),
    "user": os.environ.get("SMTP_USER", ""),
    "password": os.environ.get("SMTP_PASSWORD", ""),       # never hard-code this
    "sender": "mis@example.com",
    "to": ["regional.manager@example.com"],
    "cc": ["accounts@example.com"],
}

msg = EmailMessage()
msg["Subject"] = f"Branch sales {REPORT_DATE:%d %b %Y} (provisional)"
msg["From"] = cfg["sender"]
msg["To"] = ", ".join(cfg["to"])
msg["Cc"] = ", ".join(cfg["cc"])
total = int(df["sales"].sum())
s = str(total)                                   # Indian grouping: 9,56,650
head, tail = s[:-3], s[-3:]
total_text = ",".join([head[max(0, i - 2):i] for i in range(len(head), 0, -2)][::-1] + [tail])
msg.set_content(
    f"Hello,\n\nTotal sales on {REPORT_DATE:%d %b %Y}: ₹{total_text} across {len(df)} branches.\n"
    "Branch detail is attached. Figures are provisional until the day book is closed.\n\n"
    "This is an automated message. Reply to this email with any questions.\n")
msg.add_attachment(report.read_bytes(), maintype="text", subtype="csv", filename=report.name)

# 3. Send only when configured; otherwise save a copy for review (a dry run).
def send(message: EmailMessage) -> None:
    context = ssl.create_default_context()
    with smtplib.SMTP(cfg["host"], cfg["port"], timeout=30) as s:
        s.starttls(context=context)
        s.login(cfg["user"], cfg["password"])
        s.send_message(message)                             # To and Cc both receive it

if cfg["host"] and cfg["password"] and os.environ.get("SEND_REPORT") == "yes":
    send(msg)
    print("sent")
else:
    Path("outbox.eml").write_bytes(msg.as_bytes())
    print("dry run: saved outbox.eml")

# 4. Check what would be sent: subject, recipients and the attachment round-trip.
back = BytesParser(policy=policy.default).parsebytes(msg.as_bytes())
att = next(back.iter_attachments())
print(back["Subject"], "|", back["To"], "| cc", back["Cc"])
data = att.get_payload(decode=True)
print("attachment:", att.get_filename(), len(data), "bytes")
assert data == report.read_bytes()          # the file arrives byte for byte

On a machine without mail settings it prints "dry run: saved outbox.eml", then the subject, recipients and attachment details read back from the built message. Double-click the .eml file and most desktop mail clients open it as it would appear to the recipient.

What each part protects against

`EmailMessage`, not string building. Writing MIME headers and boundaries by hand breaks on the first attachment or the first non-ASCII character, such as the rupee sign in the body. EmailMessage with set_content and add_attachment handles encoding correctly.

`starttls()` before `login()`. On port 587 the connection starts unencrypted. Upgrading it first means the password travels encrypted. ssl.create_default_context() also checks the server's certificate, which is what stops a lookalike server from collecting the password. If your server uses port 465 instead, use smtplib.SMTP_SSL and skip starttls.

`send_message` reads the headers. It sends to everyone in To, Cc and Bcc. If you pass recipients separately with sendmail, it is easy to put someone in the Cc header who never actually receives the mail.

A deliberate switch to send. The environment variable SEND_REPORT=yes means that running the script while testing, or on a colleague's machine, produces a file rather than an email to the regional manager. Accidental sends of half-finished reports are a more common failure than code errors.

The round-trip check. The last lines parse the built message and confirm the attachment's bytes are identical to the report file. It is a quick way to catch an attachment built from the wrong path or yesterday's file.

Attaching an Excel workbook instead

The example attaches a CSV written with utf-8-sig, which Excel opens directly. To attach an .xlsx, keep the same call and change the MIME type:

maintype="application",
subtype="vnd.openxmlformats-officedocument.spreadsheetml.sheet"

Name files with the report date, branch_sales_20260926.xlsx rather than report.xlsx, so that recipients who save attachments into one folder do not overwrite last week's.

Scheduling and failure handling

Run the script from Windows Task Scheduler, cron, or whatever scheduler your team already uses, after the data it depends on is ready, not at a fixed time that might come before the day close. Two habits save embarrassment:

• Refuse to send stale data. Before building the message, check that the source file or table contains rows for the report date. If not, send a short "report delayed" note to yourself, not the report.
• Log every run. Append the date, recipient list, attachment name and result to a log file. When someone says they did not get Tuesday's report, you can answer in a minute.

Keep the list of recipients in a configuration file that the business owner can see, not buried in code. People join and leave distribution lists more often than the report changes.

Scheduling and handing over

Automated emails are the last step of a reporting pipeline, after the numbers have been checked. The same data often goes out as a short phone message too; see generate a branch-wise daily sales summary for WhatsApp. If each branch should get only its own figures, first split a master sheet into one file per branch. Reporting automation with sensible safeguards, including dry runs and control totals, is taught in our Data Analyst course.