In this post I’ll walk through a sample workflow I created for sending out Microsoft Teams messages for a team sync meeting. This is part of my Spring 2022 Fix Hack Learn (FHL) project.

Requirements

• Send individual reminder to teammates (35+) to capture “weekly accomplishments” for next sync meeting
• Schedule to run weekly (Fridays)

Solution

The high-level architecture for the solution I prototyped is as follows:

• Trigger
  • Recurrence – weekly on Fridays
• Actions
  • Create variables for tracking date when flow is run and “next Tuesday”
  • Create copy of template file and rename to desired filename
  • Get list of team members
  • For each team member
    • Send Teams message with link to above file

Below is a sample of the Teams message that is sent.

Challenges

Dynamic links in Teams chat message

When using the HTML designer for the Teams “message”, you are only able to specify static links. In our scenario we need to include a dynamic link to the upcoming document for weekly accomplishments.

Through some investigation online (link) we found that this can be achieved using the Code View. Once in Code View, you can specify an HTML anchor element <a href=…> and include the “link to item” for the file in SharePoint.

Rename files in SharePoint Online document library

Power Automate has a SharePoint action for copying a file, but you have little control over naming the file (ex. replace, new copy with an incremented integer, etc.). Separately, there are currently no native actions for renaming a file.

Instead, I found a quick workaround (link) to copy the contents of an existing “template” file and then create a copy using the contents of the template file. In the process we can provide whichever name we desire.

Calculate date for “next Tuesday”

As part of the renaming of file, I wanted this flow to be able to dynamically calculate the date for the next Tuesday on the calendar. Aside from being an interesting calculation I was personally curious about, this would also allow sending out the reminder on any day of the week.

After much searching online, I found solutions for calculating this in Excel (example), but that made use of a different set of functions that are not available in Power Automate. In the end I decided not to spend too much time on this and instead hard coded the calculation to be Friday’s date + 4 days. See below for the formula.

formatDateTime(addDays(variables('currentDate'),4), 'yyyy-MM-dd')

Conclusion

Hopefully this example can give you inspiration if you need a similar solution for sending out customized reminders of a recurring event or meeting. I was surprised at how quickly I was able to prototype the first iteration of this (less than 1 hour) and then iterate on it with a few enhancements before the day was done. I’m a big fan of how easy Power Automate makes it to automate and simplify manual processes. If you have any suggested improvements, share them in the comments.

-Frog Out

Resources

Flow does not generate clickable links for dynamic paths
https://powerusers.microsoft.com/t5/General-Power-Automate/Flow-does-not-generate-clickable-links-for-dynamic-paths/m-p/789336#M63152

Power Automate Rename File
https://www.enjoysharepoint.com/power-automate-rename-file/

How To Round Date To Previous Or Next Specific Weekday In Excel?
https://www.extendoffice.com/documents/excel/2663-excel-roud-date-to-nearest-sunday.html