Send Meeting Invites to SharePoint Online Calendar

Final Calendar Flow

Sometime ago, I wrote an article about How to enable Incoming Mails in SharePoint Online. In that article, I talked about how can we use Microsoft Flow to achieve the functionality. But there is another common functionality available in SharePoint on-premise versions which allows us to send meeting invites to SharePoint calendars. This functionality has been widely used to make a SharePoint calendar as Leave Tracker or to track important events. Let’s take a look at how this can be achieved in SharePoint Online.

The Problem

First thing that may come in mind – SharePoint Calendar is also “technically” a list only. So, why not use the steps described in the article above and create an Item in the Calendar when an email is received.

Well, here is the problem. As you can see the Calendar type list has additional mandatory fields “Start Time” and “End Time”. These will be visible the moment you select a Calendar list in the Create Item action. Since, these information are NOT available in the mail, we can’t use the same solution.

Calendar

But this doesn’t mean that we can’t find an alternative 🙂

Getting Started

If you have read the article How to enable Incoming Mails in SharePoint Online, you can easily figure out what all we would need.

  1. A Shared Mailbox
  2. A Password associated with that Mailbox account
  3. A Flow created in Microsoft Flow

For #1 and #2 above, just go through the article mentioned above. It has all the details under “Create a Shared Mailbox” and “Reset Password” sections.

For this article, I have created a Shared Mailbox “MailToSPCalendar” and set a password.

Shared Mailbox

I will focus on what needs to be different in the Flow to make it work with SharePoint Calendars.

Create Flow

Now that we have the required mailbox and it’s password in place, lets go ahead and create our flow.

  • Go to Microsoft Flow and login with your Office 365 account. This account need not be the same mailbox account.
  • Click on My flows and then on Create from blank

Create Blank Flow

  • On the next page click on “Search hundreds of connectors and triggers”

Search Trigger

  • On the Next screen, search for “Event” and select “Office 365 Outlook – When a new event is created (V2)” as the trigger

Select Trigger

At this time our Flow gets created, but it’s empty, and we now need to configure it for our purpose.

Trigger Created

Configure Flow

Now that we have our flow created, we can configure it to read the meeting invites received in the mailbox that we created and create the SharePoint Calendar entry in the Calendar of our choice.

Add Connection to Mailbox

We first need to configure the source which will trigger the flow to start. This mean, when any new meeting invite is received this flow should get triggered. For this we want to monitor the shared mailbox created earlier. So, we need to add that account under my connections. Click on the three dots (…) and click on Add new connection.

Create New Connection

It will pop-up a credential login window. Login with the email ID of the shared mailbox and password created earlier.

You must login with the email ID and password of the Shared Mailbox. i.e. the Mail ID to which the meeting invites will be sent in order to get those added to SharePoint Calendar.

Once logged in successfully, you can see that added under My connections

New Connection

Configure Source Location

Once the connection is added, we need to configure which location to read from the mailbox in the incoming mails. So, click on the icon next to the text box  of “Calendar id” and select “Calendar” from the drop down menu.

Select Calendar

At this stage, our flow is configured to trigger when any meeting invite is received in our Shared Mailbox.

Let’s go ahead and configure rest of the flow to ensure the invite gets added as a valid entry in a SharePoint Calendar.

Convert HTML to Text

This may seem like an unnecessary step, but this is required to fetch the body of the invite from the mail.

  • Click on the “+” icon in the arrow and Select “Add an action”.
  • Search for the action “HTML to Text”
  • Click on “Content Conversion – Html to text”

Add HTML to Text Action

  • Once the action is added, select “Body” from under Dynamic contents. This step will convert the Body of the invite from HTML to text, which we’ll later use to add to our SharePoint Calendar.

Select Body In HTML to Text Action

Create Calendar Entry

Now that we have all the inputs in place, lets pass those to a SharePoint Calendar.

  • Add an action “SharePoint – Create item”
  • If there is no valid connection available for your SharePoint site, Add a connection with the credentials which has required permission to add an item in the SharePoint Calendar.
  • Select the Site URL under “Site Address”
  • Select or Type in the Calendar name which exists in that site

I have created a SharePoint Calendar named “Holidays” for this article.

  • Under Title, select “Subject” from the Dynamic content. This means the subject of the meeting invite will become the title of the SharePoint Calendar

SharePoint maintains Date and Time entries in UTC, so we need to convert the incoming Start and End date and time to UTC before assigning to corresponding fields in SharePoint Calendar.

  • In the Start Time field, you can the formula from under Date and Time functions or just use this readymade one – Type this under expressions and click OK
    convertTimeZone(triggerBody()?[‘Start’],triggerBody()?[‘TimeZone’],’UTC’)
  • Similarly in the End Time field, you can use this one – Type this under expressions and click OK
    convertTimeZone(triggerBody()?[‘End’],triggerBody()?[‘TimeZone’],’UTC’)
  • Under Event Type, select “Number of occurrences” from Dynamic contents
  • In the Location field, select “Location” from Dynamic contents
  • In the Description field, select “The plain text content” from Dynamic contents under Html to text section
  • In the Category Value field, select any desired value, I selected Holiday in this case
  • And finally, In Content Type ID field, select Event from the drop down
  • Leave all other optional fields empty

This is how our filled action looks like

Create SP Calendar Item

Does it work?

At this stage, our Flow seems complete. We received the input from mail and created a Calendar entry in SharePoint site based on that. But if you try to send a Full day event from your outlook like this, you would see that even though Flow creates an entry in the selected SharePoint Calendar, the date & time shown are NOT exactly a full day.

Full Day Event

After the flow is run, you would see something like this in the SharePoint Calendar – Spanning more than a day and not exactly starting at 00:00 hours!

Full Day Event

This is because, while creating the calendar item, currently there is no field that can be updated to mark it as an All Day Event.

So, we need to do a little bit more to handle this scenario.

Full Day Events

Full day events need to be handled differently than other entries with specific start and end date & time. To take care of this we’ll add an If Condition to check what type of meeting invite has been received.

  • Add an If Condition and rename it to “Check if it is FullDay Event of not”
  • Select “Is all day event?” from under Dynamic content
  • Click on the textbox “Choose a value” and under Expression, type “true” and click OK button.

Check If All Day Event

As expected, this if condition will return yes if the incoming invite is a full day event and no, if otherwise.

Mark Invites as Full Day Event

Let’s first take a look at the “No” condition, i.e. if the incoming invite in NOT a full day event. Actually, for No condition, we don’t need to do anything. Since the calendar item has already been created based on specified time. So, we only need to handle Yes condition.

So, what we do? We call a SharePoint REST service to update the value of the field AllDayEvent to true.

  • Inside the Yes condition, add an action “Send an HTTP request to SharePoint”

Send Http Request To SP

  • Once the Action gets added, Select the Site URL in the Site Address field
  • In Method, select POST
  • In Uri field, type the REST service url “_api/web/lists/getbytitle(‘Holidays’)/GetItemById(<Select ID field from Dynamic Contents under When a new event is created (V2)>)”
  • In the Headers field, toggle to Text mode

Toggle To Text Mode

  • Paste the following entries in the Headers field including the starting and ending braces
{
  "Content-Type": "application/json;odata=verbose",
  "IF-MATCH": "*",
  "X-HTTP-Method": "MERGE",
  "Accept": "application/json;odata=verbose"
}
  • And last update the Body of the action including starting and ending braces
{
"__metadata": {
"type" : "SP.Data.HolidaysListItem"},
"fAllDayEvent" : "True",
"EventDate" :"@{formatDateTime(triggerBody()?['Start'],'M/d/yyyy')}",
"EndDate" :"@{formatDateTime(subtractFromTime(triggerBody()?['End'],1,'Minute'),'M/d/yyyy')}"
}

Note a few points about the body section:

  • The type needs to match the title of the Calendar. i.e. in this case since our Calendar title is Holidays that section reads “type” : “SP.Data.HolidaysListItem”. For an OOB SharePoint Calendar, it would be “type” : “SP.Data.CalendarListItem”
  • All Day Event field is defined as “fAllDayEvent” : “True”, it’s not a typo
  • And most interesting one, we need to subtract at least 1 second from the End Date to ensure the full day events date remains within a day. Why this is required? Because by default, Full Day event shows dates spanning 2 calendar days.

Full Day Event Two Days

See all this work

We are all done now. Save the flow and make sure it’s enabled.

Send a meeting invite to your shared mailbox configured with the flow and after sometime, you could see the invite gets added to the SharePoint Calendar.

Full Day Event

And this is how our Final version of Flow looks like

Final Calendar Flow

 

And that’s it for today. Hope this helps.

Enjoy,
Anupam

You may also like

Leave a Reply

Your email address will not be published. Required fields are marked *

seventeen − 2 =