UiPath: How to Extract Outlook Emails with Regex (Full Use Case)

let's look at one of the most typical cases we get as rpa developers and that is data extraction today i prepared a use case where we have a outlook inbox with emails in and we want to look at some of these emails and we want to extract data from them we will use a lot of outlook magic and then some regex so the task today is that if the title the subject of the email is fsfn address change as you see here and here then we want to extract data out of these here also if we don't see this or code in the topic the subject then we will just ignore so if it is with a fffsfn address change in this object then i want to extract the case idea i want to extract the postal code here and a date time moved in the real world we will probably have like 1000 emails and we want to extract all data however for simplicity we are only taking these two males and three of these data fields let's see how that is done in uipath so first go to uipath and then create a process i will call mine regex outlook and then click create open the main workflow here what i want to do first is to get the outlook mail messages in this folder note that it is in inbox and in the rpa folder we could have chosen any other folder here in my account so in activities i search for get outlook mail messages like this and drag it in what this does is that it will get me a list with all the mail messages that i specify we need to fill out some properties first we have the account this is just the default if we're not specifying anything else however i like to specify it even though you can see i only have one account but let's say we add one so it's anus yenten.org go back to uipath and up here i could either hard coded in but i like to create variables so i create a variable that says str outlook account hey friends improve your career and join the uipath rpa professionals facebook group a no spam group where i and all of your skilled uipath developer colleagues will share info tips and tricks there's a link to the group in the description below and then in the value i will specify in the account that is anna's jensen.org you should specify yours because then if i go back to get outlook made messages then i can just specify in the variable up here sdr outlook account that's fine now the mail folder that is inbox but we want to look in the rpa folder so what we do here is that we get just either hard coded in or again create a variable i always want to create variables because if these values they change then we can easily change them down here and furthermore if we are supposed to use these variables again we can easily just have them so always create variables instead of hard coding so we create another variable that is s t r and then folder maybe we should call it outlook folder like this and the folder that is in quotation marks so the main folder that is inbox then a backwards class rpa because that was the folder's name like this so now again we go to properties we delete the inbox then we can press control space and we can say str outlook folder so now we specify the account in the mail folder do we want to mark them as red now that doesn't matter do we want to take only unmet unread messages no probably not we will take the top 30 usually i'll probably change this to take 100 or everything but just leave them here as we only have four emails then we will output it to a list so go down here and press ctrl k we'll say list of mail messages then we'll click enter one advantage this have is that if whenever we need to create output simply just go in here and press ctrl k and then type in the desired name because uipath then automatically create the right type if we go down to variables we can see that we have a list of mail messages that was our name and the variable type is list of main messages if we open here we can even see it like this so now it's time to read these emails and print them out we will just do a for each like this drag the for each in under here so this for each item we can easily change this to whatever we want we can say for each anders for each mail that doesn't matter choose a describing name as it is with mail so for each mail in this list that we got up here so let me again control space to have the intellisense on so for each male in the list of male messages then we want to do something here in the body so up here in the type argument we can see that there's an object here we need to change that to mail message so go down here and browse for types let me drag this in search for mail message like this and remember to choose the one on the system.net.mail and not the one on the system.web.mail so choose this one and then press ok like this now we can just print out some standard data of these emails let's take the date subject and body so we go back to ui path and we take each mail message here so we'll find just a right line drag in the right line what we can do is that we can say mail then we can say dart date and then to string this one will just print out the date of each email into the console here in the output let's try that now we've run it and remember we only look at these four emails and we can see that we have dates now for these four emails that one we can work with later but let's just copy this ctrl c ctrl v i can change this one here to subject like this this one will print out the subject again ctrl c ctrl v and let's print out the body like this let's try to run our automation once more go down to the output console now we can see we have the date we have the subjects and we have the body here that's fine what we want to do now is that we want to make a condition because we only want to look at the outlook mails with the subject of fsfn address change i'll go back to uipath here i'll make an if like this i'll drag in the if still in this for each male because i want to check each male so inside this for if remember this i want to look at the subject i have it right here so what i do is just copy this one here and paste it in but i need to ask a question always we need to ask a question in this condition that can be answered with a true or false so here i need to ask i'll say dot contains like this parentheses and then the value in quotation marks and what do we want to search for i want to search for fsfn address change like this so if we ask does this string contains this one true or false now we know then we can say if this is true then we want to work with that particular email and if not we're not doing anything so what we want to do is that we mark all these hold ctrl down while you click them and then we can simply just drag them down here so now we are only printing out the emails where the subjects contains the fsfn address chains let's try it like this we go down to output output console you can see we have our first email here we have our second here with the data and nothing else that's fine we want to have these data written out and i know it's only the date subject and body but let's write these things out to an excel sheet and then we can work with the body and the regex afterwards so up here in the beginning i'll build a data table so find a build data table and drag it in over in the output that is the name again we press ctrl k and i say dt output like this then i click the data table here and i'll specify what goes in so let me delete these standard here and we will have for each of these things that we want to put in we will have a name so i want to have the date so just say okay then click the plus again we want the subject right like this and we want the body so i will say this this one will print out date subject and body of this email still we have not extracted this case idea the postal number zip code and the move time but we will get to that right here and we can just click ok so now we build this data table and call it dt output it's time to add some data to it so again here in activities find a add data role like this and drag it in here in the beginning of the condition so what do we want to do we want to add the date subject and body to this output table so first in the data table specify the data table that was dt output right and then we can specify an array so click the three dots here let me expand this what we need to do is that for each of the columns that we want to have data in we want to write that out you do that by having curly brackets like this and then specify what you want to have in your first column of this row we want to have the mail date to string like this and to get the next column we'll just say comma and we'll say mail subject to string and as you know i hate manual work i like to automate things but i cannot copy this because we have this open so i cannot get out here that's why i'm writing again comma then mail we have the body then to string like this so now we have what we want here in these two data rows like this and we will save it to dt output the data table we created up here why don't we write that out remember to write them out outside the for each so i mark this and then i'll have it written out after the for each down here so find a right range like this choose the one from workbook not this from excel that's the one down here we drag it in in the end the workbook path well for simplicity let's start with writing it out to my desktop so i'll probably do like this just go out to your desktop right click new microsoft excel worksheet this one will just be called this we will rename it afterwards go back here so the workbook path let's create a variable for this as well as this could as this could often change so i'll say str excel path so what path is this well go back to your desktop shift right click on your excel sheet and then copy as path now we can paste it in here in the value like this again we can close down the variables manager in the workbook path we simply just type in str excel path this one holds the value of our excel sheet we will start in range a1 i usually just delete these because a1 is standard we want to write to sheet1 that's fine and we wanted dt output like this one thing that we will need to do is to have the headers on because we have some nice headers that we build it up here let's try to run this automation to see that it actually works like this we can minimize uipath and then we can open up the excel sheet let me minimize this and as you can see we have the date subject and body for the two emails could we see that here yes we have him here that's nice but we still need some data extracted out of the body so let me close this one again and go back to uipath now we don't have any need for the date so let me delete this we also don't need the sharp deck we're done with this we have written it nicely out we only want to work with male bodies so let this be for a little while now we need to talk about something important because we got the mail body here we can find the mailbox written out here so go down to your output console point to the first entry here the participant information right click and then copy then move to a browser and go to regex101.com in your test string we will paste it in and i warn you if you work with private data be sure to have it anonymized because this is a public site so don't share any personal information this one is of course demo data so now we will look at regex and what is regex regex is just a simple pattern that can define extraction of data so for example say that i want to look for echo we just type in the regular expression echo this is a really simple regular expression and we can see it find echo we could also ask let me have a backwards class then d that is digits say that we want to look everywhere four digits and there need to be four of them so we just do like this and we can see that we find four digits here here here here here here we can do something in ui path and then we have printed this one out but this was not what we wanted i just want to give you a presentation of regex so let us write a simple regex pattern that will get us case id out hey friend is this video helping you please help me and give it a thumbs up of course we could do one two three four five six seven we have seven digits here so we could do a backwards less and then we can say digits and we can say we want six digits out but look what's happened we have one result here and we have one result here that's not good we want to specify that we want this one up here so what we can do let me delete this we can say we want to look behind this case idea so i make it positive look behind i will really urge you to practice regex if you don't know it it's worth it so i'll say look behind the case idea then a colon and a space like this you can see that we are here but we haven't defined what we want to extract well we want to extract these seven digits however we are not sure that it's seven each of the time we just know that there are some digits so what we do here is that we'll say backwards less digits now we can see we extracted one digits then we'll say plus that is one or more digits we'll go with that because we have this case idea that's the clever thing now it doesn't get confused by this one so this is a simple regex pattern what i do here let me minimize this minimize this we just make a notepad to take some notes so right click on your desktop click new text document it doesn't matter we are not going to use it anymore after this lesson but just say case idea then we copy the pattern that we got here the regex pattern so copy this one here and paste it in below the case idea now we got it here then we go back to uipath and let's see this in action because we want the case idea printed out let's just first print it out to the output so what we do here is that we'll take a matches a matches here does what it says it matches one activity so below the add data row we add the matches we need some input and that is just the mail body to string so what we do up here is that we'll say mail body to string oh no i did some manual work i can just decorate it from down here so let's delete this so now we got the input then we need the pattern and we will just hard code in the pattern for now so do quotation marks and then paste in the regex pattern that you just made so ctrl v like this now you have the pattern up here then what we're doing essentially is saying that we want to look in this body that is the email body we do this pattern and then we'll extract the data if it exists so let me go back here and the result is stored stored in an i innumerable so we press ctrl k here and we can give it a name i'll just say innumerable of matches then i'll click enter like this now we could print them out so let's do that let's find a right line drag in the right line below the matches and we'll say i innumerable of matches then 0 that's the first match we only have one here and we know that if you weren't sure if there were any matches we should probably ask for this first but we will know that each time we have an fsfn address change we have a match here and we only have one so we do this and then we'll say value now we can write out the case idea for each of these mail so let's run the file again here go down to the output console and boom here you have your two case idea isn't that clever we also need the date move and the zip code so let's do these things in the regex editor the easiest thing is probably to get the zip code first we have it right here and here we don't have any reliable thing we can look behind because this city can change so what we do instead is try to make it as reliable as we can we know that we always have two letters here it's mi for the state so what we do here is that we'll have a parentheses then we will have these square brackets we will know that it's from a to set and it's always in capital letters so we'll have square bracket end and we'll know that we have two letters so again these curly brackets two what this says is that it says that we want two letters and we want exactly two letters and it needs to be capital from a to set like this now we can have the parentheses end and we can see that we indeed have him here here here here here here here here but we also know that we have a white space after here so we'll simply just say backwards class s now we have the white space here you can see that this one up here is not a match anymore then we know that we have five digits it's always like that so again backward slash then curly brackets we'll say we have one five tickets curly brackets now you see that we only have one match here that is this one what we did up here with the parentheses was to create a group say that we want the state out we have it right here we will not use it for this one but it is an important lesson and try to understand what went here otherwise just ask in the comments but we'll just copy this as we want both the state and the zip code so we go back to our notepad and then we'll say sip code then we do like this now we just need to have the date moved here the date time moved we want this one printed out we can easily solve that as well so let me delete this so what do we want to do here well we know that we will look behind the date time moved the date will always be behind it so go up here then we will say again we want to look behind something that one here what do we want to look behind we want to say we want to look behind date and you can see if we do this one here we have it here but let's expand it a bit more to have a forward slash we need to escape it that is done with a backward slash so like this you can see that this one here changed we're now looking behind date and then the forward slash we're here and here as these two match then we'll say time then a space then moved oh sorry like this colon and then a space that's it we only have one match here so this one just tells that we want to look behind it now we can say what do we want to pick up we want to pick up first two digits so forward slash backwards sorry digits then curly brackets two now we have a match of two digits you can also see it over here we have the o1 then we need a forward class so again we'll escape it and then the forward slash like this then we have two more digits so backward slash digits curly brackets two and then like this now escape the forward slash again like this and here we could make it a bit more reliable because we know that we are always in the 20th hundredth century so we can specify this we simply just put in a 20. you can see that we have this one with us as well and then we know we had two digits so what we do here again is backwards letters digits square brackets two because we have two digits and that's it now we have extracted our date isn't that clever let me copy this one in here let me go here and this is the date moved like this so why this is smart is that we can easily very easily extract these data but this only works when we have structured data this is because this email always look the same we always have a case idea we always have state and we always have a date time moved say that these things they changed they moved around they have different formats they have different names and all that it will suddenly be a much harder case but as often you will have these one as forms so that's why we can do it let's go back to uibot instead of creating three matches we can do that let's create a bit more clever solution because often i have cases where i have like 50 columns 50 things that i want to extract out then it's a bit hard to write 50 matches so let's create a data table with each one of these names and the corresponding regex pattern here so i go back to uipath then up here under this build they disable or before that doesn't matter drag in another build data table over here in the output press ctrl k dt regex like this then in the data table delete whatever in it so delete delete delete so we want two columns we want one with the name and then the corresponding regex patent so i do this plus then i'll say name i'll have another column called regex that is just a breckex pattern now i can move these three things in so i have the case idea that is here and the regex pattern to that that is this one then i'll have another one so press enter we have the zip code like this we have it here ctrl c and do it over here again enter we have the date moved like this and we will copy it in again like this so now we have our tree regex pattern and the names up here in this data table we can see it here we could probably give it a better name so we can distinguish between these two this one is our output so i'll do just build data table output it's always nice to be able to see what is one and what is the other one this one can be directx like this so now what we want to do is that we also want to tilt in this output we also want three headers we want three headers with our case idea zip code and date moved as well so click the plus here here we have the case idea like this we have another one with a zip code make sure that the names here are the same as the one you created in your regex pattern below so do all these three things like this so now we have the data that we want to extract out of each email so then click ok so first we will go in our for each again in this if so just below the add data row drag in a for each row because now we will loop through each regex pattern that we just built it up here so drag in the for each row here so we want to loop through each row in the dt regex that's where our regex pattern is and we want to do matches for each one of these patterns so we can use this matches again or you can create a new one let me drag it in here let us drag this one in as well because first we will just write it out to a right line so we can see what's going on so matches we will still look in the mail body that's fine but this is not a hard-coded pattern anymore this one will change it will be the current value of the regex column if you don't remember let me go back so we build this data table let me open it so we loop through each one of these rows and then we take the regex pattern here here and here and apply it to our matches down here isn't that clever so what we can do here is that in the matches let me click the three dots delete this we simply just say row dot item then we'll say look in the rig x column like this and we will have it to string like here so now we are taking this matches we are taking each regex pattern we are outputting to the innumerable matches and simply just make a right line let's see how that is done like this we can go down to the output and here you can see that we have the case idea we have the zip code and the date again the case idea the zip code and the date so far so good but we want them in our excel sheet that's why we built this data table output we want them in each of the corresponding columns we only we already have these three columns right so what we need to do now is that we'll need to drag in an index counter we want to tell uipath what role we want to look in so click the variables and then click create a new variable so we'll say it index like this that is an integer the scope that will just be the sequence as we only have one sequence as it is with integers it always takes the value 0 as default that's fine with us because that's the first row remember it's zero indexed so we now created an integer with a value of zero that's fine so what we want to do outside for each row in the end of this if we want to add 1 to this integer so let's find an assign we do this because we want to add data and we want to know where we are so what we do here is that we'll say it's index and to add one to it we'll just say it index plus one like this now we can have an assign here so instead of writing it out to a right line we just do an assign and we want to add this data to our dt output let's create that so that is the dt output then we will say rows and we could define an integer that's why we have this in index so the first one that is the it index it will be zero and then it will be one two three as we are here so and then we will specify we can open it up here up into three dots here then we want to specify which column we want to add to so that is item then parentheses and where do we want to add this well we want to add it but we all we already know the name that's why we gave it the same name let me just click ok here i know it's not done yet that's why we have the same names you remember we have the same name here as we have column headers here so that's quite clever because we are looping through these rows here the case idea zip code date move we can simply just say when we have extracted this one add it to the case id column in this dt output that's it so go down here to the assign again and click the three dots so we have the item so what do we want to do we want to add them to the row item then we'll say parentheses colon then we'll say name to string then we want the parentheses here that's it so now we are adding to the output data table we are adding to the index number that we want zero one two all the way up till we don't have any more emails and then the item that is the name that we extract that's perfectly fine like this now we just need to add a value and that is just this value down here so let me copy this one in up here like this we can delete this right line as we have no use for it i'll do that now we can run our automation and what we do now is that we add each of the data that we want so let me run the file again we will of course overwrite this excel sheet but that doesn't matter so we go here open up the excel sheet and now you can see that we have in this first row here we have the date we have the subject body but we also got the case idea zip code and a date move if not clever we can even move a little bit down and we will of course also have it for the second email that's very nice let me close down the excel sheet and move back to uipath maybe we should because each time we process these and imagine this one could be a huge inbox with a lot of mails coming in every day we should probably move the processed emails so in the inbox i'll create another folder i'll right click here and then i'll click new folder let's call it processed so each time we have read an email and processed it that is going into our excel sheet i wanted to move from rpa to processed let's do that so here in activities we'll find a move outlook mail messages here and let's talk a bit about where it should be well it should be in the end of this email don't do it inside this for each row so just simply just do it here in the end drag it in that is here what mail message do we have that is just this mail up here so simply just put in mail and that is only because we have this list that we are iterating through so we are using this one up here where do we want to move it we can specify a target folder so let's do that create another variable i'll say str maybe just target folder or processed folder like this and what folder do we want it's still under the inbox so quotation marks inbox backwards class and then processed now we can specify in the folder up here str process folder like this so now we are moving the emails after we process them that quite clever so say that our automation it will run tomorrow it's not reading duplicate emails that's fine let me just do once more as you saw this one this automation it overrides this excel sheet every time we run it we're not interested in that really we want to make a separate excel sheet so this today's extraction it will go into one excel sheet and then another one for tomorrow or whenever we run our automation next time what we can do here is to create a unique number to this title so instead of it's named new microsoft excel worksheet we could call it something unique so go back to uipath the name is specified here in the str excel path so go down to variables and we have it right here so let me go over here and instead of editing in here we can go to the properties and click the three dots like this so what do let me expand this a bit so what do we want to do well we need to call it something else then my new microsoft excel worksheet that is crite on pro so let me delete this why don't we call it processed and then we have an underscore and a unique number the unique number that could be this time of the day but say that we run the automation in the same minute then it will still override so we use seconds and maybe milliseconds we'll often do that that's just a unique number that's perfectly fine so what we do here is that we'll say underscore then quotation marks as we will now add something else to it let me delete this sl xlsx we will of course add it afterwards but it's more easy just to delete it so now we have this this is this this is the start of our path then we can say plus now we will have the date time of now so we'll say now then to string if we don't do anything this one will just come out with year months and in days hours and minutes but we also want seconds milliseconds right so what we can do here is that we'll say parentheses then quotation marks now we can specify the format of this date i want a year so that is four years month mm with big ones small ones are minutes then i want days and i want hours i also want minutes that are small amps i want seconds that's 2s and then i want milliseconds i know this is 3fs because i use this a lot and you can google these that is.net time formats simply just google it and you can see these formats if you want other formats so now we have this added to the process we can say plus and we need the ending that is the quotation marks dot s l s x like this so now each time our automation run we will create a new excel sheet so let me okay this and try to run it we'll also take a look that if we have moved these outlook mail messages so then just click run file like this we go back to our desktop let me drag it down here it's right here that's it we have now created an automation that will not only extract the standard data out but we can also specify what data we want to extract out and we can give the file a unique name as we did here and we can move the emails after we process them oh here we have something to sort that's happening all the time as rpa developers we can see that it moved also the two other emails it's not supposed to do that but it happens all the time don't feel the shame as rpa developers we will make a lot of these i'll say quotation mark mistakes but that's part of your development so go back to uipath what went wrong here well we need to have it in the if because now it's outside the ifs it will look at this one here and it will both take the one that contains the fsfn address change and the one that doesn't so i made this trap so you can see how easily we can get tricked move it up here and no it wasn't a trap it was a clear mistake but a mistake as we often make so let me go back here and in the process i'll just copy these four back to rpa while moving so now we have him here let's see if our automation will run so click run file that's it let us go back to outlook yes we have these two here in the rpa we haven't touched them and we have the two address changes in the process we can go back here and we have another processed file that's very nice with a unique name here that's it for this lesson i hope you learned a lot do you want a uipath job learn the uipath reframework by clicking the video to the left the reframework is used to scale robots in all companies or learn more data extraction with the uipath document understanding package and the video to the right

Channel: Anders Jensen

Views: 5,083

Rating: 5 out of 5

Keywords: uipath, anders jensen, regex, uipath tutorial, uipath regex, regex uipath, robotic process automation, regex in uipath, rpa, uipath how to, uipath tutorial for beginners, outlook uipath, extract mail body in uipath, mail body regex uipath, how to extract emails in uipath, how to extract emails with regex in uipath, extract outlook emails with regex in uipath, uipath tutorials, save outmail emails to excel, save outlook mails to excel, regex in upath

Id: AV59DqhJa38

Length: 39min 9sec (2349 seconds)

Published: Sat Mar 27 2021