Outlook VBA: Pull Email Data into Excel

To pull email data from Outlook into Excel, run a VBA macro in Excel that connects to classic Outlook, reads messages in the Inbox, and writes their received date, sender, subject, and body to a worksheet. You need desktop Excel and classic Outlook on Windows; the code below exports messages received in the last 30 days.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows and classic Outlook for Windows, installed on the same computer. New Outlook does not support the COM automation this macro uses.
An Excel workbook saved as an Excel Macro-Enabled Workbook (.xlsm), with a worksheet named MailData. Use a dedicated, empty sheet because the macro clears its contents before exporting.
Permission to run VBA and access the Outlook mailbox. In a work or school environment, your organization may restrict macros or programmatic access.
A backup copy of the workbook before you run the macro. VBA changes cannot be undone with Ctrl+Z.

Step-by-Step

Check which Outlook app you have. This example uses classic Outlook for Windows, which exposes Outlook through COM automation. New Outlook for Windows and Outlook for Mac do not support this VBA-to-Outlook connection. If you only have new Outlook, use an approved alternative such as Power Automate or an organization-approved Microsoft Graph solution rather than trying to enable this code.
Prepare a workbook and sheet. In Excel, create or open a workbook, add a worksheet, and name it MailData. Save a copy as a backup, then save the working file as an Excel Macro-Enabled Workbook (*.xlsm). The macro clears the sheet’s contents each time, so do not put unrelated information or formulas there.
Open the VBA editor and add a module. In Excel for Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, then paste the complete code below into the new module. You do not need to add an Outlook reference: the code uses late binding and declares Outlook objects as Object.

Option Explicit

Sub PullOutlookInboxToSheet()
    Const olFolderInbox As Long = 6
    Const olMail As Long = 43
    Const maxBodyLength As Long = 30000

    Dim outlookApp As Object
    Dim outlookNamespace As Object
    Dim inbox As Object
    Dim outlookItems As Object
    Dim mailItem As Object
    Dim ws As Worksheet
    Dim i As Long
    Dim nextRow As Long
    Dim bodyText As String

    On Error GoTo ErrHandler

    ' Use a dedicated worksheet; its contents are cleared below.
    Set ws = ThisWorkbook.Worksheets("MailData")

    ' Connect to classic Outlook and its default Inbox.
    Set outlookApp = CreateObject("Outlook.Application")
    Set outlookNamespace = outlookApp.GetNamespace("MAPI")
    Set inbox = outlookNamespace.GetDefaultFolder(olFolderInbox)
    Set outlookItems = inbox.Items
    outlookItems.Sort "[ReceivedTime]", True

    ws.Cells.ClearContents
    ws.Cells(1, 1).Value = "Received"
    ws.Cells(1, 2).Value = "Sender"
    ws.Cells(1, 3).Value = "Subject"
    ws.Cells(1, 4).Value = "Body"
    ws.Rows(1).Font.Bold = True
    nextRow = 2

    ' Export mail received within the last 30 days.
    For i = 1 To outlookItems.Count
        Set mailItem = outlookItems.Item(i)

        If mailItem.Class = olMail Then
            If mailItem.ReceivedTime >= Date - 30 Then
                bodyText = CStr(mailItem.Body)

                ' Leave room below Excel's 32,767-character cell limit.
                If Len(bodyText) > maxBodyLength Then
                    bodyText = Left$(bodyText, maxBodyLength)
                End If

                ws.Cells(nextRow, 1).Value = mailItem.ReceivedTime
                ws.Cells(nextRow, 2).Value = mailItem.SenderName
                ws.Cells(nextRow, 3).Value = mailItem.Subject
                ws.Cells(nextRow, 4).Value = bodyText
                nextRow = nextRow + 1
            End If
        End If
    Next i

    ws.Columns("A:C").AutoFit
    ws.Columns("D").ColumnWidth = 60
    ws.Columns("D").WrapText = True
    MsgBox CStr(nextRow - 2) & " messages exported to MailData.", vbInformation

CleanExit:
    Set mailItem = Nothing
    Set outlookItems = Nothing
    Set inbox = Nothing
    Set outlookNamespace = Nothing
    Set outlookApp = Nothing
    Exit Sub

ErrHandler:
    MsgBox "Export stopped: " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

Save and check macro settings. Save the workbook before running the code. If Excel blocks macros, review File > Options > Trust Center > Trust Center Settings > Macro Settings, following your organization’s policy. Do not turn off macro protections globally or enable code from an untrusted source just to run an export.
Run the macro. Return to Excel and press Alt+F8, select PullOutlookInboxToSheet, and choose Run. If Outlook asks for permission to access email data, respond only if you trust the workbook and understand what it will read. The macro reads messages from your default Inbox; it does not send, delete, or move messages.
Review the results. Open MailData and check the headers, message count, dates, and a few body cells. The export includes mail received in the last 30 days, including the date 30 days ago. It sorts the Inbox newest first before writing rows. Body text is cut off at 30,000 characters so it fits within Excel’s cell limit.
Adjust the scope carefully if needed. To export a different period, change Date - 30 to another number of days, such as Date - 7 for seven days. To include all dates, remove the date-checking If line and its matching End If, keeping the mail-item check in place. If you want a different folder, the macro must be changed to select that folder explicitly; the default Inbox setting does not include Sent Items, archive folders, or shared mailboxes.

This example runs from Excel rather than from Outlook’s own VBA editor. That makes the destination sheet straightforward to address and avoids a separate export step. It uses Outlook’s installed desktop object model, so it is not a method for reading mail from a web browser or from a mailbox without Outlook configured on the computer.

Common Mistakes

Using new Outlook or Outlook for Mac. The macro calls CreateObject("Outlook.Application"), which relies on classic Outlook’s Windows COM interface. It will not become compatible by changing a reference setting. Excel for Mac can run some VBA, but it cannot use this Outlook COM connection; there is no direct Mac shortcut or equivalent for this particular export.
Putting the code in the wrong place. The instructions are for a standard module in Excel’s VBA editor. If you paste the macro into an Outlook module, ThisWorkbook and the worksheet references will not point to the Excel file as intended.
Using a different sheet name. The workbook must contain a sheet named exactly MailData. If it does not, the macro stops with a worksheet-not-found error. Rename a sheet or update the name in the code before running it.
Exporting into a sheet with valuable data. ws.Cells.ClearContents clears the contents of the entire destination sheet. It does not clear its formatting, but it will remove existing values and formulas. Keep the destination dedicated to this export and retain a backup.
Expecting every item in the Inbox to be an email. Outlook folders can contain meeting requests and other item types. The Class check intentionally skips items that are not mail messages. A message in a subfolder is not included unless you select that folder in the code.
Assuming body text is unlimited or identical to what you see. Outlook’s Body property returns plain text, not the formatted HTML layout. Long bodies are shortened to stay under Excel’s per-cell character limit; attachments are not exported.

Tips

Try the macro on a small, non-sensitive mailbox or a test folder first. Inspect the output before using it for a recurring report, and keep the workbook somewhere with access controls appropriate for the email data.
Use the received date and sender columns to filter or sort the exported records in Excel. Consider converting the results to a table after export if you want filter buttons, but remember that the next run clears the sheet’s contents.
If you need only metadata, remove the body assignment and body-column output. This reduces the amount of personal or confidential content copied into Excel and can make the workbook smaller.
If you need sender email addresses, attachments, categories, or a shared mailbox, treat those as separate additions. Sender addresses can be more complicated for Exchange accounts, and attachment handling can create files on disk; do not assume that the sender display name or this basic export covers those needs.
On Windows, Alt+F11 opens the VBA editor and Alt+F8 opens the macro list. In Excel for Mac, Option+F11 (sometimes Fn+Option+F11, depending on the keyboard) opens the editor, but this Outlook COM macro itself still cannot run on Mac.

Troubleshooting

“ActiveX component can’t create object” or an Outlook automation error: Confirm that classic Outlook is installed and configured, and that it is not only new Outlook. Check that the correct Outlook profile can open normally. On a managed device, ask your IT administrator whether COM automation is blocked rather than changing security settings yourself.

“Subscript out of range” when the macro starts: Excel cannot find a worksheet named MailData in the workbook containing the macro. Check the tab name for spelling and spaces, or edit the worksheet name in the code. Also make sure you are running the macro from the workbook where you pasted it.

The macro runs but exports zero messages: Check that the messages are in the default Inbox and that their received dates fall within the last 30 days. The date rule uses the computer’s current date. A different account, an archive, a subfolder, or a shared mailbox may not be the default Inbox.

Excel reports that macros are disabled: Confirm that the workbook is saved as .xlsm, then follow your organization’s approved macro policy. If the file came from the internet, Windows or Office may mark it as untrusted. Do not enable all macros or bypass a company policy; ask your administrator for an approved route.

Some text appears missing or the output looks cramped: The macro intentionally truncates bodies longer than 30,000 characters, and plain-text output does not preserve Outlook formatting. Widen the body column or use Excel’s wrap setting if needed. If you change the truncation limit, keep it below Excel’s 32,767-character cell maximum.

An Outlook permission prompt appears: This can depend on Outlook, antivirus status, and administrator policy. Only approve access when the macro’s source and purpose are trusted. Do not follow instructions to disable Outlook’s security guard to make an unknown macro work.

Frequently Asked Questions

Can I run this macro inside Outlook instead of Excel?
It is possible to write Outlook-hosted VBA that creates or opens an Excel workbook, but this example is deliberately Excel-hosted: Excel is already the destination, and its worksheet is easy to address. The same COM and classic-Outlook limitations still apply.

Does this export the whole mailbox?
No. It reads only the default Inbox and includes mail received in the last 30 days. It does not search other folders, subfolders, archives, or shared mailboxes, and it skips non-mail items.

Will the macro delete or change my emails?
No. The supplied code reads Inbox items and writes values to the Excel sheet. It does not delete, move, mark, or send messages. It does clear the contents of the destination worksheet, which is why that sheet should be dedicated and backed up.

Can I export attachments too?
Not with this code. Attachments require additional VBA that saves files to a chosen location and handles duplicate names, file types, and privacy risks. Add that only when you have a clear destination and permission to store the files.

Can I use this with Outlook on the web or new Outlook?
No. The code depends on classic Outlook for Windows and its COM object model. For web or new Outlook workflows, use an approved Power Automate flow or another supported service instead.

Why does the macro ask for permission or fail on a work computer?
Outlook’s programmatic-access protections and organizational policies can restrict automated reading. Follow the prompt only if you trust the code, and contact IT if access is blocked. Avoid lowering security settings as a workaround.

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.