557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|A Power Automate flow to send Xmas gift emails automatically|
|Tired of doing repetitive tasks at Christmas? This blog shows you how to create a Power Automate Desktop flow to send emails out to your nearest and dearest programmatically, with each email including instructions on how they should use the gifts they've been given.|
Every year it's the same: recipients of my gifts hassle me for instructions on how to use them. This year, fed up with having to email each person individually, I've created a Power Automate Desktop flow to create and send emails programmatically.
The great thing about this program, of course, is that I can use it every year from now on, thus minimising the amount of time I spend communicating with people on Christmas Day.
So I have (don't you?) an Excel spreadsheet containing all of the presents I've bought:
My list of presents (here's hoping Granny Anna doesn't read this before Christmas, as I don't want to spoil her surprise).
I want to send an email to each person:
I'll send the emails to my own account first for testing, then forward them from there.
Here's what a typical email should read like:
Each email will include a link to a Google page listing results for the relevant search term.
So I've created a new flow in Power Automate Desktop:
Because I'm using Windows 11 this program was already installed on my laptop.
Within this I've created my first subflow, to open Excel, read my list of presents into a variable, then close down Excel:
My GetPresents subflow.
This uses these 3 commands:
The Advanced category would allow me to do many more things in Excel, including running VBA macros.
Here's what the command to read the list of presents in from Excel looks like:
It's possible to read all of the cells from a worksheet or a named range, but I've hard-coded my selection to B4:E8 (and ticked a box in the Advanced section - not shown above - to say that my list includes column headers).
I can now test this out by debugging it:
You can step through any program command by command.
You can see that Power Automate creates a variable to hold the rows of data:
For the next part of the program I need to loop over these rows.
For each present, I'm going to have to get the Google web page's URL, so I need to open a browser:
I've gone for Edge, but Power Automate supports Firefox and Chrome too (and, surprisingly, Internet Explorer).
I then need to create a loop over all of the rows of Excel data:
The loop command I've created in a separate subflow called SendEmails. Within the loop I've read the person's name, present description and email address to use into three separate variables.
I now need to mimic these actions:
My flow needs to do these 4 things.
Here are my actions to do this:
I need the Wait command to ensure that Google has run the search before I capture the final URL in a variable.
The critical part of this is how you populate the search box. Here's the start of this step:
You can choose to type text into a UI (User Interface) element.
Power Automate will highlight any parts of the application (or in this case, web page) which are editable.
You can hold down the Ctrl key and click to select any element.
The final thing to do is to send out this person's email within the loop:
This assumes that you've already opened Outlook. The program waits 2 seconds after sending each email.
The action to send the email looks like this:
The email uses all of the variables we've accumulated to fill in the person's name, email address, present and Google instructions URL.
The overall main program should now look something like this:
The main program to run.
Here's what this program should do:
|1||Run the subprogram to open Excel, read the list of presents into a variable and then close down Excel.|
|2||Open Outlook (like everything else, you can see this happening in front of your eyes when you run the program).|
|3||Run the program to send the emails for each person in the list.|
|4||Close down Outlook.|
|5||Display a pop-up message confirming that the program is complete.|
You can now run the program, and sit back and watch!
I took this screen shot as the program was running - it's about to send the third email.
Here's what I got in my Outlook inbox after running this just now:
The amazing results of running this program!
And finally I saw:
The final message box.
I'm sure like me you want to automate the hell out of Christmas. Here are a couple of things to watch out for:
|Changing windows||Experience suggests that you will probably run into problems if you create the flow using a second screen. For an easy life, don't.|
|Pop-ups||When you run a Google search on my laptop, pop-ups appear encouraging me to switch to Chrome. To bypass these I relied on pressing the Tab key to get to the Search button (I could have created an action to click on the relevant button in the webpage, but I had problems getting this to work when a pop-up appeared).|
Here are my next projects:
(*) This last idea is nothing to do with Power Automate, but would represent the pinnacle of automation!
Some other pages relevant to the above blog include:
25 Aytoun Street