Automating Office Work with Python: Merge Excel Files, Build a Report and Send the Email

What the program does

Picture a tireless assistant doing four jobs in order.

First, open every Excel file in a folder. Second, stack them into one long table and add a "Line total" column. Third, add up by branch and by product, and write the result to a new report file with several sheets. Fourth, compose an email, attach that file and send it.

The part that scares non-programmers is sending the email. What if a test run really lands in the boss's inbox? I solve that with a "fake mailbox" running on your own machine, described below.

Setup

You need Python and the uv tool. If you do not have them, follow Getting Started with Python in 2026: Install, a Clean Setup and Your First Script. Then create a project and install three libraries:

mkdir report && cd report
uv init --bare --python 3.15
uv add pandas openpyxl aiosmtpd

The three libraries are pandas (works with tables of numbers), openpyxl (reads and writes .xlsx files) and aiosmtpd (builds the fake mailbox for testing). I ran everything with pandas 3.0.6, openpyxl 3.1.5 and aiosmtpd 1.4.6 on Python 3.15.0, checked on 21 October 2026.

For those who write code

Create sample Excel files

To test without touching real data, let the machine generate three branch files. Save as make_samples.py:

import random
from pathlib import Path
import pandas as pd

random.seed(7)
PRODUCTS = {"Wireless mouse": 190000, "Mechanical keyboard": 650000, "Headphones": 480000, "Fast charger": 220000}
Path("data").mkdir(exist_ok=True)

for name in ["Branch1", "Branch7", "ThuDuc"]:
    rows = []
    for day in pd.date_range("2026-10-01", periods=10):
        p = random.choice(list(PRODUCTS))
        rows.append({"Date": day.date(), "Product": p,
                     "Quantity": random.randint(1, 6), "Unit price": PRODUCTS[p]})
    pd.DataFrame(rows).to_excel(f"data/{name}.xlsx", index=False)
    print("Created", name)

Run uv run make_samples.py. The data folder gets three files with ten sales rows each.

Merge and build the report

Save as report.py:

from pathlib import Path
import pandas as pd
from openpyxl.styles import Font

parts = []
for file in sorted(Path("data").glob("*.xlsx")):
    df = pd.read_excel(file)
    df["Branch"] = file.stem
    parts.append(df)
everything = pd.concat(parts, ignore_index=True)
everything["Line total"] = everything["Quantity"] * everything["Unit price"]

by_branch = everything.groupby("Branch")["Line total"].sum().reset_index()
by_product = (everything.groupby("Product")[["Quantity", "Line total"]].sum()
              .sort_values("Line total", ascending=False).reset_index())

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as xl:
    by_branch.to_excel(xl, sheet_name="By branch", index=False)
    by_product.to_excel(xl, sheet_name="By product", index=False)
    everything.to_excel(xl, sheet_name="Details", index=False)
    for ws in xl.book.worksheets:
        for col in ws.columns:
            ws.column_dimensions[col[0].column_letter].width = 18
        for cell in ws[1]:
            cell.font = Font(bold=True)

print(by_branch.to_string(index=False))
print("Total:", f"{everything['Line total'].sum():,}", "VND")

Run uv run report.py. I got:

 Branch  Line total
Branch1     6490000
Branch7    10540000
 ThuDuc    13380000
Total: 30,410,000 VND

Open report.xlsx and you will find three sheets: totals by branch, totals by product and every detail row. For real files, just change the column names in the code to match your headers. All files must use the same column names.

Send the email with Python

Save as send_mail.py:

import os, smtplib
from email.message import EmailMessage
from pathlib import Path

HOST = os.environ.get("SMTP_HOST", "127.0.0.1")
PORT = int(os.environ.get("SMTP_PORT", "8025"))

msg = EmailMessage()
msg["From"] = "reports@company.example"
msg["To"] = "boss@company.example"
msg["Subject"] = "October revenue report"
msg.set_content("Hello,\n\nPlease find the revenue report attached.\n")

file = Path("report.xlsx")
msg.add_attachment(file.read_bytes(), maintype="application",
                   subtype="vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                   filename=file.name)

with smtplib.SMTP(HOST, PORT) as s:
    s.send_message(msg)
print("Sent to", HOST, PORT)

By default the program sends to 127.0.0.1 on port 8025, which is your own machine. Nothing goes anywhere unless you change the SMTP_HOST and SMTP_PORT environment variables yourself.

Test without fear of a wrong send

Open a second terminal and save fake_smtp.py. It starts a fake mail server on port 8025 that saves each incoming message as a file instead of passing it on:

import time
from pathlib import Path
from aiosmtpd.controller import Controller

Path("fake_inbox").mkdir(exist_ok=True)

class SaveMail:
    async def handle_DATA(self, server, session, envelope):
        name = Path("fake_inbox") / f"mail_{int(time.time() * 1000)}.eml"
        name.write_bytes(envelope.content)
        print("Received mail for", envelope.rcpt_tos, "->", name.name, flush=True)
        return "250 OK"

Controller(SaveMail(), hostname="127.0.0.1", port=8025).start()
print("Fake mail server listening on port 8025", flush=True)
time.sleep(3600)

Run uv run fake_smtp.py in that window, then uv run send_mail.py in the other. The first window prints:

Fake mail server listening on port 8025
Received mail for ['boss@company.example'] -> mail_1792560339115.eml

I opened the saved message. The subject was right, the attachment report.xlsx was 7,347 bytes, and opening it with openpyxl still showed all three sheets, with 6,490,000 in the first cell of the branch totals. So the whole path from Excel to email works.

Once that is fine, you can move to real sending. Real mail services usually require encryption and an account, so add s.starttls() and s.login(account, os.environ["SMTP_PASSWORD"]) before send_message, with the address and port from your provider. With Gmail or Outlook you often need an app password rather than your normal login password. I have not run that real-sending part against any service, so try it with your own address first. Keep the password in an environment variable, never in the code file.

Common problems

The first one I see often: the Excel file is open in Excel while the program runs, and Windows locks it so it cannot be read or written. Close the file and run again.

The second is column names. One branch types "Quantity", another types "Qty", and after merging you get empty columns. Agree on the names, or add a line that renames columns before merging.

The third is numbers stored as text. A cell looks like a number but Excel saved it as text, so pandas cannot add it up. Change the cell format to number, or force the type with pd.to_numeric.

Next steps

Two steps turn this into a habit. Let it run on schedule each month (Task Scheduler on Windows, cron on Mac and Linux). And do not send straight to the boss at first: send to yourself for a month, compare with the manual way, and only then switch the recipient.

If your work has its own tables and rules, I can build a tool that follows your exact process. Send me a message through the contact page.