How to export emails from Outlook to Excel 

Have you ever wondered how it's possible to import the list all the folders and emails from Microsoft Outlook into an Excel spreadsheet?

Listing all folders and emails in Outlook can provide several benefits:

1. Organize and Analyze: By exporting your Outlook folder structure and email details to Excel, you can better organize your emails, track your communication, and analyze your inbox's content.

2. Backup and Recovery: Creating a list of folders and emails is a great way to back up critical information in case of data loss or for legal compliance.

3. Efficient Reporting: If you need to generate reports or perform data analysis on your email data, having it in Excel format makes the process more accessible.

 In this post, we'll break down a VBA (Visual Basic for Applications) code that achieves just that. We'll go through the code step by step to understand how it works and what each part does. 

The key steps are the following 

1. Create the outlook application and get access to the email account

2. Assign the Outlook Application and folders to variables. Create the spreadsheet where the emails' information will be stored

3. Loop through the folders to retrieve email information

1. Create the outlook application and get access to the email account

The first step is to give the routine a name and declare variables to store various objects and values that will be used in the code:

• Sub ListOutlookFoldersAndEmails() is the name of the routine represented by all the code that will follow

•   olApp, olNamespace, and olFolder are the variables that will store references to Outlook objects.

•   ws will store a reference to an Excel worksheet.

•   i is an integer variable used as a row counter.

Sub ListOutlookFoldersAndEmails()

Dim olApp As Object

Dim olNamespace As Object

Dim olFolder As Object

Dim ws As Worksheet

Dim i As Integer

2. Assign the Outlook Application and the folders to variables. Create the spreadsheet where the emails' information will be stored

We now need to assign the email account and all the folders to variables that will be used by the code. The excel spreadsheet where all the emails information will be stored is also created

• Set olApp = CreateObject("Outlook.Application")  defines the object created above as an Outlook Application to which the user has access

• Set olNamespace = olApp.GetNamespace("MAPI") enables to access the part of the Outlook Application containing Accounts, Folders and Emails 

•  Set ws = ThisWorkbook.Sheets.Add creates the sheet where the email information will be stored

• ws.cells(1,1).value = " Folder Name" writes in cell A1 "Folder Name"

• ws.cells(1,2).value = "Email Subject" writes in cells B1 "Email Subject"

Set olApp = CreateObject("Outlook.Application") 

Set olNamespace = olApp.GetNamespace("MAPI")  

Set ws = ThisWorkbook.Sheets.Add  

ws.Cells(1, 1).Value = "Folder Name"

 ws.Cells(1, 2).Value = "Email Subject" 

3. Loop through the folders to retrieve email information 

The code runs through each folder to analyse its content 

• i = 2  The variable will indicate the line number where the email information will be reported. Starts at 2 as on the first line of the spreadsheet there are reported the column headers

• For Each olFolder In olNamespace.Folders runs through each folder in the Outlook Application to anakyse its content

• ListFolder olFolder, ws, i this line calls a different sub that is analysed below. The purpose of this sub is to list the emails in the current folder

• Next olFolder once the information related to all the emails in the current folder has been copied in the sheet the code moves to the nexct folder

• Set olNamespace = Nothing  once the code has scanned all the folders the  object is cancelled 

• Set olApp = Nothing also the Outlook object is cancelled 

• End Sub  This is the end of the code

i = 2

For Each olFolder In olNamespace.Folders

ListFolder olFolder, ws, i

Next olFolder

Set olNamespace = Nothing

Set olApp = Nothing

End Sub

The code line ListFolder olFolder, ws, i  calls the below procedure 

• Sub ListFolder(ByVal olFolder As Object, ByVal ws As Worksheet, ByRef i As Integer) which accepts 3 elements

1. olFolder which is an outlook folder obekect

2. ws which is an excel workksheet object 

3. i  an integer which is passed to the new sub 

Sub ListFolder(ByVal olFolder As Object, ByVal ws As Worksheet, ByRef i As Integer)

The procedure is composed of the follwoing lines of code 

•   Dim olItem As Object This line declares a variable to store references to individual email items within a folder

•  For Each olItem In olFolder.Items This line starts a loop iterating through each mail item in the current folder and in turn assigns each item to the 'olitem' variable

• ws.Cells(i, 1).Value = olFolder.FolderPath this line puts the folder path in column A of the sheet at row i 

• ws.Cells(i, 2).Value = olItem.Subject this ine puts the email's subject in column B of the sheet at row i 

• i = i + 1 the variable i in increased by 1 in order to move to the next line in the excel sheet

• Next olitem is line indicates the end of the loop through email items in the current folder.

•  If olFolder.Folders.Count > 0 Then This line checks if the current Outlook folder (`olFolder`) contains subfolders. If it does, the code proceeds to list those subfolders and their emails.

• The following code gets triggered  if in the current folder there are subfolders

  • For Each SubFolder In olFolder.Folders

  • ListFolder SubFolder, ws, i the same procedure is called againg to loop through the emails of the subfolder

  • Next SubFolder

• End if closes the statement 

• End sub closes the procedure 

Sub ListFolder(ByVal olFolder As Object, ByVal ws As Worksheet, ByRef i As Integer) 

Dim olItem As Object 

For Each olItem In olFolder.Items 

     ws.Cells(i, 1).Value = olFolder.FolderPath 

     ws.Cells(i, 2).Value = olItem.Subject 

     i = i + 1 

Next olItem 

If olFolder.Folders.Count > 0 Then 

For Each SubFolder In olFolder.Folders 

   ListFolder SubFolder, ws, i 

Next SubFolder 

End If

End Sub 

The full code is reported below to be copied in the Excel VBA module

Sub ListOutlookFoldersAndEmails()

    Dim olApp As Object

    Dim olNamespace As Object

    Dim olFolder As Object

    Dim ws As Worksheet

    Dim i As Integer

    ' Create a reference to Outlook

    Set olApp = CreateObject("Outlook.Application")

    Set olNamespace = olApp.GetNamespace("MAPI")

    ' Create a new worksheet in Excel

    Set ws = ThisWorkbook.Sheets.Add

    ' Set the column headers

    ws.Cells(1, 1).Value = "Folder Name"

    ws.Cells(1, 2).Value = "Email Subject"

    ' Initialize row counter

    i = 2

    ' Loop through all Outlook folders and emails

    For Each olFolder In olNamespace.Folders

        ListFolder olFolder, ws, i

    Next olFolder

    ' Release Outlook objects

    Set olNamespace = Nothing

    Set olApp = Nothing

End Sub

Sub ListFolder(ByVal olFolder As Object, ByVal ws As Worksheet, ByRef i As Integer)

    Dim olItem As Object

    ' Loop through emails in the folder

    For Each olItem In olFolder.Items

        ws.Cells(i, 1).Value = olFolder.FolderPath

        ws.Cells(i, 2).Value = olItem.Subject

        i = i + 1

    Next olItem

    ' Recursively loop through subfolders

    If olFolder.Folders.Count > 0 Then

        For Each SubFolder In olFolder.Folders

            ListFolder SubFolder, ws, i

        Next SubFolder

    End If

End Sub