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.
Featured Course
Master Excel Power Query – Beginner to Pro
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.
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
