Guide

Automating Reporting WorkflowsDeep dive

Send an Excel Report with the Microsoft Graph API

Basic SMTP auth is off in most tenants. Send the workbook through Graph instead: a client-credentials token, an inline attachment, and an upload session for larger files.

Basic authentication for SMTP is switched off in most Microsoft 365 tenants, which retires the username-and-password approach that scheduled report scripts have relied on for years. The replacement is the Graph API: an application registration, a client credential, and an HTTP call that sends the message. This guide, part of Emailing Excel Reports with smtplib, covers the registration, the send, and the larger-attachment path.

Prerequisites

Bash

pip install msal requests

You also need an application registration in Entra ID with the Mail.Send application permission and admin consent granted, plus its tenant id, client id and a client secret. Store the secret in the environment rather than the script:

Bash

export GRAPH_TENANT_ID="..."
export GRAPH_CLIENT_ID="..."
export GRAPH_CLIENT_SECRET="..."
export GRAPH_SENDER="reports@example.com"

Get a token

The client-credentials flow needs no user interaction, which is what makes it suitable for a scheduled job. msal caches the token internally, so acquiring one per run is cheap.

Python

import os
import msal

def graph_token() -> str:
    app = msal.ConfidentialClientApplication(
        client_id=os.environ["GRAPH_CLIENT_ID"],
        client_credential=os.environ["GRAPH_CLIENT_SECRET"],
        authority=f"https://login.microsoftonline.com/{os.environ['GRAPH_TENANT_ID']}",
    )
    result = app.acquire_token_for_client(scopes=["https://graph.microsoft.com/.default"])
    if "access_token" not in result:
        raise RuntimeError(f"{result.get('error')}: {result.get('error_description')}")
    return result["access_token"]

Raising with the description rather than a generic message matters here, because the two most common failures — a secret that has expired and a permission that was never consented — produce very different descriptions and identical symptoms otherwise.

Send the report as an attachment

Graph takes the whole message as JSON, with the attachment base64-encoded inline.

Python

import base64
import pathlib
import requests

def send_report(path: str, to: list[str], subject: str, html_body: str) -> None:
    data = pathlib.Path(path).read_bytes()
    message = {
        "message": {
            "subject": subject,
            "body": {"contentType": "HTML", "content": html_body},
            "toRecipients": [{"emailAddress": {"address": address}} for address in to],
            "attachments": [{
                "@odata.type": "#microsoft.graph.fileAttachment",
                "name": pathlib.Path(path).name,
                "contentType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                "contentBytes": base64.b64encode(data).decode(),
            }],
        },
        "saveToSentItems": True,
    }
    sender = os.environ["GRAPH_SENDER"]
    response = requests.post(
        f"https://graph.microsoft.com/v1.0/users/{sender}/sendMail",
        headers={"Authorization": f"Bearer {graph_token()}",
                 "Content-Type": "application/json"},
        json=message,
        timeout=60,
    )
    response.raise_for_status()          # 202 Accepted on success

The MIME type is the one for .xlsx; getting it wrong does not stop delivery but does change how some clients present the attachment. saveToSentItems is worth leaving on — a copy in the sending mailbox is the cheapest possible audit trail for a report that goes out unattended.

Large attachments need an upload session

Anything much over 3 MB after encoding is rejected. The larger path is to create a draft, upload the file in chunks against it, and then send the draft.

Python

def send_large_report(path: str, to: list[str], subject: str, html_body: str) -> None:
    token = graph_token()
    headers = {"Authorization": f"Bearer {token}", "Content-Type": "application/json"}
    sender = os.environ["GRAPH_SENDER"]
    base = f"https://graph.microsoft.com/v1.0/users/{sender}/messages"

    draft = requests.post(base, headers=headers, timeout=60, json={
        "subject": subject,
        "body": {"contentType": "HTML", "content": html_body},
        "toRecipients": [{"emailAddress": {"address": a}} for a in to],
    })
    draft.raise_for_status()
    message_id = draft.json()["id"]

    file = pathlib.Path(path)
    size = file.stat().st_size
    session = requests.post(
        f"{base}/{message_id}/attachments/createUploadSession",
        headers=headers, timeout=60,
        json={"AttachmentItem": {"attachmentType": "file", "name": file.name, "size": size}},
    )
    session.raise_for_status()
    upload_url = session.json()["uploadUrl"]

    chunk = 4 * 1024 * 1024
    with file.open("rb") as handle:
        start = 0
        while start < size:
            block = handle.read(chunk)
            end = start + len(block) - 1
            put = requests.put(
                upload_url, data=block, timeout=300,
                headers={"Content-Length": str(len(block)),
                         "Content-Range": f"bytes {start}-{end}/{size}"},
            )
            put.raise_for_status()
            start = end + 1

    requests.post(f"{base}/{message_id}/send", headers=headers, timeout=60).raise_for_status()

The Content-Range header is the part that must be exactly right — byte offsets are inclusive at both ends, and an off-by-one produces an error whose message does not point at the arithmetic. Chunk sizes must be a multiple of 320 KiB; 4 MB satisfies that and is a reasonable balance.

Compose the body from the data

The most useful report email says enough that a recipient does not have to open the attachment to know whether they need to. Building that summary from the same frame that produced the workbook costs almost nothing.

Python

import pandas as pd

def summary_html(frame: pd.DataFrame) -> str:
    by_region = frame.groupby("Region", as_index=False)["Revenue"].sum()
    table = by_region.to_html(index=False, float_format=lambda v: f"{v:,.0f}", border=0)
    return (
        f"<p>Total revenue <strong>{frame['Revenue'].sum():,.0f}</strong> "
        f"across {len(frame):,} orders.</p>{table}"
    )

to_html produces a table most mail clients render acceptably; adding inline styles rather than a stylesheet is what makes it survive Outlook, which strips <style> blocks. The wider treatment is in Email an Excel Report with an HTML Summary Body.

Verifying delivery rather than assuming it

A 202 response tells you Microsoft accepted the message, which is a weaker guarantee than most scripts treat it as. Two cheap checks turn that into something you can act on. The first is to read back the sending mailbox's Sent Items and confirm a message with the expected subject arrived there.

Python

def confirm_sent(subject: str, within_minutes: int = 5) -> bool:
    from datetime import datetime, timedelta, timezone
    since = (datetime.now(timezone.utc) - timedelta(minutes=within_minutes)).isoformat()
    sender = os.environ["GRAPH_SENDER"]
    response = requests.get(
        f"https://graph.microsoft.com/v1.0/users/{sender}/mailFolders/sentitems/messages",
        headers={"Authorization": f"Bearer {graph_token()}"},
        params={"$filter": f"sentDateTime ge {since}", "$select": "subject,sentDateTime", "$top": 25},
        timeout=60,
    )
    response.raise_for_status()
    return any(item["subject"] == subject for item in response.json().get("value", []))

The second is simply to log the message id that the draft-and-send path returns, which gives support something to search on when a recipient says the report never arrived. Neither check proves delivery to the recipient's inbox — nothing available to the sender does — but both distinguish "we did not send it" from "they did not receive it", which is the question that actually gets asked.

Keeping the secret out of the repository

A client secret is a credential with the same weight as the mailbox password it replaces, and it expires — typically after six or twenty-four months — which makes it operationally different from a password nobody rotates. Reading it from the environment is the minimum; reading it from a managed secret store is better, and on Azure a managed identity removes the secret entirely.

Python

from azure.identity import DefaultAzureCredential

credential = DefaultAzureCredential()          # managed identity where available
token = credential.get_token("https://graph.microsoft.com/.default").token

DefaultAzureCredential tries a managed identity first and falls back through environment variables and developer sign-in, so the same code runs on a laptop and in a container without branching. That removes the expiry problem along with the storage one, which is the more valuable half — a secret that expires at 02:00 on a Sunday is a class of incident worth designing out. The general argument is in Keep Excel Report Settings in a Config File.

Common pitfalls

Symptom	Cause	Fix
`403 Forbidden` on sendMail	Application permission not consented	Grant admin consent for `Mail.Send` in the app registration
`401 Unauthorized` after months of working	Client secret expired	Rotate the secret; set a calendar reminder before expiry
202 returned, no mail arrives	Sender address is not a real mailbox	Use the mailbox's own address, not the app name
Attachment rejected with a size error	Over the inline limit	Use the upload-session path above
`ErrorInvalidUser`	Sending as a mailbox the app cannot access	Scope the app with an access policy, or use a shared mailbox it can send as
Body renders as plain text	`contentType` left as `Text`	Set `"contentType": "HTML"`

Restricting which mailboxes the app can use

An application with Mail.Send can, by default, send as any mailbox in the tenant — which is more authority than a reporting job needs and more than most security teams will accept. Exchange's application access policy narrows it to a named group.

Text

New-DistributionGroup -Name "Automation Senders" -Type Security
Add-DistributionGroupMember -Identity "Automation Senders" -Member reports@example.com
New-ApplicationAccessPolicy -AppId <client-id> `
    -PolicyScopeGroupId "Automation Senders" -AccessRight RestrictAccess `
    -Description "Reporting jobs may send only as the reports mailbox"

Running that once turns the permission from tenant-wide into one mailbox, and it makes the approval conversation considerably shorter. It is worth doing before the first production run rather than after the first security review.

Performance and scale

The token acquisition is the slow part of a single send — a network round trip against the identity service — and msal caches it, so a job sending twenty reports should reuse one client rather than constructing a new one each time. Sending itself is a single HTTPS request per message.

Python

app = msal.ConfidentialClientApplication(...)     # build once

for recipient, path in deliveries:
    token = app.acquire_token_for_client(scopes=[SCOPE])["access_token"]   # cached
    send_with(token, recipient, path)

Graph applies throttling per application and per mailbox, and it signals it with a 429 and a Retry-After header. Honouring that header rather than retrying immediately is the difference between a job that recovers and one that gets throttled harder — the backoff patterns in Retry a Failed Excel Report Job in Python apply directly.

Conclusion

Register an application with Mail.Send, acquire a token with the client-credentials flow, and post the message to /users/{sender}/sendMail with the workbook base64-encoded as a file attachment. Use an upload session once the file passes a few megabytes, narrow the app's reach with an application access policy, and honour Retry-After when Graph throttles. The setup is longer than SMTP; what you get back is a sender with no password to rotate.

Frequently asked questions

Do I need a user account to send through Graph? No. An application registration with the Mail.Send application permission sends as any mailbox in the tenant, which is the right model for a scheduled report. A delegated flow that signs a user in is only needed when the mail must come from that person's own session.

What is the attachment size limit? A simple attachment in the sendMail payload is limited to about 3 MB after base64 encoding. Larger files need an upload session against a draft message, which uploads the file in chunks before the message is sent.

Why does sendMail return 202 and nothing arrives? 202 Accepted means Microsoft queued it, not that it was delivered. Check the sending mailbox's Sent Items, and confirm the application permission was granted admin consent — a missing consent produces a 403 rather than silence, but a wrong mailbox address produces exactly this.

Is Graph better than SMTP for this? It is more work to set up and considerably more robust afterwards: no password to rotate, no basic-auth deprecation to worry about, and errors that name the problem. For a tenant that has disabled basic authentication it is often the only option left.

Up one level: Emailing Excel Reports with smtplib — the SMTP route and the message-construction basics.
Send an Excel Report Through Outlook from Python — the desktop alternative when a user's own mailbox must send it.
Email an Excel Report with an HTML Summary Body — building the body this send carries.
Send an Excel Report to Multiple Recipients in Python — recipient lists, copies and per-recipient files.
Retry a Failed Excel Report Job in Python — backoff for the throttling responses.