Ampwise AI
    Back to blog

    How to Auto Extract Tables From Emails Into Excel

    You can auto extract a table from an email by reading the message’s HTML body, splitting it into rows and cells, and writing those rows to Excel. Power Query, an Outlook VBA macro or a low-code flow can do this when the table layout never changes. When senders vary, attach PDFs or scans, or the data must reach an ERP, you need extraction that understands the content instead of the markup. This guide shows each route, where each one breaks, and how to keep bad rows out of your data.

    Ampwise
    ampwise.ai
    Move Email Data Beyond Excel
    Ampwise processes orders, inquiries, and invoices from email, including varied document formats, and integrates them with business systems.
    Explore Ampwise

    TL;DR

    • Tables survive in an email’s HTML body, not reliably in plain text, so always work from the HTML version.
    • Power Query, VBA and XPath-based flows all work for one sender with a fixed layout. They get fragile when columns, logos or senders change.
    • Tables in attachments need a different route: expand the attachments, not the body.
    • Treat extracted values as a proposal: validate types, totals and duplicates before anything is saved.
    • For orders, invoices and PO confirmations from many senders, template-free AI extraction straight to your ERP removes most of the maintenance.

    Table of Contents

    Why email tables are harder to extract than they look

    A table in an email looks like a grid. Underneath, it is HTML, and that HTML was usually generated by another system or pasted from a spreadsheet. Outlook exposes both a plain-text and an HTML body, and table structure is generally kept only in the HTML. That is the first rule: if your automation reads the plain-text body, you will lose the columns.

    The second problem is consistency. The same author warns that Outlook HTML can contain missing tables, missing cells, merged cells or malformed elements, so any code that touches a table or a cell should have error handling around it. A daily report from one internal system is a very different job from purchase orders arriving from forty customers.

    So the first decision is not which tool to use. It is how predictable your emails are.

    Situation Typical fit
    One sender, same layout every time, table in the body Power Query, VBA or a flow with XPath
    One sender, table sits in an Excel or CSV attachment Attachment import in Power Query
    Many senders, varied layouts Template-free AI extraction
    PDFs, scans, free-text emails with embedded tables Template-free AI extraction
    Data must land in an ERP, not a spreadsheet Extraction with validation and ERP posting

    Method 1: Power Query with the Exchange Online connector

    If your mailbox is on Exchange Online and you want a refreshable Excel sheet, Power Query is the least code-heavy path. The steps, as described in this walkthrough:

    1. Connect to the mailbox with the Exchange Online connector and pick the folder that holds the emails.
    2. Expand the Body column and select HtmlBody.
    3. Parse the HTML with a custom function or by splitting on tag delimiters.
    4. Expand the parsed records into columns.
    5. Close and load the result into an Excel table.

    The strength is that a refresh pulls in new emails without any macro. The weakness is step 3. Parsing HTML with delimiters assumes the markup keeps its shape. When the sender changes their template, the query breaks quietly or returns shifted columns.

    A practical tip: filter the folder first, by sender and subject, so the query only parses messages that actually contain the table.

    Method 2: An Outlook VBA macro

    VBA is the classic route for people who live in Outlook and Excel. There are two common patterns.

    HTML object model. One approach loads the HTML body into a document object, finds table elements by tag name, loops through rows, and reads each cell before writing values into a worksheet. It follows the real structure of the table, so it is more robust than text splitting.

    Word editor. Another macro uses Outlook’s Word editor to reach the message’s tables collection, copies the rows after the header from the last table, and pastes them into a workbook. Its author notes that blank cells in the source table can push row contents into the wrong cell, which is exactly the kind of silent error you want to catch.

    VBA suits a single person or a small team. It is harder to share, runs only when Outlook is open on that machine, and someone has to own the code when they leave.

    Method 3: A low-code flow with XPath

    Cloud flow tools can trigger when an email arrives, which makes the process truly automatic. A typical build looks like this:

    1. Trigger on a new email in a given folder and filter by sender or subject.
    2. Isolate the HTML table, convert it to XML, and use XPath to pull the text of every cell.
    3. Chunk the flat list of cells by the number of columns, so each chunk is one row.
    4. Skip the header row.
    5. Write each row to Excel, updating by a unique column rather than blindly appending.

    The XML step has a trap. The XML parser is strict, and real-world email HTML often fails on unclosed table or cell tags, line breaks without a closing slash, and non-breaking-space entities. The fix is to clean those known problems with replace before parsing, or to fall back to splitting the text yourself.

    When the email has more than one table

    Many messages carry a layout table, a signature table and the data table. A reliable pattern splits the body on the opening table tag, filters the pieces for a value unique to the table you want, then rebuilds that table so it can be queried. Pick a landmark that belongs to the data, such as a column header, not to the styling.

    Know the limits before you build

    One practitioner who parsed tables this way called the landmark approach flaky: a sender changing something as trivial as a logo can break it, fixed-column loops fail when the column count varies, and a serial flow took about 12 minutes for 93 rows in his example. That is a fair summary of the whole category. These methods are excellent for stable, low-volume, single-sender cases, and they need upkeep as soon as reality gets messier.

    Method 4: A script in Python

    If you have a developer, a script gives you the most control. A common pattern uses BeautifulSoup to find the tables, loop through rows and cells, skip empty rows, treat the first record as headers, and build a pandas DataFrame. From there, writing to Excel or a database is one line.

    The trade-off is the same as with VBA, only moved to a server: someone must maintain the script, handle authentication to the mailbox, and adjust it for each new sender format.

    Tables in attachments and scans

    Not every table is in the body. Customers send Excel price lists, CSV exports, PDF order forms and photographed delivery notes. For spreadsheet and CSV attachments in Power Query, you expand the Attachments column instead of the Body column. That works when the attached file has the same structure every time.

    PDFs and scans are another matter. There is no HTML to parse, so you need text recognition plus something that understands which number is a quantity and which is a part number. We cover those routes in more detail in our guides to Excel attachments going into an ERP, Excel order import automation and PDF parsing from email to ERP.

    Validate before you save anything

    Getting rows out of an email is the easy half. The expensive half is making sure wrong or duplicate rows never reach your records. Whatever method you pick, build in these checks:

    1. Keep the source. Store the message ID and the date so every row can be traced back to its email.
    2. Map to a schema. Decide the fields, types and allowed values up front.
    3. Treat the output as a proposal. A safe data-entry workflow checks required fields, types, dates, ranges, totals and references, and routes exceptions to a person for approval.
    4. Use a stable key. Idempotent pipelines rely on primary keys or documented immutable composite keys, and a full-row hash only prevents exact duplicates, not updates. The same source advises confirming which identifiers are genuinely fixed and unique rather than assuming an email address or display name is.
    5. Update, do not append. The flow example above updates Excel by a unique column, so a re-run changes a row instead of duplicating it.
    6. Read back. After writing, compare the destination with what you intended to write.

    A quick example: a supplier emails a table of 12 order lines with a stated total. Your check sums line quantity times unit price. If the sum differs from the stated total, the email goes to a review queue instead of the ledger. That one rule catches shifted columns, which are the most common failure of fixed-position parsing.

    Privacy and permissions

    Email is sensitive by default. Give any automation access only to the folder it needs, log what it does, and keep a human in the loop for exceptions. The research on Gmail’s large-scale extraction system is a useful benchmark: it describes preventing engineers from viewing individual emails and evaluating with donated, opted-in or anonymized data. You do not need that scale, but the principle holds for your own workflow: the people maintaining the automation should not need to read the mailbox to fix it.

    Where rules-based extraction stops working

    Every method above shares one assumption: the table looks the same next time. In B2B that assumption fails quickly.

    • Each customer formats purchase orders differently.
    • The table sits inside a PDF, or is a screenshot pasted into the message.
    • Some orders are free text: “Please send 40 of the 12 mm bolts and 15 of the washers, same as last time.”
    • Column names change: “Qty,” “Quantity,” “Menge,” “Pcs.”
    • The result has to match your item master and post into an ERP, not just fill a sheet.

    At that point you are not parsing a table, you are reading business communication. That is the problem we built Ampwise for, and we go deeper on the hybrid approach in our piece on unstructured email extraction and on how the email-to-ERP workflow works.

    Our perspective

    Most teams start with a macro or a flow, and that is a sensible start. One sender, one table, one afternoon of setup, and a weekly chore disappears. We have seen how good that feels.

    The trouble comes in month three. A customer changes its template, a second customer sends the same data as a PDF, a third types the order into the email itself. The automation now needs a person to babysit it, and the time you saved goes into maintenance.

    That is why we do not think of this as a table-parsing problem. The table is just one place where the data hides. What matters is that the right quantity, part number and price end up in your ERP, verified, without someone retyping them.

    Ampwise works directly in Outlook and Gmail and is designed to handle emails, PDFs, Excel files and scanned documents without templates or changes to how your customers send things. You review verified data and approve it with one click, so your team stays in control while the typing disappears. For companies with heavy email workflows, that is where the 90% reduction in manual data entry comes from.

    If you are choosing between maintaining ten fragile flows and giving your team one reliable inbox-to-ERP path, we would pick the second every time.

    Evert

    See it on your own emails

    If your tables arrive from many senders, in many formats, and end up in an ERP, Ampwise reads the emails and attachments in Outlook or Gmail, extracts and validates the data, and sends it to your ERP after your one-click approval. There are no templates to build and no staff retraining. You can see how it works on the Ampwise site or read the product overview.

    Ampwise

    Manufacturers who want to keep a person in charge of what gets posted will find the details in our guide to automating email orders with human review before posting.

    How to choose your approach

    Use this short decision path:

    1. Is it one sender with a fixed HTML table? Start with Power Query, a macro or a flow. Add the validation checks above.
    2. Does the table live in an Excel or CSV attachment with a fixed structure? Import the attachment, and key your updates on a unique column.
    3. Do formats vary, or do PDFs and scans appear? Move to template-free extraction before the maintenance burden grows.
    4. Does the data need to land in an ERP? Choose a tool that validates against your master data and posts to the ERP, not one that stops at a spreadsheet.
    5. Do column names or field mappings keep causing errors? Look at how mapping is handled; our article on email data mapping patterns covers four that cut errors.

    Whatever you choose, run it in parallel with your manual process for a couple of weeks and compare row counts and totals before you switch over.

    FAQ

    Can I extract a table from an email if it is only in plain text?

    Usually not reliably. Outlook offers both plain-text and HTML versions of a message, and table structure is generally preserved only in the HTML. If a sender only provides plain text, you have to split on delimiters such as tabs or commas, which breaks easily when values contain them.

    What is the easiest way to pull an email table into Excel automatically?

    For a single sender with a stable layout, Power Query’s mailbox connector is the lowest-code option: select the folder, expand the HTML body, parse it, and load it into a table. Refreshing the query brings in new messages. For many senders or non-HTML formats, it will need constant adjustment.

    How do I stop duplicate rows when the automation runs twice?

    Write with a stable key instead of appending. Choose a field that is truly unique, such as an order number combined with a line number, and update the existing row if the key already exists. Keep the message ID with each row so you can trace and reverse any entry.

    What about tables inside PDF attachments or scanned images?

    There is no HTML to read, so the file must be run through text recognition and then interpreted for meaning. Template-free tools do this without per-customer setup. See our guide on Gmail PDF invoice processing or Outlook PDF invoice processing for AP for how that looks in practice.

    Is it safe to give an automation access to my mailbox?

    Limit it to the folders it needs, log its actions, and route exceptions to a person. Privacy-focused research on large-scale email extraction describes designs where engineers cannot view individual emails, a good principle to apply to your own setup.

    Sources

    📅 Book an Ampwise AI demoBack to blog