Export Outlook Emails To Excel - VBA Code

Export Emails From Outlook to Excel with Date and Time

To Export Outlook email data to Excel & archive mails, we have 3 options here.

Menu option for Outlook to Excel
Outlook VBA: Export emails from Outlook to Excel VBA with date and time.
Archive Outlook Emails

Lets start to learn all possible methods, one by one.

1. How to Export Outlook Emails To Excel?

To extract data from Outlook to Excel:

Click File menu or Office Ribbon in Outlook.
Select "Import and Export".
Choose "Export to a file" & press Enter.
Select "Microsoft Excel 97-2003" & Enter.
Choose 'Outlook Folder to Export' & click next.
Type Output Excel File path to export Email data.
Map Outlook field headers with output Excel column headers [optional].
Click Finish.

All the email items from the chosen PST folder will be downloaded to Excel. It will have email fields like sender, email body, date and time etc., More Advanced Options are here... Download Outlook Mail Attachments to local Folder

2. Outlook VBA: Copy Email Fields to Excel with Date and Time

Here is the VBA code to extract emails from Outlook to Excel.

Downloaded Times

Before executing the code, make a note of these points.

Go to Tools -> References & enable “Microsoft Outlook nn.n Object Library. (nn.n is version of Object Library that varies depending on your Office Installation)
Change "Mail box Name" & "Folder Name" from where you need to extract the email data.
Outlook application should be running in your machine.

Note: If you are executing this code in Outlook VBA, then above reference is not required. Export Outlook emails to Excel with date and time

Option Explicit
'This Code is Downloaded from OfficeTricks.com
'Visit this site for more such Free Code
Sub VBA_Export_Outlook_Emails_To_Excel()
    'Add Tools->References->"Microsoft Outlook nn.n Object Library"
    'nn.n varies as per our Outlook Installation
    Dim Folder As Outlook.MAPIFolder
    Dim sFolders As Outlook.MAPIFolder
    Dim iRow As Integer, oRow As Integer
    Dim MailBoxName As String, Pst_Folder_Name  As String
    
    'Mailbox or PST Main Folder Name (As how it is displayed in your Outlook Session)
    MailBoxName = "MailBox Name"
 
    'Mailbox Folder or PST Folder Name (As how it is displayed in your Outlook Session)
    Pst_Folder_Name = "Folder Name" 'Sample "Inbox" or "Sent Items"
 
    'To directly a Folder at a high level
    'Set Folder = Outlook.Session.Folders(MailBoxName).Folders(Pst_Folder_Name)
    
    'To access a main folder or a subfolder (level-1)
    For Each Folder In Outlook.Session.Folders(MailBoxName).Folders
        If VBA.UCase(Folder.Name) = VBA.UCase(Pst_Folder_Name) Then GoTo Label_Folder_Found
        For Each sFolders In Folder.Folders
            If VBA.UCase(sFolders.Name) = VBA.UCase(Pst_Folder_Name) Then
                Set Folder = sFolders
                GoTo Label_Folder_Found
            End If
        Next sFolders
    Next Folder

Label_Folder_Found:
     If Folder.Name = "" Then
        MsgBox "Invalid Data in Input"
        GoTo End_Lbl1:
    End If
 
    'Read Through each Mail and export the details to Excel for Email Archival
    ThisWorkbook.Sheets(1).Activate
    Folder.Items.Sort "Received"
    
    'Insert Column Headers
    ThisWorkbook.Sheets(1).Cells(1, 1) = "Sender"
    ThisWorkbook.Sheets(1).Cells(1, 2) = "Subject"
    ThisWorkbook.Sheets(1).Cells(1, 3) = "Date"
    ThisWorkbook.Sheets(1).Cells(1, 4) = "Size"
    ThisWorkbook.Sheets(1).Cells(1, 5) = "EmailID"
    'ThisWorkbook.Sheets(1).Cells(1, 6) = "Body"
    
    'Export eMail Data from PST Folder to Excel with date and time
    oRow = 1
    For iRow = 1 To Folder.Items.Count
        'If condition to import mails received in last 60 days
        'To import all emails, comment or remove this IF condition
        If VBA.DateValue(VBA.Now) - VBA.DateValue(Folder.Items.Item(iRow).ReceivedTime) <= 60 Then
           oRow = oRow + 1
           ThisWorkbook.Sheets(1).Cells(oRow, 1).Select
           ThisWorkbook.Sheets(1).Cells(oRow, 1) = Folder.Items.Item(iRow).SenderName
           ThisWorkbook.Sheets(1).Cells(oRow, 2) = Folder.Items.Item(iRow).Subject
           ThisWorkbook.Sheets(1).Cells(oRow, 3) = Folder.Items.Item(iRow).ReceivedTime
           ThisWorkbook.Sheets(1).Cells(oRow, 4) = Folder.Items.Item(iRow).Size
           ThisWorkbook.Sheets(1).Cells(oRow, 5) = Folder.Items.Item(iRow).SenderEmailAddress
           'ThisWorkbook.Sheets(1).Cells(oRow, 6) = Folder.Items.Item(iRow).Body
        End If
    Next iRow
    MsgBox "Outlook Mails Extracted to Excel"
    Set Folder = Nothing
    Set sFolders = Nothing
    
End_Lbl1:
End Sub

This is how we can create an alternate to traditional archive Outlook emails built in option and maintain our own readable Email repository. To execute this code, press F5 and system will extract data from Outlook to Excel from the mentioned mailbox and folder. Note: The limitations of this Export Outlook to Excel code are:

Error:
- Double & Single quotes in below code might change to improper symbols while copying. Check it if you get any execution or compilation error.
- If there are any meeting invites or items other than a Email, then the above code will throw error. Remove the mail items that this code cannot read and re-execute the code.
Warning:
- This VBA does not extract complete MIME details of email, as how it is stored in Outlook archive folder. This method only stores the mail content in Excel just for future reference.
Information:
- Attachments from emails are not skipped. It will export only text content. For more information on downloading attachment to a system folder, you can get to know by reading this post “Outlook Attachments”.

3. How to Archive email in Outlook? - On Demand

To archive Outlook email to a PST folder, follow these steps.

In File Menu, click "Archive" (shortcut: Alt + f + r).
Choose Outlook mail folder to be archived.
Choose date for 'Archive items older than'.
Enter path for archive PST file & click Ok.

Outlook will start to move all the emails matching the date criteria to your archive pst file. Archiving old emails will improve the performance of Outlook considerably. Once your Outlook takes more time to load or search emails, then try to run a archival process on that specific email folder. There are 2 options available to archive Outlook emails - Outlook Auto Archive & On demand.we are going to see the automatic archival settings next.

4. How to Schedule Outlook Auto Archive?

To schedule the process of archiving Outlook emails at regular time interval, follow these steps.

In Tools Menu, click "Options" (shortcut: Alt + t + o).
Choose 'Others' tab & click "AutoArchive".
Enter Outlook Emails Autoarchive settings
1. Time Frequency.
2. Eligibility Date criteria for cleaning up older emails.
3. Output PST Folder path.
Click Ok when all settings are entered.

By using this method, you can make the Outlook to run archival process after office hours. Usually, your PC slows down if you have large number of older emails in your Inbox. So, try to schedule a archival after office hours.

Misc about Export Outlook Emails To Excel or Email Archiving

In this page, we learned multiple ways to read all the Emails from a specific Outlook Folder and write the details like Email Subject, Sender Name, Received Time, Body etc., to Excel. People do lot of search for the code in this article, but still some might get confused about what is the actual need to export Outlook emails to Excel. Transferring data from one MS Office application to another, but why? With Excel, after exporting they can apply options like Filter, Vlookup, Pivot Chart, and Pivot Table. More Tricks: Send Personalized Outlook Email from Excel to multiple Recipients Anyways Microsoft has provided option through Outlook VBA to read email content and export the mail body, contacts, addresses, Calendar and Tasks to Excel or Text file. Outlook also has option for VBA, but it is fun to integrate it with Excel VBA. Related: Read and Update Multiple Appointments to Outlook Calendar from Excel

Export Outlook Emails To Excel - VBA Code - 5,700+ Free Downloads