Let’s create a Flow in Power Automate that accomplishes the following:
- Sends an email every Monday morning
- Includes a table of upcoming training opportunities from a SharePoint calendar
- Table has a column with “Add to calendar” download links
1. Create a new flow of type Scheduled – from blank
2. Name the flow and set the schedule to whatever frequency and times are appropriate for your needs.
3. Choose your site and list name. In my case, my calendar isn’t listed so I’ll type its name, Events, after select Enter custom value.
4. To filter to just the events coming up in the next week, we’re going to expand “Show advanced options” and enter filter criteria.
5. If you, like me, are going to filter based on “Start Time” you’ll need to know its internal name which is EventDate. For our filter, we can only use internal field names. To get started, copy and paste the following filter query into your Filter query box:
EventDate gt '' and EventDate lt ''
6. Now click between each set of apostrophe ( ‘) marks and change the dynamic content panel to Expression as seen below.
- First expression: utcNow()
- Second expression: addDays(utcNow(),8)
7. Now add a Create HTML table step. Use the dynamic content panel to set “From” to value from the Get items step (1). Then Show advanced options (2) and set Columns to Custom (3).
8. Under Header, type each column header you want in your table in the order you want it to appear horizontally. For Event I’ve mapped its Value to the “Title” field on my SharePoint calendar by selecting Title from dynamic content. You may also wish to add columns like Location and Category. Get your headers in the order you like, but leave Date and Save/Add to Calendar blank for now.
9. Once you’ve gotten all but Date and Save mapped to a SharePoint calendar field, it’s time to use Expression (on the dynamic content panel) to set up the remaining two. You can copy and paste Date’s formula as long as you’re using Start Time (EventDate) for the date. Save, however, you’ll need to update with your own site collection’s URL (where the calendar lives) and replace the GUID with your own calendar’s GUID. Follow steps 1-2 in this post to get your calendar’s GUID.
- Date: formatDateTime(item()?[‘EventDate’],’ddd MMM d’)
- Save: concat(‘<a href=”https://YourSiteURL/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=%7B1EA8795A%2D3B0D%2D43D7%2DA48E%2DB3CCD4BFE950%7D&CacheControl=1&ID=’,item()?[‘ID’],’&Using=event.ics”>Add to my calendar</a>’)
10. Now add a Compose step. This will make sure our links are clickable in the email (helps decode the HTML so that it parses and renders correctly). We’ll populate this, again, by using an Expression.
replace(replace(replace(replace(replace(body('Create_HTML_table'),'<','<'),'>','>'),'&','&'),'<table>','<table border="3" bgcolor="ffffff">'),'"','"')
11. Lastly, add a Send an email step. I preferred using the html markup option and added an image to my header. You can use either the default rich text or html editor. To insert your table, make sure you insert Outputs (from dynamic content > Compose step).
That’s it! In 11 steps, you’ve scheduled a weekly digest that will email recipients a table of clickable “Add to calendar” links for upcoming opportunities.
To test your flow, rather than wait for the start of your schedule, use “Test.”
To elevate this concept to the next level, you can adjust your HTML filter to only get a certain category and send multiple emails to subscribers of different categories (say, only PowerApps subscribers getting PowerApps courses). You could also create multiple HTML tables and show “SharePoint courses” in one table, “OneNote courses” in the next and so on.
8 Replies to “Send a weekly email of upcoming events with “Add to calendar” .ics download links using Power Automate (Flow)”
Hi Nate, thanks for posting this – it’s extremely helpful! I have managed to get the flow working how I like, however the ‘add to calendar’ expression is not working for me.
My expression is
concat(‘Add to my calendar’)
Am I doing this correctly? I have the GUID and SP site link there correct I believe.
Hi Kyle – your ?ID= appears to be incorrect (or at least how it posted in a comment here). It should be something like:
concat(‘Add to my calendar‘)
I would double-check step 9 and pay close attention to punctuation – sometimes copied and pasted text can include curly quotes, for example, when they need to be straight quotes.
I keep getting a webpage issue with the link. I formatted it like you have it drawn out, but to no prevail. Everything works except the link. Does this strategy work in SP online?
Hi Nate – does this work with modern SharePoint events webpart? When I get to the create HTML table step, I don’t see any other values in Dynamic content other than “value’ and ‘body’.
Hi Jessie, yes it does work for modern events web part. For “list name” in the Get items step, you’ll use ‘Events’ (as a custom entry since you’re not likely to see it as a dropdown selection). Once you use custom entry and type Events, you should then see dynamic content in the following steps.
Hi, Nate – with this method, how are attendees tracked? In other words, how will we know how many people signed up for the event? Thx
Hi Paula, I would recommend creating a Teams webinar if you wish to track sign-ups. Check out https://natechamberlain.com/2021/05/18/how-to-schedule-a-microsoft-teams-webinar/