The problem

On the telecom UAT project I was working on, defect notifications came in as emails from a test-management tool (SilkCentral) — dense, semi-structured messages with issue numbers, severity, build, and a free-text description buried in the body. Every one of them landed in an Outlook inbox and just sat there. If I wanted to know which defects were open, which product area they hit, or how a specific order had been retested, I had to open emails one at a time and read through boilerplate and confidentiality footers to find the actual content.

This is a problem that shows up anywhere a team routes structured business events through email because that’s the system everyone already has access to — defect trackers, incident alerts, order notifications, approval requests. The information is structured in principle, but as long as it’s trapped in an inbox it’s unsearchable, unreportable, and easy to lose track of. Any QA or ops team relying on a ticketing tool that notifies by email has the same gap.

The approach

The setup is a two-stage pipeline. An Outlook VBA macro (SaveEmailAndRunScript.bas) fires on matching incoming mail, using Outlook’s own rules engine to catch it as an event handler, and writes the subject and body straight out to a timestamped .txt file. That’s the whole job of the macro — get the email out of Outlook’s object model and onto disk before anything else touches it.

From there, a Python poller (defect_poller.py) watches that folder, picks up new .txt files every 60 seconds, parses out the subject and body, and appends each one as a row to an Excel workbook (emails.xlsx) — creating the workbook with headers on first run if it doesn’t exist yet. Processed files get moved into a separate folder so nothing gets picked up twice, and every run is timestamped into a log file. The result is one Excel sheet where every defect notification is a row I can filter, sort, or pivot on, instead of a pile of emails I have to open individually. I split it this way deliberately — I tried having the macro shell out to Python directly per email, but a standing poller turned out to be more reliable than firing a subprocess from VBA on every message, so I kept the direct-call code path in the macro but commented out and switched to the poller as the real mechanism.

What I learned

The interesting part wasn’t the Python, it was getting VBA to do the minimum possible before handing off. Outlook’s object model is awkward to work with from a script, so the macro’s only job is “extract text, write file, exit” — no parsing, no formatting logic, nothing that could throw an error inside Outlook’s event handler and leave a mail rule silently broken. Pushing every bit of real logic into Python where I could actually test and debug it made the fragile half of the pipeline (VBA inside Outlook) as small and boring as possible, and let me iterate freely on the parsing side without touching Outlook at all.

Where this could go

The pattern generalizes past defect emails: any inbox that receives structured or semi-structured notifications — support tickets, monitoring alerts, approval requests, order confirmations — can be tapped the same way, with a thin capture step at the mail client and a real parsing/storage step downstream. At team scale, the natural next step is swapping the Excel sheet for a shared database or a real ticket queue, and swapping the fixed-position “line 1 is the subject” parsing for something that handles varied email formats (multiple senders, different notification templates) instead of one specific one. That turns a personal workaround into a lightweight integration layer for teams whose source-of-truth systems only speak email.