BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Posted by Andy Brown on 18 November 2021
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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:
|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:
|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!