Need up to 30 seconds to load.
Today we're going to have fun importing data from Outlook to Excel. We're going to make the
whole process dynamic, so it's not a one-time thing. We're going to set up a connection to
Outlook, so we just need to press Refresh and we get the latest Outlook information
that we're tracking directly in Excel. Obviously, we're not going to dump all our emails in Excel.
There's going to be something specific that we want to track. For example, sales emails
or project ideas. Anything that you receive a lot of emails about and you'd like it all
organized in a nice Excel table. It's so easy to set this up. Don't believe me, just watch.
Okay, so this is my mailbox and I want to import emails from a specific folder of this mailbox
to Excel. I don't want to import everything, I want to just import the content of the Action
Required folder. Because, let's say, all of these emails are going to be easier for me to analyze
if i get them in an Excel table. To do that, just open up Excel, open a blank workbook, go to Data,
"Get Data", from other sources, from Microsoft Exchange. Now, all you need to do is give it
your mailbox address which is your email address. Next step is to authenticate yourself. So, this is
something you need to do, if it's the first time you are connecting to this mailbox. Once you've
done this and the connection is set up, you don't need to do this every time you want to refresh
your data. The connection is going to be there. I'm going to use my Microsoft account and then, sign in.
You just have to select your Microsoft account and your password, and then click on Connect.
Now, Power Query is going to set up a connection to this mailbox. Check this out. Notice all the different
tables I get. I have a calendar table. So, in case you're interested to keep your eye on
specific meetings, you can import this table and add the filters that you need. I have a mail table,
this is where all the emails are. So, not just the emails in my inbox but in all other folders as
well. I get a table for meeting requests, for people, and even tasks that I have in to-do.
In this case, I'm interested in mail, so I'm going to select that and go ahead and transform the data.
Now, take a look at these categories. I get the folder path, so these are the names of
the folders i have. In this case, actually, I just want to restrict it to the Action Required folder,
so I'm going to click on this dropdown and just select Action Required and you can also use
text filters, whatever makes sense to restrict it to the view that you need. So, I'm going to click on
OK, and my folder path is going to be restricted to the Action Required folder. Here, I get the subject
of the email. Sender comes in as a record. Notice we have a different category for name and the email
address. This means if I expand this, I can get a separate row for the name and for the address.
I also get the "Display to" information, and if I scroll over we have "DisplayCc", we have the two recipients.
This one comes in as a table, so here, we get to see the name and the address of the recipient.
If we have more recipients, we're going to see them in this table, and we have "CcRecipients", "Bcc", the
time the email was sent, the time it was received, the importance of the email, whether it was read,
whether it has attachments or not. So, if you're looking for special emails that have an attachment,
you can set this to "True". Now, in this case, all these emails that are in the action required
folder, they don't have any attachments, so I only see the False option. Then, I also get the preview
of the email. And, on the side here, I get the body of this email. I get two versions of this: the Text
body and the HTML body. Now, actually in this case, it makes sense just to go with the Text body. I'm
going to add a check mark for that and click on OK. Last column is a unique ID. So, there is a lot of
things here that I don't need, and some parts that I might want to expand. So, let's say for Sender,
I actually just want to keep the name, so let's go with OK. Now, let's restrict this to only the
columns that we need. So, I'm going to go to Home, Choose Columns, let's uncheck "Select All Columns",
and only select the ones that I'm interested in. That's going to be Subject, in this case,
the name, and the body of the email and click on OK. That's it! All my steps are recorded,
every time I refresh, these steps are going to run. Now, let's go ahead and send this to our workbook.
Close and load, this is going to create a new sheet and it's going to upload my data in an Excel table
on this sheet. This way, I can view the content easier and I can even use formulas that reference
this table. And you're probably wondering, "Is this all dynamic?" Well, yes, it is. Let's go ahead and push
another email to this folder. So, I'm going to go to the inbox here, let's take this one that
says, "Don't forget to record", I'm going to drag and drop it in the Action Required folder. I can see it
on the bottom here, so how about in Excel? Let's right-mouse-click and refresh this, and I get to
see the email right here. That's the subject, that's the name, and that's the content. Which is also a
good time for me to let you know that, in case you're not subscribed to this channel, consider
subscribing. Now, as you can see, all of this is dynamic, so if you are using rules in Outlook
to move over certain type of emails to another folder, you can then create a connection from
Excel to that folder and you don't even have to have Outlook open. You just go to Excel,
you right-mouse-click, you refresh, and you get the latest information in your Excel table. How cool is that?
So, what do you think? Was that difficult? Not really, right? All you have to remember is go
to the Data tab and the rest is going to come to you. That's my tip for today. Thank you for being
here. Thank you for watching. Hit that thumbs up and while you're down there, you might as well
hit subscribe, if you aren't subscribed yet. I'm going to see you in the next video.
you
