VBA Extract Data From Outlook Email to Excel: Macro Guide

To extract data from Outlook email to Excel with VBA, you loop through a mail folder in the Outlook VBA editor, read the properties you want off each MailItem, pull any extra values out of the body with string functions, and write each message as a row into a worksheet. It works, it is free, and it is under your control. It also has a shelf life: the new Outlook for Windows does not run VBA at all, because it is a web application and cannot host the COM engine that macros depend on. Here is the working macro, followed by an honest account of where it stops.

How do I extract data from Outlook email to Excel using VBA?

Open Outlook (the classic desktop version), press Alt and F11 to open the VBA editor, insert a module, and paste the macro below. It opens Excel through late binding, so you do not need to add a reference to the Excel object library first. Run it and you get a worksheet with one row per matching message and one column per field, including a value pulled out of the body text.

Sub ExportOutlookDataToExcel()
    Dim ns As Object, fld As Object, itm As Object
    Dim xl As Object, ws As Object, r As Long

    Set ns = Application.GetNamespace("MAPI")
    Set fld = ns.GetDefaultFolder(6)   ' 6 = the Inbox

    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    Set ws = xl.Workbooks.Add.Sheets(1)

    ws.Cells(1, 1) = "Received"
    ws.Cells(1, 2) = "Sender"
    ws.Cells(1, 3) = "Subject"
    ws.Cells(1, 4) = "Order ID"
    r = 2

    For Each itm In fld.Items
        If TypeName(itm) = "MailItem" Then
            If InStr(1, itm.Subject, "Order confirmation", vbTextCompare) > 0 Then
                ws.Cells(r, 1) = itm.ReceivedTime
                ws.Cells(r, 2) = itm.SenderEmailAddress
                ws.Cells(r, 3) = itm.Subject
                ws.Cells(r, 4) = GetField(itm.Body, "Order ID:")
                r = r + 1
            End If
        End If
    Next itm

    ws.Columns("A:D").AutoFit
End Sub

Private Function GetField(body As String, label As String) As String
    Dim p As Long, e As Long
    p = InStr(1, body, label, vbTextCompare)
    If p = 0 Then Exit Function
    p = p + Len(label)
    e = InStr(p, body, vbCrLf)
    If e = 0 Then e = Len(body) + 1
    GetField = Trim(Mid(body, p, e - p))
End Function

Two things in that code are doing the real work. fld.Items is the collection of everything in the folder, and GetField is a crude parser: it finds a label in the body, takes everything from the end of that label to the end of the line, and trims it. Every field you want out of the message text needs its own call, and every one of them depends on the sender formatting that line exactly the way you expect.

How do I loop through an Outlook folder with VBA?

Use GetNamespace("MAPI") to reach the mail store, then GetDefaultFolder(6) for the Inbox. For a subfolder, walk down from the parent with fld.Folders("Orders"). Always test TypeName(itm) = "MailItem" inside the loop, because a folder can also hold meeting requests and reports, and reading .SenderEmailAddress off one of those throws an error.

On a large mailbox, iterating every item is slow. The usual fix is fld.Items.Restrict with a filter such as "[ReceivedTime] >= '01/07/2026'", which pushes the filtering down to the store instead of pulling tens of thousands of items into VBA. If your macro takes minutes to finish, that is almost always the reason.

Does VBA work with the new Outlook?

No. VBA and macros are not supported in the new Outlook for Windows, and neither are COM add-ins, because the new client is a web application running in a sandboxed environment that cannot host the Visual Basic for Applications engine. Microsoft has published no roadmap for adding it back and points people to Power Automate and web add-ins instead. If your organization is being moved to the new Outlook, any macro you write today has an end date attached to it.

That single fact is why this question gets asked so often now. A lot of teams have a macro that has quietly exported Outlook data to Excel for years, and the person who wrote it left. It still runs on classic Outlook, and it will stop the week IT flips the toggle.

Why does my Outlook VBA macro stop working?

Four causes cover almost every case, and none of them announce themselves clearly:

• Macro security. Outlook's trust center blocks unsigned macros by default, so the code sits there doing nothing after a policy update or a reinstall.
• A sender changed their template. GetField looks for "Order ID:" and the vendor now writes "Order #". The macro runs, reports no error, and writes blank cells.
• The client changed. A move to the new Outlook, or to a Mac, or to a Windows-on-ARM build, and the COM automation the macro relies on is simply gone.
• Bitness and references. Early binding against a specific Excel library version breaks on a machine with a different Office build. Late binding, as used above, avoids most of this.

The pattern is that failures are silent. A parser that reports "this field did not match on 14 of 300 messages" tells you something a macro writing empty cells never will.

How do I pull a specific value out of the email body with VBA?

With string functions or a regular expression. InStr and Mid, as in the macro above, are enough when the label is always identical and always followed by the value on the same line. For anything looser, people reach for CreateObject("VBScript.RegExp") and write a pattern per field. That works right up to the point where you have eight fields from five senders, at which point you are maintaining a small text-processing library inside a mail client. If you would rather have an AI coding assistant plan and maintain that code than own it yourself, that is a reasonable route; the honest alternative is to stop writing extraction code at all.

HTML email is where VBA gets genuinely awkward. itm.Body gives you a plain-text rendering that can mangle a table into run-together text, and itm.HTMLBody gives you raw HTML you then have to parse yourself. Order confirmations, shipping notices, and invoices are almost always HTML with a table in them, so this is not an edge case.

VBA, Power Query, Power Automate, or a parser

Credit where it is due: for a single export you will run once, on a machine that still has classic Outlook, the macro is the fastest thing on that list. You are done in twenty minutes and you pay nothing. The case for anything else starts when the job repeats, when more than a couple of fields are involved, or when someone other than you has to keep it alive.

Can I extract data from a shared mailbox with VBA?

Yes, if the shared mailbox is mapped in your Outlook profile. Reach it with ns.Folders("Orders Inbox").Folders("Inbox") using the display name of the store, or resolve it through ns.CreateRecipient and GetSharedDefaultFolder. The catch is that the macro runs on your desktop under your profile, so the export only happens when your machine is on and you press the button. Scheduling it means leaving a workstation running, which is the point where most teams start looking at a server-side option.

What is the alternative to VBA for extracting Outlook data to Excel?

Connect the mailbox to a parser instead of automating the mail client. MailParse signs in to the Microsoft 365 or Outlook account, reads the messages in the folder you choose, and writes the fields you name into columns: sender, received date, subject, and any value in the body such as an order ID, invoice total, or ticket reference. Each row in an HTML table comes out as its own row. Export an Excel workbook or a CSV, or take the same fields as JSON through the email parser API if the data is heading into another system.

There is no COM dependency, so nothing breaks when the desktop client changes, and no workstation has to be switched on for the run to happen. The general version of this workflow is covered in extracting data from an email body to Excel, and the Outlook specifics live on the Outlook email parser page. If you would rather stay in code, the Python approach to extracting email data is the cross-platform equivalent of the macro above, and importing email data into Excel with Power Query covers the native Excel route.

One boundary worth stating plainly, because VBA can technically save attachments to disk and people assume a parser does the same job: MailParse reads the email itself, meaning the headers, body text, and HTML tables, and records each attachment by filename, type, and size. It does not read what is inside a PDF or a spreadsheet attached to the message. If the numbers you need live inside an attached document rather than in the message text, that is a document extraction job and belongs in a different tool.

Where to start

If you have classic Outlook and a one-time export, run the macro. Change the folder, change the subject filter, add a GetField call for each value you need, and you will have your spreadsheet before lunch. If the same export has to happen every week, if the senders are humans rather than one system, or if new Outlook is on your IT roadmap, put the effort into a setup that does not depend on a mail client running on someone's desk. Map the fields once, confirm one real message comes back correctly, and let the export repeat itself.