Browse 531 attributed reviews, viewable separately for our classroom and online training
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.

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

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:

List of emails

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:

Typical email

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:

New flow

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:

Subflow for Excel

My GetPresents subflow.

This uses these 3 commands:

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

Excel read command

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:

Debugging programs

You can step through any program command by command.

 

You can see that Power Automate creates a variable to hold the rows of data:

List of presents

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:

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

Looping in PAD

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:

Four things to do

My flow needs to do these 4 things.

Here are my actions to do this:

The flow actions

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:

Populating text box

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.

Clickable element

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:

Sending the email

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:

Sending an email

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:

Main program

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!

Screen shot

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:

Inbox contents

The amazing results of running this program!

And finally I saw:

The final message box

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!

This blog has 0 threads Add post