Outlook VBA: How to Pull and Export Mail Data

To pull mail data with VBA, use classic Outlook for Windows’ object model to select a folder, loop through its items, and read properties such as the subject and received time. You can then write those values to Excel or another destination; the example below exports Inbox messages to a new Excel workbook.

What pulling data from Outlook means

Outlook stores email, appointments, contacts, and other items in folders. VBA can access these through Outlook’s object model: a set of objects and properties that represent the application, the signed-in user’s folders, and the items inside them. A macro can read selected fields, apply filters, and send the results to another application or file.

“Pull data” does not mean that Outlook automatically makes a clean database table. A message has many properties, and not every item in a folder is an email. A folder might contain meeting requests, delivery reports, or other Outlook item types. Decide which item types and fields matter before writing a macro. For a basic message list, useful fields include the received date, sender’s display name, subject, and entry ID.

Requirements and Outlook version limits

The VBA examples here are for classic Outlook for Microsoft 365 on Windows. VBA and the Outlook COM object model are not supported in the new Outlook for Windows or Outlook for Mac. If you have the new Outlook, switch to classic Outlook if it is available in your organization, or choose an alternative such as Power Automate or Microsoft Graph.

Excel must be installed on the Windows computer to use the example that creates an Excel workbook through COM automation. The code uses Outlook’s built-in VBA environment and late-bound Excel objects, so it does not require you to add an Excel reference in the VBA editor. Organization policies may restrict macros or automation, and an administrator may need to approve the approach.

Open the Outlook VBA editor

In classic Outlook for Windows, show the Developer tab if it is not already visible: select File > Options > Customize Ribbon, check Developer in the main tabs list, and select OK. Then choose Developer > Visual Basic to open the VBA editor. You can also use Alt+F11 on Windows. The editor and VBA macros are not available in Outlook on the web.

In the editor, choose Insert > Module to create a standard module, then paste a macro into it. To run it, put the cursor inside the procedure and press F5, or return to Outlook and use Developer > Macros. Outlook’s macro settings are under File > Options > Trust Center > Trust Center Settings > Macro Settings. Do not enable all macros indiscriminately; use a trusted, approved setup and follow your organization’s policy.

Export Inbox messages to Excel

This example reads MailItem objects in the default Inbox and writes four columns to a new Excel workbook. It sorts the Inbox items by received time, newest first. It leaves Excel open so you can review the results and save the workbook where you want it.

Sub ExportInboxToExcel()
    ' Read Inbox mail and create a new Excel workbook.
    Dim ns As Outlook.NameSpace
    Dim inbox As Outlook.Folder
    Dim items As Outlook.Items
    Dim item As Object
    Dim mail As Outlook.MailItem
    Dim xlApp As Object
    Dim xlBook As Object
    Dim xlSheet As Object
    Dim rowNumber As Long

    Set ns = Application.GetNamespace("MAPI")
    Set inbox = ns.GetDefaultFolder(olFolderInbox)
    Set items = inbox.Items
    items.Sort "[ReceivedTime]", True

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)

    xlSheet.Cells(1, 1).Value = "Received"
    xlSheet.Cells(1, 2).Value = "Sender"
    xlSheet.Cells(1, 3).Value = "Subject"
    xlSheet.Cells(1, 4).Value = "Entry ID"
    rowNumber = 2

    For Each item In items
        If TypeOf item Is Outlook.MailItem Then
            Set mail = item
            xlSheet.Cells(rowNumber, 1).Value = mail.ReceivedTime
            xlSheet.Cells(rowNumber, 2).Value = mail.SenderName
            xlSheet.Cells(rowNumber, 3).Value = mail.Subject
            xlSheet.Cells(rowNumber, 4).Value = mail.EntryID
            rowNumber = rowNumber + 1
        End If
    Next item

    xlSheet.Columns("A:D").AutoFit
    xlApp.Visible = True
End Sub

The macro skips non-mail items, but it still examines every item in the Inbox. A large folder can take time to process. It creates a new workbook rather than changing an existing one, and it does not save automatically. Review the output, then save it from Excel. Before running any macro that writes to a workbook, save a separate copy of the workbook first. VBA changes are not reversed with Ctrl+Z; the same caution applies to macros that move, delete, or edit Outlook items.

Choose folders, fields, and filters

GetDefaultFolder(olFolderInbox) selects the signed-in account’s default Inbox. Other built-in folder constants include olFolderSentMail, olFolderCalendar, and olFolderContacts. For a particular subfolder, first get its parent folder, then use that folder’s Folders collection to locate the child. Folder names and layouts vary, especially when accounts have been configured differently, so check the folder exists before relying on it.

Change the columns in the example to match your task. Mail properties include Subject, Body, ReceivedTime, SentOn, SenderName, and Categories. For contacts, properties are different, such as FullName and Email1Address. Calendar items have properties such as Start, End, and Location. Use the appropriate item type and check its properties in Microsoft’s Outlook object model documentation when you need a field not shown here.

To extract only a subset, filter before processing rather than exporting everything and deleting rows afterward. Outlook’s Items.Restrict method can return items matching a filter, for example a date range or a subject condition. Filter syntax depends on the property and item type; date strings can also be sensitive to regional date formats. Test a filter on a small folder and confirm which boundary dates it includes before trusting a large export.

Understand message data and performance

Outlook’s Items collection can contain mixed item types, which is why the example checks TypeOf item Is Outlook.MailItem before reading mail-specific properties. A message’s EntryID is useful as an identifier within Outlook, but it should not be treated as a permanent, portable key: moving an item to another store or folder can affect its identifier. A subject is not unique, either, so avoid using it alone to identify a message.

Large folders and full message bodies can make a macro slow and create bulky output. Start with the smallest useful set of fields, use a restrictive filter, and export in batches if needed. If you need attachments, decide whether you need their names, file paths, or file contents; those are different tasks with different privacy and storage implications. For a repeatable business process, record the selected folder, date range, and fields so another person can understand what the export represents.

Security and privacy considerations

Email can contain personal, confidential, or regulated information. Export only what you need, store the resulting workbook in an approved location, and apply appropriate access controls. A macro that reads messages is different from one that sends, deletes, or moves them, but both should be reviewed before use. Do not run VBA received from an unknown source, and do not weaken Trust Center settings just to bypass a warning.

Outlook may display a programmatic access warning in some configurations, and administrators can control how such access is handled. Do not try to evade a warning or organizational security policy. If the macro is blocked, contact your IT administrator and explain what data it needs and where it will go. Keep the export limited, avoid logging message bodies unless necessary, and remove temporary files according to your organization’s retention rules.

When VBA is not the right tool

VBA is practical for a one-off export or a task run interactively by someone using classic Outlook on Windows. It is less suitable for unattended processing, cross-platform use, or workflows that must run reliably while no user is signed in. Excel automation through COM also depends on desktop Office applications and is not a server-side integration method.

Power Automate: Useful for supported cloud workflows, such as reacting to incoming mail and recording selected details. Connector capabilities and organization policies determine what is possible.
Microsoft Graph: A developer API option for applications that need to work with Microsoft 365 data. It involves app registration, permissions, and authentication rather than Outlook VBA.
Outlook export or manual copy: For occasional, simple transfers, built-in export features or copying selected fields may be enough. They offer less customization than a macro but avoid maintaining VBA code.

Choose based on where the process must run, whether it needs to be scheduled, the amount of data, and the permissions available. If a macro only needs to create a report from a few dozen messages, VBA may be simpler. If it must run unattended across accounts or integrate with another service, discuss a supported API or workflow with your administrator.

Frequently Asked Questions

Can I use VBA in the new Outlook for Windows?
No. The new Outlook does not support Outlook VBA macros. The code in this guide requires classic Outlook for Windows. If classic Outlook is unavailable, consider Power Automate or Microsoft Graph, subject to your organization’s configuration.

Can Outlook VBA pull data from Sent Items instead of Inbox?
Yes. Use GetDefaultFolder(olFolderSentMail) in place of GetDefaultFolder(olFolderInbox). The rest of the loop can stay similar, but sent messages may have different useful fields, such as SentOn, and you should still check that each item is a MailItem.

Can VBA export the message body or attachments?
Yes. A MailItem exposes its body through properties such as Body and HTMLBody, and attachments through its Attachments collection. Bodies can contain substantial or sensitive content, while attachments require decisions about file names, save locations, and overwrite behavior. Add only the fields you actually need.

Does Outlook VBA work on a Mac?
No. Outlook for Mac does not provide the Outlook VBA environment and COM object model used by this example. A cross-platform or cloud workflow generally requires a different tool, such as an appropriate Power Automate flow or a Microsoft Graph application.

Why does my export miss subfolders?
The example selects the default Inbox and processes only items directly in that folder. It does not recursively inspect child folders. To include subfolders, explicitly loop through the folder’s Folders collection and process each one, taking care to avoid duplicate exports if your folder structure or rules cause overlapping data.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.