Sending Emails using Power Automate based on Status and Date Columns in Excel

hi my name's Jeff froess and this is another video on Microsoft 365 based on my book creating business applications with Microsoft 365 So today we're really going to talk about power automate and working with Excel uh but first on the book you can get on Amazon or uh other retailers so I encourage you to take a look so let's let's jump over to Excel and I'll show you kind of what we've got going here so this is a s leile and I've only got different three email addresses I'm going to test with so these emails aren't unique even though I've got some rock and roll people here in uh my name so I've got an ID column as well which we're going to need uh so I can uniquely update this file but the idea is we're going to look at this completed status and then check or if they're incomplete send them an email and then write the date that we did that so that we can compare and not send them say more an email more frequently than than every seven days and then I'm going to go ahead and just for showing off going to write the date in the notes that we sent the email out this email turns out from when I've been playing with this little bit it needs to be in a particular format so the we're going to kind of put a nice format that we're used to here and this going to be kind of a date time zone format here and the the use case that we're really trying to tackle is really two one is just if you had a spreadsheet say of training completions and you just want to let everybody know you know once a week and and keep bugging them until they complete it that would do that uh another one is if you just have a lot of emails to send out and you're run into limits uh say you can only send 500 emails at a time and Outlook and somebody's copy and pasting a whole big list into the BL line copy thing you know instead maybe we can do individual emails with power automate um so one quick thing I've seen this if you're free forming this text if it doesn't match it's a different case spaces at the end you get lots of problems so I I encourage you to go ahead and do a drop down like this so if you haven't done that I like to put them on a different tab so you can see those are my valid values and I'll come in here and say in the Home tab actually it's on the data tab where is that yeah data validation you select list here and then you can pick the choices so that's what I'm doing there the other thing that's important when you're working with Excel and power automate is make sure this is a table so you highlight that area go in there and say format as a table It'll ask you if you have a header row or not and that's how it kind of turns blue and everything there so you need that table if you ever want to look you can go to the name manager and see so there I've got one table it's the name table one so let's go ahead and give it a shot I'll leave this we're going to watch this as we go but let's go over to power automate I'm going to create this as a scheduled flow but we're going to just test it right inside here we'll just say video um send email based on Excel status something like that we'll send it uh weekly I'll just send it every day 10:00 for now and I'll be disabling this shortly all right and you know it gets a little more complicated than you think basically because of variables to do that checking of it so let's just launch into it I got a bunch of variables to put in there so I'm just going to use this initialized variable over and over I've got one I'm just going to say send email that I'm going to set to true or false so it's going to be a Boolean I got another variable we call that bar today whoops the hands in the wrong Keys bar today tix so I like to use the tix function to compare times and so forth so U got to do a little math with them but it's a little better I think so I'm going to use tix and you can see it needs a time date string so I'll use UTC now like that I've done that in some other videos so that will give us the ticks oh let me go in I I always like to just rename these so I can kind of self- commenting so I'm going to take the time today make the video a little longer but certainly better practice and then I can look at the code and see what's going on so I'll do those two and I've got three more function or three more variables this will be um our Tex class notified remember that date notified that's going to be an integer as well we can't set that now so we'll have to set that inside the loop and this one's going to be VAR last notification date and that's going to be a string the reason I do that is it it's actually a little hard to read inside the loop When We're looping through the Excel variables or the the Excel row so it's a little easier to throw that date into its own variable and then use it in the tix function so we'll see that and then I'm going to need to have a number of the DAT so uh in other words how many days between those two ticks because it's kind of a little bit funky math it's a little bit easier to just put that into a variable so I'm just going to call this VAR days between tips copy that and I'm oops copy that make that's going to be a integer as well all right so that gets rid of our variables at least initializing them for now so what we need next is I've got the Excel right here and You' think I'd want to work with tables but it turns out I need to be the row so I'm going to need to list rows present in a table so for this you've got a find it and I didn't actually show you where that Excel file was so let's jump over there I've got a team with a videos channel and I've got that data status data so that's where it is so unfortunately I got a couple one of these name the same but luckily I've figured out which one it is so I come in here luckily I can see that videos and file or Channel and see the name of the file then we know things are going well remember we saw that was table one in there so we've got that and then now what I need to do is I need to Loop so I need to go to apply to each and again I'll kind of usually rename that I'll say Loop through rows we put that in and it it's a little hard to know which which one's it here but it's you want to do the value here and then we're going to do a condition and I'll rename it as well I'll just say uh check status say check the status is incomplete so I come over here and I look at the status is equal Al to incomplete and again we got to watch for spaces I talked about that earlier so if it's not incomplete I don't do anything but if it is incomplete well then I've got some stuff that I need to do okay so the first thing I want to do is I want to just start setting variables so I've got first variable oops there it was remember how I just said it was a little easier to read it out of there so I need that VAR Mass notification date and I just need to read that here date last notified okay and now what I'm going to do is a another condition and I'll rename that as well so I can see say what I'm doing so check if date last notified is null okay so I'll read my variable actually I can really doesn't matter so I'll read this one if it's not equal to blank let's do the no case first so what's going to happen there if it's if it's the first time through then I'm always going to send the email so I'm going to come through and say whoops variable and I'll set for send email to true and normally I would update all these things as well but I don't want to uh take all the time so I'm not going to rename those for now but now if it's not blank then what I need to do is I need to to set you know check the ticks so I'm going to come in here and so I'm going to Now set the the tick last notified and me use that tix function again this is why I set that variable because if I come back to D dyamic content I can get that get the variable but notice how I don't have that date last notified anymore and I can do some funkiness and kind of figure it out but it's actually a lot easier just to get it right here so I'm going to get the VAR last notifi date like that so I can get the ticks and then now that I have that I can do another variable and I can set that days between ticks this I have to go look because I got to get the to do my math I have to go grab it so I just have to grab the number because it's this is like the number of Nan seconds you got to go look online to get this exact thing but the gist of it is that you're doing the div so we're going to divide the number by the number of Nan seconds in a day so we'll get to that but then we're going to do the subtract function and the later day is the now so so we want the VAR to dat tix and you're subtracting the VAR tick SL notified so let me go to the end of the line we'll see if I have it right and I put the that's the number of ticks in a day so we'll test that but that should be right so that will set our two variables so the set of our Tak class notifi and then the number of days and then we're going to do another condition get that's I said this most the complication is actually all in these dates but we'll say if the days between ticks if it's greater than or equal to 7 then we set that variable there was a little different order so we're going to set that VAR send email to true and if it isn't then we'll set it to false all right so all of that is just setting the whether we set it a true or false okay and then we can do another condition if that barend email is true then finally we send an email so we type type Outlook now what I'm going to do first is I'm going to send it to myself see if I see it here my 365 account is there we go that one there now I'm going to this is important from a testing standpoint we're going to go back and we'll send it to the actual people but you want to be sure to send it to yourself first CU otherwise you may have up trouble and you're sending it incorrectly had one of our users do that where they had a space in the file and they were trying to send it only people who weren't completed but since they had a space at the end of the word it send it to everybody so you don't want to do that so anyway say your status is status and say dear and this important from our testing too so we can make sure that we're when we send it to ourselves we knew who it's for you can say your status is that please fix it as AP thanks management not the greatest the in the world remember we've talked in other videos this is a great one where if you have an or box that you're have the send to wrs that you go ahead and send it there so it doesn't come from you personally I'll just kind of play it it will make it a high priority email and then now what we want to do is we want to go ahead and update that row so that where if we run in this every day we're not sending them an email every day maybe we wait a week or something so we're gonna update the row so we got to pick all these same thing pick the file again so we're going to go to the document Library go to the file pick the table and then this is real important this is the part where we needed the ID when I first did this I tried to pick email but that wasn't unique and so it didn't it actually worked but it picked the first one that could find which wasn't the exact right one so we need to tell it it's the ID of the one that we're in and then this one's kind of challenging a little bit so back here when I did this set the ticks where I got to find where that was I think it was here yeah when I did this I should have pointed this out for this to work this needs to be in what they call ISO six 8601 format so the easiest way when we write this is to put it in that format to start with so I had to figure that out already so let me grab that in the clipboard so I have it so let's come back here so we're going to set this and we're going to format oops got the wrong keys on there format date time and we're going to say now UTC now but notice I've got a second parameter that's the string and I need that so that's the year and four digigit dash month day and then the T for time zone the hour and the minute and then Z for Zulu or universal time and then I'm going to go ahead and update the notes and I'll just say sent email on but in this case I don't want that funky time so let's try it again we'll just say format date time oops do it on the expression side again we'll say UTC now but in this case I'll do month whoops month day year which at least in the US is more common for us all right let's try that out so we're again we're fairly safe because we're sending it to ourselves don't have any errors yet so we'll test it what it's kind of cool if I when I run this I'm going to go back to Excel and if it works right we should actually see it right itself as we go it's taking a second so I don't see it yet so let's go back and see if we had any error messages oh we had to go done first so maybe now it's only time as it's running let's try it again I don't see it yet so let's go back and check it now it did say that it worked so what we can do is go here and let's see if it found any so that was true check the date oh I think I didn't do my logic entirely right let's see all right yeah so I can see I kind of messed up my thing a little bit so let's go back and look at it that's why it didn't work so so it came through it's true and then we come in yes if the dat is notified as null it comes over here yes but I should have this bit should be outside so I I put it into the wrong spot so let me go look at the one I was editing it from so let's edit this it's all collapsed here so it's gets a little bit confusing let's do one at a time yeah so this one we're setting the variable but it's this guy here that should have been here I believe yeah so we're check in that variable there so yeah that was a problem so that's why it never the first time through it never sets it so a little bit of a logic here and that's why this didn't update itself so let's try it again so now we can set the first time to the VAR send email is true and then we check if it's true and then send the email so let's save it and test it now let's try it again I'm excited to have this get update the Excel file there we go see how that popped up it's looping through so there we go so we got the three that were incomplete it put the date in that 8601 format populated it and that's today's date October 8th 2023 so let's go back over and look and you see it went through so so far so good and then what should happen let's edit it is now we worry about the concurrency thing let's run it again and what should happen is it shouldn't send any emails let me actually go check my email email cuz I didn't show you that yeah so I got three emails and that's how it worked here Peter it's please fix it ASAP thanks management and so forth I got three of those so that's great I'm going to run it again and we shouldn't see anything in the Excel file but when we come through when this is done we'll go through and we'll see that none of them there we go so if we expand all the way to here make a little bit smaller so we can see it so none of these should send an email there we go so I'm looking down at this Blue Area down here so none of them sent the email so that's great now let's go back and change one of these so we'll send a change to Amy and just to make it easy we'll just assume it's a month ago so we'll just change this to 09 and we'll run it again and what shouldn't happen is just that one should get the email so let's go back and watch it and this should we're watching that sell right there and see if it gets changed to 10 there it was change to 10 and now I got this email your status is in complete dear Amy so so far so good we got one more change to make cuz that was all for testing it so I'm going to edit this make it a little bit bigger so everybody can see it and I'm going to go back into where we sent the email instead of always send it to myself I'll send it to the email that was in the spreadsheet so we'll save it and just to test that we'll get rid of this so now I should get three emails and actually let me I'll adjust these two I let's I want to try to my Gmail as well so I'll make this incomplete as well so now I should get four emails so let's run it make sure I saved test let's watch this there we go oh it's funny it may not have reread the email which is probably why this one one didn't get changed so I probably needed to to refresh it so Ah that's all right so I should get some to this other email address as well so let me refresh on my email there we go and see now notice this has actually gone to that J roads email uh so forth I see I misspelled status as well so we'll fix that and then we'll be done so we'll come in [Music] here change that there we go all right well a little more complicated than we might have thought mainly to the updating the Excel file but hopefully that shows you what you could do and uh a little bit of logic into that Excel so hope this is helpful and I look forward to seeing you next time thanks

Channel: Jeff Rhodes

Views: 10,172

Keywords: Excel, Flow, Microsoft 365, Power Automate

Id: YYOcPrgAAyg

Length: 28min 1sec (1681 seconds)

Published: Sun Oct 08 2023