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.

Click to enlarge

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.

Click to enlarge

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)
Click to enlarge

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).

Click to enlarge

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.

Click to enlarge

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’)
Click to enlarge
  • 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>’)
Click to enlarge

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(body('Create_HTML_table'),'&lt;','<'),'&gt;','>'),'&amp;','&'),'<table>','<table border="3" bgcolor="ffffff">'),'"','"')
Cilck to enlarge

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).

Click to enlarge

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.

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

  1. 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.

  2. 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?

  3. 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’.

  4. 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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.