Send a weekly email of upcoming events with “Add to calendar” .ics download links using Power Automate (Flow)

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'),'&lt;','<'),'&gt;','>'),'&amp;','&'),'<table>','<table border="3" bgcolor="ffffff">'),'&quot;','"')

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.