Power Automate Desktop to send emails via Excel/Outlook

hey hello everyone this is victor mumma from excel moments and this is a video i love so much what is it exactly it's really sending you know emails to multiple recipients from excel using outlook so i typically have done this with vba and i have videos on my channel that show this in this video i want to do it using power automate desktop okay let's get into it let me launch my power ultimate desktop first of all as you can see i've been playing around so i will create a new flow i will call this maybe send to multiple recipients maybe i should apologize at this point that this video may be slightly long but it should be worth your time okay so let's just have an idea of what you know uh the logic of this is going to look like because with power automates desktop and power ultimate you're not really writing codes as they say low code no code okay so let's just get the logic of what we want to do we want to send you the mails to every recipient on this list via outlook so let's assume you just opened your um your computer you know just launched your desktop and what would you do well you probably wouldn't want to open up look you know you want to launch outlook i use launcher because i know what i'm expecting to write um you also want to launch excel excel could be open so you can actually hook up to an existing instance but you may want to open a new instance do what you want to do close down instance and not interfere with you know whatever is going on so you launch excel then you want to uh you know maybe read the data you know from the excel sheet okay and read the data from the excel sheet i'll call it that um what would you then do so once you've gotten that data you can then loop you know you can look from you know row to row okay now as you are looping what will you do you want to send you know the email based on whatever row you are on so send email via outlook um yeah yeah outlook okay and once you are done with that um yeah pretty much that's it you can either you know close the excel instance you can choose to close um the um the outlook instance okay so those are the options you have you know for you so that's it so let's get into power to me desktop and you know let's start to create this flow okay so you have a lot of things you can automate using power automate desktop ideally i should have a video to show you power to me desktop and excel but i think i'll just fuse everything in together just wish me luck uh but let's go together we should be fine so the first thing like i said is we want to launch outlook okay so you would if you look down here you can see that you have outlook you can expand and see you know the couple of things you can do there launch send email close if you open excel you would see a longer list you know and if you expand you know the advanced here then you see a much more longer list okay but once you kind of have an idea of the keywords you can just search here so the first thing i'm going to do is i'm going to launch i'm going to launch outlook so i drag that you know to the canvas as i would want to call it okay so i launch outlook it's going to create a variable that variable becomes you know the name of the outlook instance so whenever you're referencing that variable you it knows your referencing outlook so i will leave the default names for now outlook instance so i'll say save okay so that's it so they have look instance is called outlooking starts okay after that i'm going to launch excel now in launching excel i have the option of you know maybe creating a new instance or trying to you know a different instance opening a blank document or opening a certain document what i'm going to do in this case is rather than launch xlt blank documents i will launch and open the document that has you know the data in it this is just a copy of it this is a copy this is not the actual one but similar yes but this is exactly what we want i mean just a copy of the workbook so i'm going to open you know the actual workbook so i'm going to just browse for that i think i have it under my downloads let me be sure which one it is okay it's this one okay so it's going to open that um you know document when excel launches let's see if there's any other thing we need um under advanced okay find vsa password you know you can include that okay that's fine you know and then you can do save okay so this is going to be called excel instance as you can see the variable name here okay so that's that so now once you've done that the next thing you want to do is you want to read that data that data you have in excel so if you expand the excel here you will see the option of read from excel worksheet so we drag that here now it's tied to of course that excel instance now what do you want to retrieve you're not retrieving the value from a single cell you want to retrieve value from your range of sales okay so click range of cells now is about start column start row so let's say start column we want to start in column one we want to start in row one right there's so many ways to doing this the end column now let's go back to excel just to have an idea n column is column six you could use the alphabet two f and end row is row six okay so let's go back and say what six six some of you should already see a problem with this at this point what's that that's really the fact that this is hard coding you know the rows and column numbers so if the data expands especially you know down the rows then you won't be able to accommodate it and that's not what you want you want to make it as dynamic as possible okay so um let's expand advanced um you could say that oh first line of range contains column names okay so that's an interesting thing you know you can include to say okay yes the first you know um line contains headers and it kind of ignores that you know and it changes how the loops work and what the loop um you know indices or index are you know but it is something interesting to note and you know to play around with so i would save this at this point the the name of this is going to be called excel data that's the name of the variable so excel data is a data table that houses all that data you saw in excel okay so now let's try and fix this you know non-dynamic nature of whatever we've done because what we've just done is we've said from you know row 1 column 1 to column 6 through 6 we want to make this dynamic so we need to be able to know at every point you know what the last row is and what the uh you know the last column is good enough you know they already have that built into you can see here under excel you see get first free column and row from excel so if you drag this here you can see that it's going to create two variables for you first three column first three row automatically right so you don't need to do anything it's going to look at the data and it can figure out that you know the first three the first three not the last used first three here is going to be g which is seven and then first three here is also going to be seven okay so it's seven seven that's what it's gonna get you know that's our first three column my first three row so i will bring this up you know above my read from excel sheet so that in my read from excel sheet i can reference it so when i come here now you have to know that where you see you know end column and end row rather than you know using the hard coded numbers what do i do i will pick the variable okay so what's the variable for um you know the column first three column but don't forget the first three column is one to the right of what you need right so this first three column is seven but what you actually need is six so i'm going to just do what a minus one in there now let's look at um you know the end row the end row is similar to right so i'm going to do first free row you know and i'm going to do what -1 okay so now this way as in it's dynamic so every time it's always going to tie to the right you know columnar row okay so let's do save we're not changing much but that's it so once you have this now set up what we want to do here now is now to loop from row to row so i'm going to add a group in here i'm going to add a loop so loop okay so now it's asking me where do you want to start from so it gets very interesting but let's you know not confuse ourselves too much let's just go with the numbers we have in our head i know that in a sense row one is the header row right now okay so i'm going to pick you know two as i'm starting from row two so when i pick row 2 i know that that's like my own first line of data where am i going to stop it's still the same thing you know i can still use that first three row minus one what you could also do to make it easy for yourself is you could create a variable right you could create a variable maybe called last row and tie it straight to first three row minus one so that that way you just reference you know last row rather than doing first three row minus one but that's you know most of what that is you understand why you are doing that you can flow with that increment by one so what does this mean it may start from row two you're going to go all the way to row six and you're going to be going from row two three four five six so that's the meaning of this and the variable that will be doing that titration here that will be looping is called loop index okay if you a basic programmer you know like excel we like to say i you know for i equals this you know so this is like your i here this is your loop index okay so when it starts off it starts off at 2 and it increments that way so let's do save all right so now within this loop is where we are sending the meals why because when it gets to row 2 you want to send the mail to the recipient on row 2 then you go to row three row one so that's a part of you know sending the email needs to come in within the loop here so you could go to outlook again you know if you expand outlook you will see send email message throughout right so we can put this make sure this is within the loop okay so now this is where it gets a little i'll say tricky or interesting so now the outlook instance you know don't forget that was done at the beginning this is the outlook instance this is when you launch outlook he creates a variable called outlooking style so this is already tied to it the account is you know what you are sending the email with so i can use my maybe email address um now who are you sending it to so let's take a look at the excel sheet so we have an idea what's going on we are sending it to whoever is in column e okay so column e is where the um address the email address of the person we are sending to is located so now it's interesting how do you reference you know a column and a row as in power to my desktop there are a couple of ways i just show you you know how don't forget that the data table that we have in excel there you know we had read that data into you know power to me desktop which is here and we called it excel data right so excel data is that range the entire range but now you're interested in just a column there so let me click this let me pick excel data which is you know the table i want now let me show you something when you say excel data for you to reference if you want to reference a particular row and a particular column you just put them in square brackets so you can do this if you do something like this but i will explain what it means if you say one two this one is the row index this two is the column index what it uses is zero indexing so what it means is that this one here means row two this two here means column three so what you are doing here is nice you're referencing row two and column three within excel data that's the meaning of this so you always have to have that at the back of your mind it's not the regular real numbers it's just like what you are thinking about minus one okay so if you also um you know know the name of the column you may not need to do two now you can just put the name of the column if you know the name of the column is required attendees you can write it just putting it in quotes you see what i've done here so what this means here is is going to reference row 2 you know on the required attempt this column okay so but now let's really think about what we want to do we know we want to reference the required attempt this column because that's where the email address is well now what rule are we interested in so this is where it gets a little interesting don't forget that we are using loop index right we are using loop index to loop and we start from row two okay so now we know that we need to bring in loop index here in some way but there are a few things that you need to note one is that you have already designated row one as a header right so just so that you follow so you've designated this as a header row so it means that technically this is now your row one right this is row one but the index will be what zero because it uses you know as in one less than that okay then what about here here the index is going to be what the index is going to be 1 the index is going to be 2 you know and so on okay so now what does this mean it means that if we look at what as in row 2 of our data you know it is going to have an index of what of zero that's what it means okay so if we want to pull up you know this first name vector we should be using what's index what zero okay so if you kind of follow that so here where you know we use loop index and loop index starts from two it means that for us to reference the first row here we should be doing loop index minus two so we do loop index minus two so loop index minus two you know we start the first index at zero which is the first recipient and it will keep going on that way it's a little interesting and it looks a little you know messy at this point but what you kind of know what you want to do you should be fine okay so this is really it's this is the person we are sending to for the cc let's say we want to copy those who are in the optional attend this column so optional attend this column that people want to copy okay so we will just repeat this same thing but now i'm going to put optional here okay and like i said you could also reference it using the index but don't forget that the index must be you know minus one okay now we don't need anything on bcc let's just say subject i just say invitation to an interview if you've created a variable that has this you can also reference that now the body let's just do something quick so say hello now what i want to put here is i want to put the name of the person to say hello so victor you know so now look at this victor is in column the name is in column one right okay or the name of the column is first name i could use any one of them to reference it so i could say hello if i want to use this same construct i've been using right so instead of this here i will say first name i can call this first name okay so it's like hello victor put a comma then you say you are invited to an interview i can do regards my name now you also have the option of you know putting the body as html if you do that then you need to start using you know as an html tags you know your p paragraphs you know line breaks bold italics i would argue that you know that gives you much more flexibility in making sure that the body looks exactly like you know you want it to look you know but for now in this simple case i will keep it as this okay so this is really it and uh let's see i do save okay so let's kind of follow the logic again we open you know launch our outlook we launch excel so we open the file we want to read from we want to know the boundaries so we want to know the last row last column we read that data then we now start looking from row to row for every row we're going to send the email when we are done with that really we can close you know excel which is closing that instance that we've opened you know so we're not going to close every excel is just that instance i can decide to close outlook but i might just leave out look open for now but i can also do close outlook as you can see here and um that should be it's kind of from a logical stance point it looks okay and it probably let me see ties with kind of what i was trying to set up here you know similar yeah so for the most part uh you you can choose to approach these things differently but just to give you an idea okay so now that we have this done we're going to test it so i'm going to press play and see if i get you know one of the emails or two of the emails yeah i think i've used actual mailbox since that should receive the emails so you will see that it's launching excel is doing something here okay so it's done it's done sending to all of them um then we need to check if you know we've gotten the notifications that's the thing so i'm gonna go normally because i'm using my headphones i can hear the sound when the video drops i haven't heard this sound so i'm i'm worried now like oh okay did they actually drop but let's check um the time is about 2 33 a.m so let's see um this is an old one for sure okay yes so this is it so you can see here it says hello johnson you're invited you know to an interview i'm sure you know if you check the other ones yes you should get you should get some i mean i've been i've been messing with it so yeah some of them may not deliver yeah so some of them may not deliver but yeah i think this one is one yeah this is it so hello not gym you're invited to an interview you know and if you kind of follow um you know the names in the excel sheet so uh yes those are the emails of course because i use some other fictitious emails and that's why you know most of them didn't get delivered so just with um you know with that setup we have here we're able to read data from excel you know send emails to you know everybody on the list and it's you know i'll say scalable so if you add more rows of data to that you know you can it doesn't really matter the flow is still going to work either way because you've made the dynamic in reading the data you know uh from the sheets and knowing where the boundaries really are so this is another interesting way to do it so if you are not really given to you know excel vba writing codes this is kind of doing it without codes but just having some ideas of you know logic loops and how things increment but i think with sufficient practice you know it would all come together so that's what i thought to show this is my first video on power automate well i think you can rate it and say okay if it was a good one victor stop doing power to make videos this is not your thing but i think it was fairly decent to give you you know an idea of how you know this works so if you like the video please hit the like button uh you can also subscribe to the channel excel moment so for now i'm out

Channel: ExcelMoments

Views: 309

Rating: 5 out of 5

Keywords: Power Automate Desktop, Excel, Outlook, Emails

Id: tdZvv6c3_hg

Length: 20min 18sec (1218 seconds)

Published: Wed Sep 15 2021