Import Outlook to Excel with Power Query

Imagine a series of email messages that you want to track, such as:

  • Sales Inquiries
  • Complaint Letters
  • Job Application Submission

If your Outlook Inbox is a bit too disorganized making it difficult to see all messages of a certain topic, what if you could open an Excel spreadsheet and see a table/list of all messages by the desired topic.

We’re not talking about exporting all the emails from our account into Excel.  This table will be configured to show only the needed information to allow us to apply tack-sharp focus to our task.

This is easily done using Excel’s Power Query feature.  Let’s see how.

Take a look at my Outlook Inbox.  We want to bring in only the messages stored in the “Action Required” folder below the main Inbox folder.

If I can send all these messages into an Excel Table, reading through and processing the messages will be a bit easier to manage.

Creating an Outlook Query in Excel

We begin by starting a blank workbook in Excel and creating a connection to our Outlook Inbox using Power Query.

Select Data (tab) -> Get Data -> From Other Sources -> From Microsoft Exchange.

Enter your email address in the Mailbox Address field.

If this is the first time you have connected to your Outlook account using Power Query, you will need to provide your account name and password to permit Power Query the needed access to your Exchange email.

This is a one-time requirement.  Further connections will utilize the same credentials provided at this step.

In this example, I will use my email address associated with my Microsoft account.

Provide your password and click the Connect button to proceed.

Power Query course Leila Gharani

Master Excel Power Query Course

Beginner to Advanced (including M) 

Collect, Combine and Analyze Data with Ease - Create Pivot Tables with Data Model.

GET ACCESS

Connecting to the Outlook Inbox

Power Query creates a connection to the Outlook mailbox and displays the top-level sections of the account.

If you click on one of the listed tables you can see a preview of the data in that table, such as all the email folders and their contents.

These tables make it easy to import the full data then filter and sort the data in any way you need.

We’ll select the Mail table and click the Transform Data button to load the contents of the email into Power Query.

Filtering the Inbox for Only Needed Messages

Once we have brought the Inbox information into Power Query, check out these categories across the top of the table.

These headings allow us to sort and filter the table to include only the needed items.

Useful columns for filtering include:

  • Recipients names & email addresses
  • Date and time the email was sent
  • Date and time the email was received
  • Message importance/priority level
  • Attachment indicator and attachment filenames
  • Message read/unread status

An interesting column is the “Body” column.  This contains two versions of the email message: a plain text version and an HTML version of the message.

I want to filter the “Folder Path” column to only include messages from the “Action Required” subfolder.

You can also filter using pre-build text filters for items that begin, end, include, or don’t include specific text.

Other columns, like the “Subject” column display all the email subject lines.  The “Sender” column contains nested information about the name and email address of the sender.

If we want to include the “Sender” information in the output table, we can click the Expand Table button at the top of the “Sender” column and click OK to extract the names and email addresses as additional columns in the current table.

Excel Dynamic Arrays Course Leila Gharani

Master Excel Functions in Office 365 - Complete Course

Excel has Changed Forever! DON'T MISS OUT!

GET ACCESS

Getting Rid of the Unwanted Columns

As the table contains many unneeded columns, an easy way to get rid of those columns is to use the Choose Columns feature located on the Home ribbon.

From here we can easily select only the needed columns from the table.

Updating the Table When New Messages Arrive

As all the transformation steps have been recorded in the query, it will be a simple matter of pressing a Refresh button to re-execute the steps, updating the table results.

To send the results to an Excel Table, click the “Close & Load” button on the far left of the Home ribbon.

Updating the Query Results

When a new message is received that meets the query criteria, we can click the Refresh button on the Data or Table Design ribbons (or right-click the table and select Refresh) to re-execute the query to bring in the latest messages.

We see a newly added message in the “Action Required” sub-folder.

To bring the new message into the Excel Table, click Refresh.

The new message appears at the bottom of the Excel Table.

Getting Outlook and Excel to Work Together

When pulling messages from an Outlook folder, it’s a good idea to set up a rule in Outlook that automatically sends specific messages to the desired folder.

This way, you don’t even have to have Outlook open and running for the Excel query to reach into the Inbox and download the needed information.

You can set the query to automatically refresh when the Excel file is opened, making this a 100% hands-free operation.

Learn Excel from a Microsoft MVP

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Visit Courses
Free Tutorials