Sending out Christmas gift instructions in Power Automate
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.
The aim of my flow
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.
Creating a flow
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.
Getting the website address for each present's instructions
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.
Sending the emails
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.
Bringing this all together
The overall main program should now look something like this:
The main program to run.
Here's what this program should do:
|
Action
|
Notes |
|
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. |
Running the program
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.
Problems you may have
I'm sure like me you want to automate the hell out of Christmas.
Here are a couple of things to watch out for:
|
Problem
|
Notes |
|
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). |
Ideas for future flows
Here are my next projects:
- A flow to take a list of Christmas presets and buy them
automatically on Amazon.
- Using some of the AI tools in Power Automate to generate
future present ideas programmatically.
- Creating an avatar or hologram to take my place at the
Christmas dinner table (*).
(*) This last idea is nothing to do with Power Automate, but would represent
the pinnacle of automation!
