Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Personalise Report Emails with Jinja2 Templates

Write the subject, plain-text and HTML body of report emails as Jinja2 templates instead of f-strings: one context per recipient, autoescaping for names and comments that contain HTML characters, StrictUndefined so a missing field stops the run, filters for money and percentages, conditional paragraphs, a shared layout, and previews written to disk before anything is sent.
Email delivery

The first version of a report email is usually an f-string: f"Hi {name}, attached is the {month} report." It works until the message needs a table of the recipient's figures, a paragraph that only appears when something is overdue, an HTML version with styling and a plain-text version that says the same thing — and a manager who wants to change the wording without editing Python. At that point the wording belongs in templates. Jinja2 is the standard Python template engine: templates are plain files with placeholders, loops and conditions, and the Python code only prepares the data for each recipient. This page builds personalised report emails for a list of branch managers with Jinja2, covering the details that matter for email: escaping, failing loudly on a missing field, number formatting, a shared layout, and checking the rendered messages before they go out. It belongs to Emailing Excel Reports with smtplib.

Prerequisites

Bash
pip install jinja2 pandas openpyxl

The data is one row per branch with the manager's name and email, revenue against target and overdue invoices. The emails are built with the standard library's email.message.EmailMessage; sending them is the same as for any other message and is covered in Send Each Recipient Their Own Excel Report with Python.

Step 1 — Put the templates in files

Python
from pathlib import Path

tpl = Path("templates")
tpl.mkdir(exist_ok=True)
(tpl / "subject.txt").write_text("{{ branch }}: {{ month }} results — {{ revenue | money }} ({{ vs_target | pct }} vs target)")
(tpl / "base.html").write_text("""<html><body style="font-family:Arial,sans-serif;font-size:14px;color:#222">
{% block content %}{% endblock %}
<p style="color:#888;font-size:12px">Sent automatically by the finance reporting job. Reply to this email with questions.</p>
</body></html>""")
(tpl / "results.html").write_text("""{% extends "base.html" %}
{% block content %}
<p>Hi {{ first_name }},</p>
<p>Here are the {{ month }} results for <b>{{ branch }}</b>, prepared for {{ manager }}.</p>
<table style="border-collapse:collapse">
  <tr><td style="padding:4px 12px">Revenue</td><td style="padding:4px 12px;text-align:right">{{ revenue | money }}</td></tr>
  <tr><td style="padding:4px 12px">Target</td><td style="padding:4px 12px;text-align:right">{{ target | money }}</td></tr>
  <tr><td style="padding:4px 12px">Against target</td>
      <td style="padding:4px 12px;text-align:right;color:{{ '#1a7f37' if vs_target >= 0 else '#c62828' }}">{{ vs_target | pct }}</td></tr>
</table>
{% if overdue_count %}
<p><b>{{ overdue_count }} invoice{{ 's' if overdue_count != 1 }}</b> totalling {{ overdue_amount | money }}
{{ 'are' if overdue_count != 1 else 'is' }} overdue. The list is on the <i>Overdue</i> sheet of the attached workbook.</p>
{% else %}
<p>No invoices are overdue — thank you.</p>
{% endif %}
{% endblock %}""")
(tpl / "results.txt").write_text("""Hi {{ first_name }},

Here are the {{ month }} results for {{ branch }}.

  Revenue         {{ "%12s" | format(revenue | money) }}
  Target          {{ "%12s" | format(target | money) }}
  Against target  {{ "%12s" | format(vs_target | pct) }}
{% if overdue_count %}
{{ overdue_count }} invoice{{ 's' if overdue_count != 1 }} totalling {{ overdue_amount | money }} {{ 'are' if overdue_count != 1 else 'is' }} overdue.
{% else %}
No invoices are overdue - thank you.
{% endif %}
-- Sent automatically by the finance reporting job.
""")
print(sorted(p.name for p in tpl.iterdir()))

There are four templates: the subject line, a shared HTML layout, the HTML body that extends it, and a plain-text body. Each template holds only wording and layout — no calculations — so whoever owns the message can edit it without touching the script. The HTML template uses {% extends %} and {% block %} so that the greeting, footer and styling live in one place when there are several kinds of report email. The plain-text template exists because every HTML email should also carry a text part: some mail clients and many notification previews show only the text, and spam filters treat HTML-only messages with suspicion. Writing it as its own template, rather than stripping tags from the HTML, gives a version that reads properly.

From a spreadsheet row to an email Each row of the results sheet becomes a context dictionary. The same context renders the subject, the text body and the HTML body, which are assembled into one message with the workbook attached. Results row one per branch Context dict names, numbers, flags Render templates subject, text, HTML EmailMessage text + HTML + xlsx Preview or send .eml first

Step 2 — An environment that escapes, fails loudly and formats numbers

Python
from jinja2 import Environment, FileSystemLoader, StrictUndefined, select_autoescape

env = Environment(
    loader=FileSystemLoader("templates"),
    autoescape=select_autoescape(enabled_extensions=("html",), default_for_string=False),
    undefined=StrictUndefined,
    trim_blocks=True, lstrip_blocks=True,
)
env.filters["money"] = lambda x: f"£{x:,.2f}"
env.filters["pct"] = lambda x: f"{x:+.1%}"

Three settings make Jinja2 safe for this job. Autoescaping for .html templates turns <, > and & in the data into HTML entities, so a manager name like Smith & Sons is displayed as written instead of being read as a broken tag — and so text copied from a spreadsheet can never inject markup into the email. It is off for .txt templates, where entities would show up literally. StrictUndefined makes any placeholder without a value raise an error; the default is to render it as an empty string, which produces emails that say "Hi ," and nobody notices until a recipient replies. trim_blocks and lstrip_blocks remove the blank lines that {% if %} tags would otherwise leave in the text version. The two filters keep number formatting in one place, so every email shows money and percentages the same way.

Step 3 — Build one context per recipient and render

Python
from email.message import EmailMessage
from email.utils import formataddr
import pandas as pd

results = pd.read_excel("branch_results.xlsx")
month = "September 2026"

def context(row):
    return {"branch": row["Branch"], "manager": row["Manager"], "first_name": row["Manager"].split()[0], "month": month,
            "revenue": row["Revenue"], "target": row["Target"], "vs_target": row["Revenue"] / row["Target"] - 1,
            "overdue_count": int(row["Overdue invoices"]), "overdue_amount": row["Overdue amount"]}

def build(row):
    ctx = context(row)
    msg = EmailMessage()
    msg["Subject"] = env.get_template("subject.txt").render(ctx).strip()
    msg["From"] = formataddr(("Finance Reporting", "reports@example.com"))
    msg["To"] = formataddr((row["Manager"], row["Email"]))
    msg.set_content(env.get_template("results.txt").render(ctx))
    msg.add_alternative(env.get_template("results.html").render(ctx), subtype="html")
    return msg

messages = [build(row) for _, row in results.iterrows()]
for m in messages:
    print(m["Subject"])

All the logic — which numbers, calculated how — lives in context(). It turns a spreadsheet row into a plain dictionary of exactly what the templates need, with names that make sense in the template rather than the column headings of the source. Keeping calculations out of the templates matters: a template that computes revenue / target - 1 itself is hard to test and easy to break when someone edits the wording. The subject is rendered from a template too and stripped, because a stray newline in a subject header makes the message invalid. The workbook attachment is added exactly as in any other report email, with msg.add_attachment(...).

Step 4 — Check the rendered messages before sending

Python
from pathlib import Path

out = Path("preview")
out.mkdir(exist_ok=True)
for row, m in zip(results.itertuples(), messages):
    (out / f"{row.Branch}.eml").write_bytes(bytes(m))
    html = m.get_body(preferencelist=("html",)).get_content()
    line = next(l for l in html.splitlines() if "prepared for" in l)
    print(f"{row.Branch:8}", line.split("prepared for ")[1])
print(messages[1].get_body(preferencelist=("plain",)).get_content())

Writing each message to an .eml file gives a complete preview: double-clicking one opens it in the mail client exactly as the recipient will see it, with both parts and any attachments. Reviewing the previews on the first run of a new template, or after a wording change, catches the problems that tests miss — a paragraph that reads awkwardly, a figure in the wrong place, a table that looks wrong in Outlook. The printed lines show that the Cardiff contact name, Smith & Sons , reached the HTML part as Smith &amp; Sons &lt;Agency&gt; — displayed correctly by the mail client rather than swallowed as an unknown tag — while Tom O'Neill's apostrophe is encoded harmlessly; and the text body of the Bristol message shows the "nothing overdue" branch of the condition.

The three rendered subjects Leeds is 4.2 percent over target with three overdue invoices, Bristol 5.9 percent under target with none overdue, and Cardiff 1.3 percent under target with one overdue invoice. Each line is rendered from the same subject template. A B 1 Branch Subject line 2 Leeds Leeds: September 2026 results — £182,340.50 (+4.2% vs target) 3 Bristol Bristol: September 2026 results — £141,200.00 (-5.9% vs target) 4 Cardiff Cardiff: September 2026 results — £98,650.25 (-1.3% vs target)

Step 5 — Catch a missing field before the run sends anything

Python
from jinja2 import UndefinedError

try:
    env.get_template("results.html").render({k: v for k, v in context(results.iloc[0]).items() if k != "first_name"})
except UndefinedError as e:
    print("render failed:", e)

With StrictUndefined, a template that refers to a value the context does not provide fails with a clear message naming it. Because every message is rendered before any is sent — the messages list in step 3 is complete before the first send_message — a mistake in one recipient's data or a renamed placeholder in a template stops the whole run, instead of sending half the emails correctly and the rest with gaps. Pair this with a log of what was sent so a rerun after a fix does not send duplicates.

Who edits what

The point of templates is to divide the work cleanly. The person who owns the message — often in finance or operations rather than development — edits the .txt and .html files: the greeting, the wording, which figures appear and in what order. The developer owns context() and the filters, which decide what values exist and how they are calculated and formatted. A useful convention is a short comment at the top of each template listing the variables available to it, so editors know what they can use without reading the Python. Keep the templates under version control alongside the script, so a change of wording is reviewed and can be undone like any other change.

Template or code Wording, layout, which figures appear and conditional paragraphs belong in the templates. Calculations, data access, number formatting rules and who receives which message belong in the Python code. In the templates greeting and wording order of the figures conditional paragraphs styling of the HTML In the Python code reading the results calculations such as vs target money and percent filters recipients and sending

Common pitfalls

  • HTML templates without autoescape. Data with & or < breaks the layout; enable it for .html.
  • Silent blanks. The default undefined renders as nothing; use StrictUndefined.
  • Calculations in templates. Prepare values in Python and pass them in the context.
  • HTML-only emails. Add a plain-text part rendered from its own template.
  • Newlines in the subject. Strip the rendered subject before setting the header.

Conclusion

Jinja2 separates what a report email says from how its data is prepared. Put the subject, text and HTML bodies in template files with a shared layout, create the environment with autoescape for HTML, StrictUndefined and filters for money and percentages, build one context dictionary per recipient, render every message before sending any, and preview them as .eml files the first time a template changes.

Frequently asked questions

How do I use Jinja2 for email templates in Python? Create an Environment with a FileSystemLoader, render the subject, text and HTML templates with a context dictionary, and put the results into an EmailMessage.

How do I make Jinja2 fail when a variable is missing? Pass undefined=StrictUndefined to the Environment; rendering then raises UndefinedError instead of inserting an empty string.

Should autoescape be on for email templates? Yes for HTML templates, so that characters like & and < in the data are displayed safely; leave it off for plain-text templates.

How do I format currency in a Jinja2 template? Register a filter in Python, such as env.filters["money"], and use it in the template as {{ revenue | money }}.

More on email delivery

More on email delivery →