cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

sending email to a list of people except one

I'm trying to send a birthday reminder to my team 5 days before someone's birthday. I can get an email send 5 days before but I'm not sure how I can get the email addresses from the rest of the team, except for the birthday boy. I don't want him to be reminded of his own birthday.

Names, birthdays and email addresses are stored in a sharepoint list.

 

Any ideas are higly welcome

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

@KoenM ,

 

I hope this will help you meet your requirement.

 

NOTE: This flow can be optimized for better performance. Thought of providing you an example so that you can take it as starting point.

 

Created a SharePoint list with following schema: Title is the Name of the person.

P6.JPG

 
 
 
 
 
 
 
 
 
 
Get Items Birthdayboys
Expression: formatDateTime(addDays(utcNow(),5),'MM-dd-yyyy')
 
Get Items Non Birthdayboys
Expression: formatDateTime(addDays(utcNow(),5),'MM-dd-yyyy')

 

p1.JPGP2.JPGP3.JPGP4.JPG

P5.JPG

 

Compose Emails expression:

join(variables('varOthersEmails'),';')
 
Compose Birthday boy names:
concat('<table border=1><tr><td>Birthday Boy</td><td>Birth Day</td></tr>',variables('varBirthdayBoys'), '</table>')
 
OUTPUT EMAIL
 
P7.JPG

 

 

 

 

 

 

 

 

 

 

Regards

Krishna Rachakonda

If this reply helps solving your issue, please mark the issue as Accepted Solution.

View solution in original post

5 REPLIES 5
Dual Super User
Dual Super User

@KoenM ,

 

I hope this will help you meet your requirement.

 

NOTE: This flow can be optimized for better performance. Thought of providing you an example so that you can take it as starting point.

 

Created a SharePoint list with following schema: Title is the Name of the person.

P6.JPG

 
 
 
 
 
 
 
 
 
 
Get Items Birthdayboys
Expression: formatDateTime(addDays(utcNow(),5),'MM-dd-yyyy')
 
Get Items Non Birthdayboys
Expression: formatDateTime(addDays(utcNow(),5),'MM-dd-yyyy')

 

p1.JPGP2.JPGP3.JPGP4.JPG

P5.JPG

 

Compose Emails expression:

join(variables('varOthersEmails'),';')
 
Compose Birthday boy names:
concat('<table border=1><tr><td>Birthday Boy</td><td>Birth Day</td></tr>',variables('varBirthdayBoys'), '</table>')
 
OUTPUT EMAIL
 
P7.JPG

 

 

 

 

 

 

 

 

 

 

Regards

Krishna Rachakonda

If this reply helps solving your issue, please mark the issue as Accepted Solution.

View solution in original post

hi @rsaikrishna , thanks for your swift reply, it gave me a good insight in what I was missing.

 

Couple of things I want to add though:

* you filter the query based on the birthdate in the sharepoint list being equal to todays date +5, but in my opinion that will only be true in the year that you are born. Somehow the year should be trimmed from the equation, checking only on day and month. It tried doing that with a 'substring' function but I only got an error

* your condition screenshots only reveil "length(...)" so I'm guessing you check the length of the variable being not 0?

* I guess I don't need the last 'Compose birthday boy names' because my team is limited to 13 people and none of them are born on the same date so I don't need to concatenate anything. I can use the output from varBirthdayboy in my email body. Correct?

 

Thanks in advance for taking the time to help me!

@KoenM 

 

I am very happy to help you on the requirement. Its mutual learning. I love doing this often.

 

My comments for your questions:

* you filter the query based on the birthdate in the sharepoint list being equal to todays date +5, but in my opinion that will only be true in the year that you are born. Somehow the year should be trimmed from the equation, checking only on day and month. It tried doing that with a 'substring' function but I only got an error

[Krishna]  You are right. Year represents current year. We should set everyone's birth dates to current year with correct Month and Day.  This makes the calculation simple. Also, at the end of my flow execution, I prefer to update the date field to next year without updating Month and Day. By maintaining just month and day requires more logic. We should trim the Year from their birthday when we are sending the email to all the boys other than birthday boy.

 

 

* your condition screenshots only reveil "length(...)" so I'm guessing you check the length of the variable being not 0?

[Krishna] I used two Get Items - One to get all birthday boys. Second one is for rest of the boys. For each Get Items, I am check if the collection length or count returned by Get Items is zero or not. If not zero, means Get Items returns some values. 

 

* I guess I don't need the last 'Compose birthday boy names' because my team is limited to 13 people and none of them are born on the same date so I don't need to concatenate anything. I can use the output from varBirthdayboy in my email body. Correct?

[Krishna] "Compose birthday boy names" - I am collecting the names of the birthday boy names. I am including their names in the email which is sent to other boys. So that, they will know whose birthday is coming up.

 

My flow will give you starting logic to work on but I prefer to enhance it with following:

a. When there are no birthday boys, no email should go out.

b. When any exception occurs in the flow at different levels, we should handle it.

c. Scheduling the flow to run daily.

 

Please feel free to let me know if you have any questions.

 

Regards

Krishna Rachakonda

If my reply helps resolving your issue, please mark my reply as Accepted Solution.

@rsaikrishna  thanks for your comments, they all make very much sense.

 

I got my flow working now thanks to your suggestions about the variables. I didn't get the right filter working on my "get items" to trim the year from the birthday so I turned it around, but it made things a bit more complicater...

Your suggestion to change the year of the birthday to current year and update it after each birthday would have made it easier but for now I'm going to keep it as follows:

 

What I do now:

* flow runs every day at 9am

* get all the items from the list

* calculate todays date and todays date+5 days in the format 'dd-MM'

* initialize variables 'birthdayboy' and 'othersemail'

* loop through all items and transform each birthdate to format 'dd-MM'

* create a parallel flow:

* condition to check if todays date +5 matches birthdate and a condition to check if it doesn't matches

* if yes to first condition --> add name to varbirthdayboy

* if yes to second condition --> add email to varOthersEmail

(every entry in my sharepoint list should be added to one of the two variables)

* create a third condition to check if length of varbirthdayboy not is 0

* if not, it means that someone's birthday is coming up and then compose the email

* if varbirthdayboy = 0 --> terminate

 

With this flow I complete your 3 enhancements items 😉

Maybe it's not the most performant way of handling things, but I'm quite new at PowerAutomate and I'm still learning... Any suggestions are still welcome

@KoenM 

 

Thank you for accepting my reply as Accepted Solution.

 

Performance is always key and it depends how often our flow runs and how big the data set we are querying. If the data set is very small. I will ignore performance for time being keeping the urgency of the deliverable. 

 

Here are few resources for Flow learning:

https://flow.microsoft.com/en-us/blog/microsoft-flow-learning-resources-materials/

https://docs.microsoft.com/en-us/learn/modules/get-started-flows/

 

Regards

Krishna Rachakonda

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (17,624)