You’re offline. This is a read only version of the page.
I am trying to build a flow that would extract birthday dates from an excel file, compare today's date via a condition, send an email to the person celebrating the birthday and also send an email to other people telling them that this person is celebrating their birthday. Also if there is more than one person's birthday on that day it would generate an html table. Please help. Thank you for your time and assistance.
this is the flow 5 days ahead:
- recurrence every day at the time you want the mail to be send
- get items from sharepoint list
- calculate today: formatdatetime(UTCNow(),'dd-MM')
- calculate today+5: formatdatetime(addDays(utcNow(),5),'dd-MM')
- init varpictureURL (optional) as String
- init varBirthdayboy as String
- init varOthersEmail as Array
- init varGetBirthDateFromSP as String
- check if birthday occurs (parallel branch):
for every value in the Sharepoint List:
- compose the birthdate from SP: substring(get items(dayofbirth),0,5) to get it in the same format
--> branch condition birthdayboy: if outputs(today + 5) = outputs(birthdate from SP) you know that in 5 days a birthday is coming up
- if yes: append that name to a string varbirthdayboy
- append pictureURL to varPictureUrl (totally optional)
- append to string varGetBirthdateFromSP: outputs(birthdate from SP) --> you will need this later on
--> branch condition othersemail
- if outputs(today + 5) not equals outputs(birthdate from SP) you know that it's not that persons birthdate
- if yes: append the email address to varOthersEmail
Right now you will have to variables: Varbirthdayboy will contain the name of the birthdayboy and varothersemail will contain all the email addresses of the others. Let's check if it's actually somebody's birthday today (outside the loop):
- if the length(varbirthdayboy) not equal to 0 (it's somebodys birthday)
- yes: compose the TO address of the mail: join(variables('varothersemail'),';') --> because this was an array it will join all the email addresses with a ';' into one string that you can use in your TO field of your mail
- send an email: "our colleague 'varbirthdayboy' has a birthday coming up in 5 days"
TO = output(compose TO address)
subject = in 5 days it's the birthday of "varbirthdayboy"
-if no: terminate the flow because it's nobody's birthday in 5 days
This works for small teams and you need to maintain the sharepoint list (or excel if you like) manually. Joiners and leavers should be updated regularly
This is a copy of the message I send earlier:
I actually split up the process in 2 flows. 1 that will send a warning mail 5 days ahead of your birthday to everyone in your team (sharepoint list with name, email and birthdate) and 1 flow that actual sends a message in teams on the day of your birthday.
This is the flow on the day of your birthday:
- Recurrence every day at the moment you want the message to be sent
- get items from your sharepoint list
- calculate today in format dd-MM
- initialize a string var for Birthdayboy
- check when the birthday occurs:
- for every value in your sharepoint list
- birthdate from SP: substring ('DOB' 0,5) to get the same mm-DD format (we don't care about the year, how old he got)
- get birthday picture: if you like a picture to be send in the message, we had a personalized picture
- condition birthdayboy: if outputs(Today) = outputs(birthday from SP) --> today would be 07/02 and somebody on your sharepoint list would have his birthday today it would also be 07/02 (watch out for the format in your SP List)
- if yes: it's someone's birthday (possibly more) so append the name to a string varbirthdayboy
- post the message to teams in some channel: "To our colleague varbirthdayboy, happy birthday"
- set the varbirthdayboy back to Null, otherwise for the next birthday boy it will still remember the first name. Remember that you are still in a loop here so it will post a message for every one separately if they would have their birthday on the same date.
Hope this makes sense, otherwise hit me back.
btw: I made this a long time ago, now I do this for larger teams using a dynamic way based on Active Directory membership so there is no sharepoint list to be maintained anymore. Especially handy when people leave or join the company/team frequently
can you also share me the example for this flow? I want to build a same flow. thanks!
Hi @AndrewJr
I just messaged you with an example of my flows. I use a sharepoint list instead of excel but the principle is the same.
I also split the process in 2 separate flows, probably it's doable in 1 but it worked for me this way. Let me know if that's what you need.
Just a reminder that excel and sharepoint are good for small teams or groups but it requires manual maintenance if people join or leave the team. Better is to query Active Directory and filter on team and then retrieve the birthdate (if it's stored in AD for your company off course, otherwise you're stuck with a list)
Hi v-bofeng-msft.
Thank you for quick response and SUPERB flow however I am requesting assistance with a flow that:
1. Check birthdates against today's date.
2. Send individual email (s) to today birthday recipient(s).
3. Send individual emails to non-birthday personnel letting them know that today is someone (s) birthday (s) with the name of the birthday recipients(s) in the body of the email.
Hi @AndrewJr ,
I've made a test for your reference:
1\I assume there is a table:
2\My flow
3\Result
Best Regards,
Bof
#1
stampcoin
23
#2
Pstork1
20
Most Valuable Professional
#3
Gprior
19
Share
Report
All responses (
Answers (
