• Ed G.

Birthday Bot - Part Six - Send SMS and eMail with Power Automate

In part five, we were able to send physical cards up to a week in advance of an upcoming birthday. This allowed us to do that as part of the chatbot conversation about that birthday since that was happening 7 days before the actual birthdate. For SMS and eMails, we want them to go the same day as the birthday, so we staged them in a different SharePoint list in part four...and now we're going to poll that list each day to see if there's any eMails or texts that need to be sent.

While stewing on this, I had what I thought was a pretty cool idea. I'm not certain yet if it's a good idea, but I thought it was pretty cool: Rather than build a separate flow to check each day for birthday messages that are scheduled to go out that day, I'd leverage the same flow that is checking my contacts each day for birthdays coming up seven days from that day. This gives me a chance to experiment with parallel branches, which I haven't spent much time with so far.

Adding a Parallel Branch and Filtering a List


To add the parallel branch, start by clicking the plus sign as we normally do to add an action, but this time I'll select "Add a parallel branch". This will let both sections of my recurring flow to run simultaneously.

From there, we'll build this fairly simple flow just as we would any other.


Next, we'll need to look at all of the birthdays in our schedule that haven't been sent yet, filtering also by items where the SendDate is today. I'll use the SharePoint Get items action.

OData syntax is always a bit of a struggle, so here is a cheat sheet to help. My filter query is looking at the DateSent for null (empty) values, and where the SendDate is equal ('eq') to the UTCNow() time formatted to match my SharePoint list of mm/dd/yyyy. That expression looks like:


formatDateTime(utcNow(),'MM/dd/yyyy')


Note that the month indicator ('MM') has to be capitalized otherwise you will get back the minutes. Also, notice that in my OData filter, the date expression has single quotes on either side. If you get back an error that your expression is invalid, try wrapping your value in those quotes.

Send SMS or eMail With Power Automate


Because we might have multiple birthdays to send on a single day, we'll prepare for that by adding an Apply to Each loop to cycle through each item returned. Inside that loop, we want to check the SendMethod to see if our flow will send an SMS or an eMail.

Because my SendMethod was a dropdown field with two choices, my dynamic values in Power Automate listed both a SendMethod and a SendMethod Value. If you find your condition is traveling down the wrong path, double-check that you've selected the value field if you want to match to the data you can actually see in your SharePoint list.



In this case, if it is NOT "SMS", I'll want to send an eMail. I realized that I didn't have the first name separated in my SharePoint list, so I added that field and then went back to the notification flow to be sure that is copied into my scheduled send list.




Next, I'll update the schedule so that this birthday isn't picked up in the future by adding today's date to the DateSent field.

If it is an SMS birthday wish, I simply use the Twilio action, Send Text Message (SMS) and feed in the details and then follow with a similar Update Item action to update the DateSent field.

Build the Things


We've covered a great deal of ground with this project, and I'm really excited about the possibilities. You can use these lessons and connectors in any order for any purpose to build some very cool things. The first thing I'll do with this is to go back and clean up some of the formatting and I'm certain there will be other things to fix along the way. The platform is very forgiving and allows for the flexibility to come at things from many different angles.


Be sure to check out my latest podcast with Dynamics World below, and I've got a couple of MicroJobs listed on Collab365 to help people through building their own awesomeness!




Don't forget to get in, make mistakes, learn some things, and have fun!


Try. Fail. Learn. Repeat.

©2018 by The Flying Polymath.