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 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:

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:

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 (*).