Outlook Emails in Power Automate Desktop - Full Tutorial

this power automate desktop lesson will show you how you can extract information out of Outlook emails and save them to excel we'll also see how we can handle attachments my name is Anna Jensen let's learn some Microsoft power automate desktop we want to navigate to Outlook and here we want to create two new folders that will be subfolders to inbox one of them is called PID and the other one is called processed so empty for now Now navigate to the course page the link is in the description below click the download the project files here click the three dots and say download this will download a zip file and if I just do this then I can push it to my desktop here we have our zip file right click and say extract here this is our project folder if I open this one you can see we have two folders We have one called attachment open that one that is empty this is where our attachments will go then we have five sample emails in MSG format we will move them into our pad sub folder in our inbox so Ctrl a and that will Mark everything find my inbox and let me just do this and then I will drag him over here so now you can see we have five emails in the subfolder called pad these are the five emails that we'll work on imagine that this will be emails coming in every day and we want to handle them so this is just for one day for a couple of hours but this robot will work every day now I will go back here and then we can see we have an Excel book if I open that one here we just have an empty sheet or almost empty we have a table the shade is called emails and then we have this table here we have date from subject body and attachments the intuition is that we want to save all the information into this sheet so let's go create that or close this Excel sheet and also minimize this and what I want to do is to grab this project path up here I can shift and I can see right click copy as path and then I can move back to Power automate for desktop let me just see here so the first thing we'll do is to create a variable for our project and we can do that here so I'll find a set variable we'll do this because a project path can change right now we have it on our desktop but it can easily move elsewhere when I move the robot into production so the variable name we will not call it new VAR I will call this project path and then hit enter then in the value I press Ctrl V this is the project path that we just copied remember to move the quotation marks like this so then I can click save now we have a project path we can start the Outlook activities so find a launch Outlook here and drag it in this will just open up an Outlook instance that could be that can be referred to as Outlook instance this is a variable then we can click save say that we want to retrieve all emails from this Outlook and I'll find a retrieve email messages from Outlook and drag it in here we need an account first and you can see we refer to the Outlook instance that was the one we launched up here so the account that is just the name of your data file my name is foreign folder let me just show you that that one is error inbox and then we'll say pad I use this pad instead of just the inbox just to keep a little bit private here so you don't see all my emails but also because it's easy we only have five emails here it will work on my normal inbox as well we can just change it here in mail folder so what I want to say is that I want to say inbox and then I want to say pad like this we also want to go down here and say save attachments and then we want to say let me just do this and we want to say where do I want to say save my attachments into well we want to save it into the project path that is only the main folder then we need to add the attachments subfolder which we have there it goes like this click the little X here double click the project path and go down here have a backward slash and then we will just say attachments like this and we can click save so we have a lot of filters here we will not charge them now you can also say do we want to mark them as read or not doesn't really matter now because we'll just move them whenever they're processed but these things you can play a little bit around with if you want and I can click save so now we retrieve everything but we will stream into a list of Outlook mail messages let me show you how that looks so if I click run here the only thing we'll do is just to retrieve email messages from Outlook and if I go over here retrieved emails this here you can see our list of Outlook email messages so if I just pick one of them that could be for my colleague Steen you can see this one I can click more here you can see we have some different things that we can grab out for example we have a body we have a date we have a from and we have the date that we want a object so a lot of things that we can work with and similarly if I just go back here and let us just see which one is this one from Anna this one has had an attachment so that will be this one up here and here we we can see we have an attachment here it got saved into actually you have two attachments that could save into this path so what I want to do is that you can see here the name was called Twitter information.txt and you can even see the directory so we want to save all these information to an Excel sheet it's very very easy so let me show you I just click close here we want to iterate through this list here because right now the file names are only in a list we want to do something with them and what we want to do is that we want a fridge like this and drag it in here so the value to iterate that will be this list up here when we use a for each we always iterate through a collection of something that could be a data table which is a collection of data rows or here a list of Outlook mail messages so click this little X here and then say retrieved emails we will store the emails into current item and we can refer to them so for each one of the emails we can say we want to refer to the Q and item so that will make referencing very very easy I can click save nothing will really happen because we're just iterating to him taken one by one we're not doing anything yet um let's just have a display message to show that we can actually get the information out of them out of it display message then go down here to message to display so we want the date the from the subject and the body let me show you so have it in here in the message to display like this little X here click this arrow to the left of Q an item here you go we now have the things that we want the first thing we wanted that was the date double click here this is the expression for date that is q and item dot date then we want the from we can of course write these but I recommend you just do it automatically in that way we will reduce the risk of errors so I click this little X here we wanted to find the from this is the expression from from everything else and from looks like this with a dot but for some reason the from will be in a half brackets so then we'll have subject we don't have to worry because we can just click here find the subject and finally we wanted the body so I just find the body here move up here that's it then I can just click save now let's try to run it to see that we can actually write out these information from the emails there you have it we have the date time here the email the the subject and then the body for the next email we have it here and similarly for the two other ones so for now we can write out the things that we wanted we can grab them now we just need to save them to Excel and then we want to handle attachments very very simple and but you just need to know a few little tricks so keep watching so um right now our Excel sheet is empty but imagine that this robot runs every day let me just show you so if I open up the Excel epic again this shadier now is empty so when we run this we will have five rows added to it but imagine the robot runs tomorrow I don't want my data to get overwritten so what I want here is that I want to say add the next rows from the next emails at the bottom of this list then I can just change this Excel sheet every month every week or every day if I want it but don't override any data we need to tell power automate for desktop that it looks like this so I'll just grab this sheet name here I can remember it called it's called emails but I like to just copy paste a lot of things so we don't make any mistakes I right click rename Ctrl C just to copy this and then we can move out here first thing we want to do is to launch and Excel instance because right now we have this for each but before that we want to make sure where we want to add the data to just like we talked about so I find a launch Excel drag it up here above the launch Outlook it's not there's a lot of usually we want to have in whatever um whatever sequence we have these actions that's quite important but sometimes it's not that it's not that important whether or not it's just one step above or one step below so for now we place it here but we can easily have him or just before the for each for example we don't want to open a blank document we want to open up the following document here we will use our project path again so click this x here we'll say project path then I want to say after that we have in the project folder we have a an email called emails.xlsx to make a bad watch less emails.xl is X like this we will not have it visible then we can click save so we don't we are not really interested in uh what's there but we just need to know when the first free row of this Excel where the first three row of this Excel sheet is it looks like this so I want to say set active this will set an active Excel worksheet and drag it in here so I'll say name and now I'll say Ctrl V and for some reason it's blank but it was called emails so the emails that we want to activate that is called emails and then I want to click save so now we're activating this we can now use a very very clever action called get first free column Row from Excel worksheet drag that in just beneath the set active Excel worksheet this will produce two variables first free column and first free row so that is brilliant this because we need the first three row so little this will be an integer available for example right now it's just the the first one but and that will be the Row 2 because we have headers on but imagine that we have a thousand rows in there then we can get it like a thousand and one and that was the first zero or 1002 so then I can click save so now we get this into a nice variable we will not use the first three column that's not really important but we will use the first free row similarly we also want to close Excel because when we launch excel in power automate for desktop it's sometimes lock the Excel book so we also we always want to have a close Excel drag it in in the end here and here we will just save so when we add data to it in this process we will choose save so then I can say save like this so now we will create an output data table where we will take all the information out of these emails so when the robot runs we get a list of emails right now it's five emails and then we want to say for each one of these emails drag the information out and those ones will be in five columns to our Excel sheet so we'll create data row and that's that has the advantage that we create this data row add it to a data table a data table is a collection of data rows so we can just add five data rows to it a data table a data row is just like a row in an Excel sheet but it only exists when the robot runs it has the advantage that it has huge huge speed slash performance advantage over just writing it into Excel we could write it into Excel in this for each but we choose the data table activities for performance reasons so what we want to do is to create a new data table so up here in the beginning I'll find a set variable here drag it in below the first one we will call this email data and now we will create a data table it will only consist of headers because we don't want to hard code any thing into it just the headers so our flow down here in the for each can add the things to it this it goes like this so in the value have two percentage sign like this and we want the curly brackets curly brackets start curly bracket end then we want a hat because the Hat shows a power domain for desktop that this is a header row then we want a hard bracket start and a hard bracket end and now we can have our headers so the first column in the Excel sheet that was date but let's just verify what we're actually doing here we'll be adding these five to our data table so we can easily transferring them to excel after we save them here so the first one is date in single quotation marks armor separated like this the next two ones that is from and subject so what I will do here is just in single quotation marks say from comma and then I will say sorry in single quotation marks I'll say sharp check like this comma and then I will say body and then comma and then the last one watch wash attachment so attachment like this and another single quotation marks if you forgot a comma or a single quotation mark here we will just have an arrow but it is fine for now so now we created the variable now we can add things to it so instead of this display message here let me delete that we will add a row into this data table for each one of these retrieved emails so find an insert row into a data table this is a native or activity that just automatically adds the row to it we can also do it with another set variable but I want to show you both things so you learn the most the data table that we want to fill in that is the email data from up here so click this x here and use the email data we want to add the row or to the end of the data table and now our values comes in this one will be a data row and remember a data table is a collection of data rows so we want to add a row and that row needs to have five elements because we have five columns in our data table up here otherwise we will have a syntax error and what we want to do here is that we want to have two percentage sign in starting at the end we also want to have a hard bracket start and a hard bracket end like this and then our elements comes into here the first element we wanted that one was the date and it is also corresponds to this date up here so click the X here move into current item by clicking this Arrow move up here and then find a date by double clicking it it will look like this so what we need to do is that we want to delete the percentage sign these two are here and then in still inside these hard brackets will have a comma and then we want the Q and item from so click the sex here find the from you can just collapse the date it might be more easy again delete the percentage sign in the start and in the end we only need them in the start at the end of the entire row so here we have the Q and date then we want the Q an item from this is exactly the same Expressions that we had before with our display message so another comma then we want the X and now we want the subject do like this delete the send sign again like this another comma Now we needed a body so do like this and double click the button so here I will delete the percentage sign here and here so right now we only have four we also have an attachment column we already created that um here but we haven't handed the attachments yet we will do that a little bit later so we'll just leave this one empty for now that is two single quotation marks now you can click save and we have added a row into our data table remember a data table only exists in the memory that is during runtime of the robot that is when we click run here and to the robot stops in that way we can use these data tables otherwise it will get lost so we need to be a we need to remember to save it and what I will do here is that I will just have an up here I'll just have an a right to Excel worksheet move it outside the fridge because now we added items to our data table we can write at a data table once to the email data because now we are done getting the data in email data the value to write that is the email data that one is got updated here like a couple of times right now it's five rows but it could be fewer or it could be more the right mode that will be unspecified cell the column that would be column A because we wanted to have it in the First Column that was corresponds to this column the row since we wanted to not overwrite any data you can see that we found this a first free column row and we want to use this variable up here because this one gives a number we have the first three row is and I can just go in here click this little X here and I want to say first free row like this and then I can click save so now we actually have a working flow we still don't handle the attachments that's fine let's just run it to see it works so and let me make sure that this is done so I close it then I can click save now we are launching Excel we set the active Excel worksheet handle the five emails and we save the Excel book let us inspect the data because here in emails xlsx there you go we now and let me just do this we now have everything nice and smooth what I can do here is that I can just wrap text so just it's a little bit more readable but we have our body in here if we wanted to work with them imagine that we want to grab data out we also have this object here and the attachments column this one is empty for now but we will fill in information about the attachments later on so everything is here even the received date so um quite easy wasn't it if you like these kind of videos please give it a thumbs up that will really help me a lot thank you so let's handle the attachment because um we handle everything else and let me just uh close this one here because what I want to do here our strategy now is that for each one of the emails in retrieved emails and remember that one was a list of Outlook mail messages it looks like this so only two of them had attachments tied to them so I only want to say I want to save the attachments from those and I want to add them to the correct column that was the attachment column but for the emails with no attachments attached oh sorry that was a little bit for the email with no attachments we don't want to update the column that should just be left blank so we will need to add ask a question here in the for each we will do that with an if so an if always asks a question it says is this true or false and then we can act accordingly so for each one of these emails I'll ask a question I want to find out whether or not that email has one or more attachments tied to it so the first operand click the sex here a current item move a little bit up and you can see this attachments this is a list of files so if if an email have two attachments then that list will have two elements even if that email have one attachment it will be a list with one element that will still be a list but if we don't have any elements the list will be empty and we'll take advantage of that so we will say um if this q and item dot attachments if that is not empty so that is if it isn't empty then we want to do something because now we know that we actually have some attachments tied to it so I can do this so and all the actions that we want to perform whenever we have attachments will go in here first we want to create a unique folder and we want to create a unique folder so we can easily find the attachments again and we want to save that name into the Excel sheet so here I'll find a create folder so I just need to be a little more specific or be better to read it was there so create folder drag it inside there um if so create new folder into I want to create a new folder into the project folder project folder and then backwards slash attachments and let me just show you here we have actually saved these attachments here a couple of times because we ran the robot um so these ones have got saved like we ran the robot like three times so we want to instead of this mess we want to create folders for each one of the attachments instead so I'll say create and create new folder into and that one will be in my project path like this and then I want to say I want to create it in attachments so backward slash attach mints the new folder name so that will be unique and I need some some sort of a unique number I could use the received time down to the seconds but I could also use the entry ID which which is a unique mail ID so the new folder name I can go in here again into current item and then take the entry ID and pick that one remember that it gets stored into a new folder here so then I can click save that's it so now we create a new folder and this is one for each one of the current items now I want to say well um we can have one I know I have at least one attachment tied to this email but I could have more so I need to have a for each and then move um each one of them into the correct folder and the folder that one we have the name here that was called new folder so we can just refer to that so now I want a for each inside this if so find a for each and this one will take each attachment to the email again we know we have an attachment because the Q and item attachments is an empty so the value to iterate well that will be the current item attachment so I go here and find the Q and item attachments I'll delete this list of files we will not store them into q and item two but let's pick attachment like this and then I can click save now I can refer to this attachment when I want to do something the only thing I want to do is that I want to move this attachment into this newly created folder so go up here and then find a move file here and drag it in down here so the files to move well I can just there's only one file and that will be the attachment the attachment that one was this variable here called attachment that is a file the destination folder well that was the new folder like here find a new volume if file exists I can override or do nothing doesn't really matter that will not happen because the email at the entry ID is unique so then I can just click save so now we took all of the attachments we created a folder for once and we have moved them into it now we can update the lock so um in the end of the if I want to have a insert row into a data table so let me go down here press Ctrl C and since I wanted to move it up here just before the end click the end and then say Ctrl V it will get pasted into just above this so I just need to update this here and what do I need to update it with well I want to look inside the new folder and I want to grab that name so now I just don't want the path I just need the name because I know it's in attachments to open this delete these two single quotation marks click the sex here then have a new folder find the name and do like this remember to delete the percentage sign there you go we now have updated with the name as well so I'll just save it in case my computer breaks power automate for desktop never Auto saves that is a request Microsoft if you watch this video so we also want to to handle the emails whenever we you can see here we we also want an else because right now we only handle the things with the attachments on and here we have and so we have this one here but we want to move it up here in ndf so if we don't do this then we want to do this else here what will happen here with the attachment if that is that we perform this and then we'll perform this we're not interested in in that so I will find an else practice else in in the end of the if so this one will just ask is this true yes then we'll perform this else will do here so if this is not true that means that the attachments is empty that is the email has no attachment I can just drag this one into it so now we're updating the data table accordingly if we have an attachment we will write that in the last element of the row if we don't have one we will not update it now um we will just move for each one of the emails we will just we will move them into a processed folder and that is in the end of this for each so now we're done with everything we can move them so find a processed I'm sorry process email messages in Outlook and drag it in here the account is anas anus jensen.org oh that misses an A and again if you want to help me please give this video a thumbs up that will help me a ton this video will get out to more I can make more videos in the future because I will get more subscribers earn a little bit more so thanks to each one of you that supports this channel but also thanks to you that just watch it's highly appreciated so the email messages um to process that will be the Q and email that will be this Q an item take this one here move email messages to mail folder and now I want to move them I want to moving into this processed folder from pad to processed so if I go down here say inbox backward slash then I'll say processed like this and I can click save shouldn't we go test it so when I click run here the robot will run and the previous files that we saved will get deleted and now we will have an entire new flow running we can see that it looks like 11 seconds first let's move to Outlook you can see we have an empty pad and the five emails are now moved so far so good this is clever because next time the robot runs it will not process these ones again now we just need to have a move side inside the attachments like this you can see we have two folders so these are quite long but they are unique names we can also name it dates but that doesn't really matter here we have one of the attachments tied to this email we have here we have two of these attachments tied to the other one and let me just go into emails yep and here we actually if we go over here we can see that um now our rows got added to it because we ran it five times before so that was these five now the second run that will be these fives so this you can just see them as five previous emails that was expected it won't happen again because now we are moving with the processed emails to a new folder so that was quite quite clever but if you go over here we can see that we have the attachments folder here and again you could argue that you want a smaller name but that was just not the intention of this exercise so clap yourself on your back you have completed a very Advanced Outlook email exercise the next lesson is right here on on the screen go click that one

Channel: Anders Jensen

Views: 22,090

Keywords: power automate desktop, anders jensen, outlook in power automate desktop, emails in power automate desktop, power automate desktop email, power automate desktop email attachment, power automate desktop email multiple attachments, power automate desktop outlook, how to save emails in power automate desktop, microsoft power automate desktop, outlook emails in power automate desktop, outlook actions in power automate desktop

Id: x5l7Ow7s5Lo

Length: 33min 10sec (1990 seconds)

Published: Tue Oct 04 2022