Automate Weekly Summary Email Reports

A weekly summary email keeps stakeholders informed without any manual effort. This recipe shows how to write a script that queries your data, formats a plain-text or HTML report, and sends it via SMTP — then schedules it with cron and verifies delivery with a CronJobPro heartbeat. If the script crashes or the server never fires the job, you get an alert before anyone notices the silence.

Schedule

0 8 * * 1

Every Monday at 8:00 AM (server local time)

Setup

  1. 1

    Install dependencies

    Make sure Python 3.8+ is available on your server. The script uses only the standard library (smtplib, email, sqlite3/psycopg2), so no pip install is needed for the core. If your database is PostgreSQL, run: pip install psycopg2-binary

  2. 2

    Configure credentials via environment variables

    Never hard-code passwords. Add these to /etc/environment or your cron environment block: SMTP_HOST=smtp.yourprovider.com SMTP_PORT=587 SMTP_USER=reports@yourcompany.com SMTP_PASS=yourpassword REPORT_FROM=reports@yourcompany.com REPORT_TO=team@yourcompany.com DB_URL=postgresql://user:pass@localhost/mydb CRONJOBPRO_PING_URL=https://cronjobpro.com/ping/YOUR_TOKEN

  3. 3

    Save the script and make it executable

    Copy the script below to /usr/local/bin/weekly_report.py, then run: chmod +x /usr/local/bin/weekly_report.py Test it manually first: python3 /usr/local/bin/weekly_report.py Confirm the email arrives and the exit code is 0 before wiring up cron.

  4. 4

    Add the cron entry

    Open your crontab with: crontab -e Add this line (adjust path and timezone as needed): 0 8 * * 1 python3 /usr/local/bin/weekly_report.py >> /var/log/weekly_report.log 2>&1 Ensure the cron environment has the variables set in step 2. A safe way is to source a file at the top of the cron entry or use a wrapper shell script.

  5. 5

    Create a CronJobPro heartbeat monitor and wire it in

    In CronJobPro, create a new Heartbeat monitor with period set to 7 days and a grace period of 2 hours. Copy the generated ping URL (https://cronjobpro.com/ping/YOUR_TOKEN) into your CRONJOBPRO_PING_URL environment variable. The script already calls this URL on success. Set up an alert channel (email, Slack, etc.) so you are notified if the ping does not arrive within the expected window.

The script

python

#!/usr/bin/env python3
"""weekly_report.py — query data, build a summary, email it, ping heartbeat."""

import os
import smtplib
import urllib.request
import sys
from datetime import datetime, timedelta
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

# --- Configuration (loaded from environment) ---
SMTP_HOST = os.environ["SMTP_HOST"]
SMTP_PORT = int(os.environ.get("SMTP_PORT", "587"))
SMTP_USER = os.environ["SMTP_USER"]
SMTP_PASS = os.environ["SMTP_PASS"]
REPORT_FROM = os.environ["REPORT_FROM"]
REPORT_TO = os.environ["REPORT_TO"]  # comma-separated for multiple recipients
PING_URL = os.environ.get("CRONJOBPRO_PING_URL", "")


def fetch_report_data():
    """Replace this function with your real data query.
    Returns a list of dicts, each representing one row/metric.
    Example below uses SQLite; swap in psycopg2 for PostgreSQL.
    """
    import sqlite3  # or: import psycopg2

    week_ago = (datetime.utcnow() - timedelta(days=7)).strftime("%Y-%m-%d")

    # Example: SQLite
    # conn = sqlite3.connect("/var/data/myapp.db")
    # For PostgreSQL:
    # import psycopg2, os
    # conn = psycopg2.connect(os.environ["DB_URL"])

    # Stub data — replace with real query results:
    rows = [
        {"metric": "New signups", "value": 42},
        {"metric": "Orders completed", "value": 187},
        {"metric": "Revenue (USD)", "value": "$9,340"},
        {"metric": "Errors logged", "value": 3},
    ]
    return rows


def build_html(rows, week_end):
    week_start = (week_end - timedelta(days=6)).strftime("%b %d")
    week_end_str = week_end.strftime("%b %d, %Y")
    rows_html = "".join(
        f"<tr><td style='padding:8px;border:1px solid #ddd'>{r['metric']}</td>"
        f"<td style='padding:8px;border:1px solid #ddd;text-align:right'>{r['value']}</td></tr>"
        for r in rows
    )
    return f"""
    <html><body style='font-family:Arial,sans-serif;color:#333'>
    <h2>Weekly Summary: {week_start} – {week_end_str}</h2>
    <table style='border-collapse:collapse;width:100%;max-width:480px'>
      <thead><tr>
        <th style='padding:8px;border:1px solid #ddd;background:#f5f5f5;text-align:left'>Metric</th>
        <th style='padding:8px;border:1px solid #ddd;background:#f5f5f5;text-align:right'>Value</th>
      </tr></thead>
      <tbody>{rows_html}</tbody>
    </table>
    <p style='margin-top:24px;font-size:12px;color:#999'>Sent automatically by weekly_report.py</p>
    </body></html>
    """


def build_plain(rows, week_end):
    week_start = (week_end - timedelta(days=6)).strftime("%b %d")
    week_end_str = week_end.strftime("%b %d, %Y")
    lines = [f"Weekly Summary: {week_start} - {week_end_str}", ""]
    for r in rows:
        lines.append(f"  {r['metric']}: {r['value']}")
    lines.append("\nSent automatically by weekly_report.py")
    return "\n".join(lines)


def send_email(subject, html_body, plain_body):
    recipients = [r.strip() for r in REPORT_TO.split(",")]
    msg = MIMEMultipart("alternative")
    msg["Subject"] = subject
    msg["From"] = REPORT_FROM
    msg["To"] = ", ".join(recipients)
    msg.attach(MIMEText(plain_body, "plain"))
    msg.attach(MIMEText(html_body, "html"))

    with smtplib.SMTP(SMTP_HOST, SMTP_PORT) as server:
        server.ehlo()
        server.starttls()
        server.login(SMTP_USER, SMTP_PASS)
        server.sendmail(REPORT_FROM, recipients, msg.as_string())


def ping_heartbeat(success=True, exit_code=None):
    if not PING_URL:
        return
    try:
        if exit_code is not None:
            url = PING_URL.rstrip("/") + f"/exitcode/{exit_code}"
        elif not success:
            url = PING_URL.rstrip("/") + "/fail"
        else:
            url = PING_URL
        urllib.request.urlopen(url, timeout=10)
    except Exception as exc:
        print(f"WARNING: heartbeat ping failed: {exc}", file=sys.stderr)


def main():
    now = datetime.utcnow()
    try:
        rows = fetch_report_data()
        subject = f"Weekly Report — {now.strftime('%b %d, %Y')}"
        html_body = build_html(rows, now)
        plain_body = build_plain(rows, now)
        send_email(subject, html_body, plain_body)
        print(f"{now.isoformat()} — report sent to {REPORT_TO}")
        ping_heartbeat(success=True)
        sys.exit(0)
    except Exception as exc:
        print(f"ERROR: {exc}", file=sys.stderr)
        ping_heartbeat(success=False)
        sys.exit(1)


if __name__ == "__main__":
    main()

Monitor it

Create a Heartbeat monitor in CronJobPro (type: Heartbeat, period: 7 days, grace: 2 hours). You will receive a unique URL in the form https://cronjobpro.com/ping/YOUR_TOKEN. Set this as the CRONJOBPRO_PING_URL environment variable on your server. The script calls this URL on successful completion; if anything goes wrong — the server is down, cron never fires, the script throws an exception, or SMTP rejects the message — the ping will not arrive within the 7-day window plus grace period, and CronJobPro will alert you via whichever channel you configured (email, Slack, Discord, Teams, PagerDuty, Opsgenie, or webhook). On a script error the script explicitly calls /ping/YOUR_TOKEN/fail so you get an immediate failure alert rather than waiting for the full period to expire. This means you will always know whether the report actually reached recipients, not just whether cron thought it ran.

Start freeHow heartbeat monitoring works

Frequently asked questions

Can I send to multiple recipients?

Yes. Set REPORT_TO to a comma-separated list such as alice@example.com,bob@example.com. The send_email function splits on commas and passes all addresses to sendmail.

How do I swap in a real database query?

Replace the stub rows list inside fetch_report_data with your actual query. For PostgreSQL, install psycopg2-binary, create a connection using psycopg2.connect(os.environ['DB_URL']), run cursor.execute() with your SQL, and return cursor.fetchall() mapped to dicts. For SQLite, use the built-in sqlite3 module as shown in the comment.

What if cron fires but the script fails silently?

That is exactly what the heartbeat monitors. If the script exits with a non-zero code or raises an exception, ping_heartbeat(success=False) is called, which hits /ping/YOUR_TOKEN/fail and triggers an immediate alert. You also redirect stdout and stderr to /var/log/weekly_report.log in the cron entry, so you have a local audit trail.

How do I change the send time or day?

Edit the cron expression. For example, 0 7 * * 5 sends every Friday at 7 AM. Remember that cron uses the server's local timezone unless you prefix with CRON_TZ=UTC or configure your system timezone explicitly. When you change the schedule, update the CronJobPro heartbeat period to match the new interval.

More recipes

Automate Daily Sales and Metrics ReportsNightly PostgreSQL Backup with pg_dumpAutomate Nightly MySQL/MariaDB Backup with mysqldump
Automate Weekly Summary Email Reports | CronJobPro