When a key user asked me a way to better integrate calendar meetings into the daily lives of their users, I showed the default ways of working with the calendar: Simply connect the calendar to your Outlook and you can open up the items.
But, she replied, what about our portal site? We just have a specific session every few weeks, ment for a specific audience. We don’t want all our 2000 employees having to open the calendar in Outlook.
A quick search online (insert your own bing-google joke here) brought me to the SharePointProMag site where Dan Holme had this amazing suggestion. This post dates back from 2007, but it still works in SharePoint 2013.
The idea is to build a calculated column that calls the “owssvrs.dll”. This file was already present in FrontPage many years ago, the functionalities make up the primary features of SharePoint, namely list creation, deletion, and authoring. It creates a virtual table that stores data and metadata and forms an abstraction on top of the database. With this, it allows the user to manage tabular data in a Web site environment. As part of the SharePoint module, the owssvr.dll file can be used directly from the browser. The user can utilize it programmatically through a client API. And that is exactly what we do here.
So, to start, we need to create the column. Do this via list settings, not via the ribbon directly, because we will need something extra. Add a new column to the list (calendar) and use the Calculated Column type.
Enter the following formula:
Change ‘yoursitename’ with the name of the site you currently are. Change ListID with the ID of the list. You can find that listID in the url of the page where you are now (that is why you need to do this via list settings).
If you copy this link to the browser, you will see the calendar item appear as an Outlook calendar item which you can now directly save.
Now, you can do all kinds of tricks to make the link actually clickable. First thing that comes to mind is to create a html link wrapper around it. We need to do 2 things for that:
- change the “data type returned” from the calculated column to Number (little trick to make calculated columns become clickable links)
- change the formula of the calculated column to:
=”<a href=’http://yoursitename/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=ListID&CacheControl=1&ID=”&ID&”&Using=event.ics’>open in Outlook</a>”
This gives you following result:
Problem we have is that in the displayform (when you open the item itself in SharePoint), the link is very ugly.
This is something that you can do with SharePoint Designer: create a new workflow on the calendar that runs every time you change / add the calendar item.
The workflow has 3 parts:
- Create a new column in SharePoint of type “Hyperlink”
- Open SharePoint Designer (or the workflow tool of your choice)
- Create a new local variable (in the workflow), called “url” that is of type ‘string’
- Add an action “Set Workflow Variable” where you set the new “url” variable to this value:
- http:// yoursitename/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=ListID&CacheControl=1&ID=”&ID&”&Using=event.ics, add to outlook
- Change the “&ID” to the lookup ‘Current item: ID’ (to make sure it uses this item to make a link)
- add an action “Set field in current item” where you set the Hyperlink field from step 1 to your workflow variable ‘url’
- Save and publish the workflow. Create a new item and let the workflow run.
The end result should look like this: