How to Import Email to Excel using Power Automate

hi everyone kevin here today i want to show you how you can automatically import emails from either gmail or outlook directly into microsoft excel this is completely free and once we set everything up it'll just run automatically in the background you don't have to touch anything when you receive an email if the conditions are met it'll automatically make its way over to excel so when would you use something like this well let's take an example at the kevin cookie company we receive all of our orders by email i know it's a little bit antiquated but that's how we work and then some poor soul at the company has to take the details of that order and enter it into microsoft excel and yes that poor soul happens to be me so i'm looking forward to automating this flow i'll walk you through step by step how to make this work all right let's check out how we can do this to import email into microsoft excel we're going to use a product called power automate and it's made by microsoft to get started let's head to the website office.com and on the home page you can sign in with your microsoft account if you don't have a microsoft account don't worry you can sign up entirely for free right down here simply click on sign up in this video today i'll be using a free account and all of this functionality will work just fine i'll click on sign in once you sign in you'll land on a page that looks like this and as the first step we want to create a new excel workbook and we also want to make sure that it's stored in onedrive so why is that well by being stored in onedrive this flow will be able to work all the time whether or not your computer is on or off to create a new workbook over on the left hand side let's click on excel on the excel start page in the top left hand corner you can create a new blank workbook i've already started one so i'll click on it right down here this drops me into the cookie orders workbook and i want to use this to capture all of the order details that come in via email if you're in a blank new workbook let me walk you through how you should set this up simply create columns with all of the data that you want to capture from your emails here in my example i want to make sure i track the customer email when the customer placed the order what cookie type they want how many cookies they want how they want us to deliver it and then there's one additional column called fulfilled and we're going to use this to track whether we've sent the order to the customer as you could tell i spent a lot of time creating this table once you're all done creating all of your columns click somewhere within your table go up to the insert tab up on top and then make sure that this is a table once you turn it into a table it'll look something like this also up above you can click here if you want to rename your workbook this will make it easier to find later feel free to give it some name that you'll recognize once you've made all of those changes our workbook is ready to go i'm now back on the office.com home page and next i want to set it up so our emails go into excel and once again we're going to use a tool called power automate to access that in the bottom left hand corner click on this icon that says all apps on the all apps page over on the left hand side here we'll see power automate if you've never used power automate before this allows us to create different workflows between apps and in this case we're going to use power automate to connect our email to microsoft excel let's click on this this drops us on the power automate home page and as a quick aside right here on the page you can see a whole bunch of different sample flows that you can set up to automate your life so there are some pretty neat examples that i think could help you save some time all right so let's get back to how we get our email into excel and for that we want to create a new flow up here in the top left hand corner let's click on new flow here we have a number of different options but we want to create an automated cloud flow the reason we want to select this one is we want it so when an email arrives in your inbox it'll automatically add it into microsoft excel without any human intervention let's pick this option this opens up a prompt where i can start building my flow and at the top i need to give it a name i'll call this mail to excel i think that's pretty clear what it does and right down here i need to choose a trigger for this flow and the trigger is what's going to kick things off and in this case i receive an email message so here i'll type in mail arrives and i see several different options down here here i have when a new email arrives in office 365. so if you have a business or school account you'll want to make sure you select this and down here i'm using a free consumer account so i'll select the option for outlook.com also you can even set it up for a shared mailbox and if i scroll down a little bit you can set it up for gmail as well it works the same regardless of who your email provider is in this case i'm using outlook.com so i'll select this one and then click on create before we start building out the flow i want to give a quick preview of what the email looks like that i want to import into excel so here i receive an email and it could come from any customer email address the subject will include cookie order and then the details of the message will include the cookie type the quantity and also the delivery method now that we've looked at the email let's jump back into power automate back within power automate i'm ready to set up my flow and although it might look a little bit complicated it's actually not that bad and we'll walk through all of this step by step right up on top i see the first step of this flow when a new email arrives and here i can specify the folder that i want to check i could change it to something else but i'm going to receive all of my emails in the inbox so i'll leave this as is down below i can also show some advanced options and these are basically additional filters that i can apply to the messages so does it have to come from let's say a specific email address should it include a specific subject and in this case all of the orders will say cookie order so i want to filter based on the subject here i'll type in cookie order i could go through and i could set all these different types of filters but this is all i need now that i'm done with this first step let's come down to this button and click on plus new step this now adds a new step and for this one i want to take the html contents of the email message and i want to convert it into text and the reason we're doing this is i want to extract some details out of the email message once i type this in right down here i see an action for html to text preview i'll select this one this has now added a step for html to text i'll click into this area to specify the html and when i click there over on the right hand side i can see dynamic content that i can insert and i want to take the body of the message that's where i'm going to extract different details i'll select this one now that i've selected body let's add one more step for the last step i want to add a new row into my table in microsoft excel right up here i'll search for another action and i'll search for excel here i see two different options there is excel online for business or excel online onedrive if you have a business or school account select this option and if you have a free consumer account which i'm using today i'll select the onedrive option i'll pick this one right here now that i've selected excel online for onedrive i see a whole bunch of different associated actions and when i receive an email i want to add a new row into a table and right here i see the option to add a row also like this next i need to specify what excel workbook i'm working with and when we first started we created that new workbook so we want to make sure we select that file right up here i'll click in file and let's click on the browse icon once you click on the browse icon it'll show all the different files in your onedrive and here i see my cookie orders workbook i'll select this one down below i now need to select a table here when i click on table i see that there's one table contained within this workbook called table one i'll select this one here now you'll see that power automate automatically fetches all of the different columns from the table here i see the customer email the receive time the cookie type all those columns that we set up earlier in that first step to the right of all of these different column names i see this field and i need to specify what details of the email i want to populate into this field and the first two are really easy here i'll click on customer email and when i click on that over on the right hand side i see all of this different dynamic content with dynamic content i can pull in things like who the email came from who it was sent to what the subject of the message was what the body of the message was in fact if i simply want to pull these different attributes of an email into my excel table there's no need for this second step with html to text the only reason we added this step here is because we want to extract some of those details from the body but if simply pulling in the entire body on its own is good enough well you could simply skip that second step here i want to capture the customer email so right here at the top i'll select from for the next field i want to indicate when we receive the order and here i see the receive time i'll click into this field and once again in the dynamic content i can scroll down and if i look through this list here's an option for received time i'll select this next i want to capture the cookie type the quantity and also the delivery method and if you remember from looking at the email earlier we want to pull this content out of the body to extract this content from the body we're going to use an expression let's start with the cookie type and right over here when i select this field here again i see the dynamic content except instead of choosing this dynamic content let's click over into expression and here i'll enter in an expression i've also included this expression in the description if you want to look at it and reverse engineer what it does essentially what this does is it'll look at my email message and it'll pull out the number associated with the cookie type now that i'm done entering in this expression i'll click on ok for the quantity i want to enter an expression for this as well so here i'll select the field i'll click on expression and let me paste in my expression once again all of these are in the description down below if you want to use this same example here i'll click on ok and lastly i want to add one more expression for the delivery method so i'll select this field click on expression type in my expression and then i'll click on ok for this last column we're not going to use this instead once we receive an order in excel we'll mark down whether we've sent the order or not so we'll still have to manually do that i'll leave this one blank and that's really all there is to it we've now set up this flow so it should just work automatically now that we've completed entering all of these details at the very bottom make sure to click on save and just like that here we see that the flow is ready to go and they recommend testing it out that's certainly a good idea here i have an email message all ready to go i've addressed it to the email address associated with that flow i've also entered in the subject cookie order and here are all the details of the message now when we were setting up the flow we set up a few different expressions to extract email from the message and you want to make sure the message exactly matches the format that you're looking for otherwise it can mess with your expressions once again if you're simply taking the entire body of the message it won't really matter that much once you're ready to send let's click on send and let's see if this works back on the my flows page here i could hover over the flow that we just created i'll click on the ellipsis and right in this menu i can click on run history and here i can see that the flow successfully ran just a moment ago when i shift over into microsoft excel here i can see that it automatically added the order details into my workbook here i see the customer email address when they place the order the cookie type the quantity the delivery method and then here i have the fulfilled column we just released the pigeon about 30 seconds ago with the 20 cookies so here all type in fulfilled now if the pigeon gets there or not well that's not our concern once it leaves the factory we're done with it that's how easy it is to import emails into microsoft excel or i mean it takes a few steps but overall it's pretty easy and you never have to touch it again this is now running in the background and you'd be surprised at how many cookie orders i'll probably get from people watching this youtube video alright well let me know down below in the comments will you be able to use this to eliminate any of your manual tasks to see more videos like this please consider subscribing and i'll see you next time [Music] [Music]

Channel: Kevin Stratvert

Views: 96,503

Keywords: kevin stratvert, import email to excel, mail to excel, email to excel, gmail to excel, outlook to excel, import data, power automate, mail, outlook, flow, excel, microsoft, gmail, import email, import mail, convert email to excel, email, import mail to excel, to excel, importing, bring in, mail excel, email excel, exchange, microsoft 365, office 365, how to import mail into excel, how to import email into excel, how to, excel mail, excel email, microsoft flow, ms excel, kevin

Id: OrCs36S3w3w

Length: 13min 32sec (812 seconds)

Published: Fri Jun 11 2021