1,619 articles and 11,984 comments as of Monday, July 5th, 2010

Wednesday, May 12, 2010

SharePoint to Outlook via Web Services and VBA

Guest Author: James Love
Chronicles of a Chronic E-Junkie

I had one of those moments a short time ago. One of those where someone asks if it’s possible to do something, but doesn’t mention SharePoint whatsoever, yet you can clearly picture a solution using SharePoint. It’s so much nicer when it’s like that, instead of a user who’s seen a Team Site and then scopes his questions to what he’s seen so far.

Anyway, this requirement was to create a place to track meetings. I could have created a SharePoint calendar then asked all the users to connect to it, but in this small company, everyone has access to everyone else’s Calendar, so adding another Calendar increases that overhead, and every time someone books a meeting, they’d have to also remember to add it (and all the same details) to the SharePoint Calendar.

The idea I came up with, was to store data in a custom List in SharePoint, and create a VBA macro that captures Calendar additions automatically, and uploads them straight to the SharePoint List. A "Category" has to be set up within Outlook called "MyCompany Meetings". This is used to detect whether or not we want to add this to the List. We could also use this Category field to forward our meetings to different custom lists for each category. In this example, I just upload to one.

This code also assumes a permanent connection to Exchange. Offline working isn’t taken into account here, but you could create some kind of message queue which these "Additions to SharePoint" are stored when Exchange is Offline (The Outlook "NameSpace" object has a boolean property called "Offline" which you can use to test the connectivity of Outlook before attempting to connect to the SharePoint Web Service). 

This article does not look into that in detail; it is focussed on creating a connection to a SharePoint Web Service in Visual Basic for Applications; specifically from Outlook.

This code uses the List’s GUID and it’s View GUID to add the contents. These can be found by using Stramit SharePoint CAML Viewer.

VBA code:

Option Explicit
Dim myOlApp As Outlook.Application
Public WithEvents myOlItems As Outlook.Items
Private oSoapClient As SoapClient30
Private Const ListView As String = "{9C7F2AC0-05F2-4506-B5C0-1DFF907F3902}"
Private Const ListID As String = "{785F9A6A-2C91-4264-99EB-26ED6379515C}"

Public Sub AddToSharePoint(ByVal Subject As String, ByVal Location As String, ByVal MeetingDate As String, ByVal Details As String)
    Dim BatchXML As String

    BatchXML = "<Batch OnError='continue' ListVersion='1' ViewName='" & ListView & "'>"
    BatchXML = BatchXML & "<Method ID='1' Cmd='New'>"

    BatchXML = BatchXML & "<Field Name='Title'>" & Subject & "</Field>"
    BatchXML = BatchXML & "<Field Name='Location'>" & Location & "</Field>"
    BatchXML = BatchXML & "<Field Name='MeetingDate'>" & MeetingDate & "</Field>"
    BatchXML = BatchXML & "<Field Name='Details'>" & Details & "</Field>"

    BatchXML = BatchXML & "</Method></Batch>"

    Set oSoapClient = New SoapClient30
    Call oSoapClient.MSSoapInit(par_WSDLFile:="http://intranet.mycompany.local/_vti_bin/Lists.asmx?WSDL")

    Call oSoapClient.UpdateListItems(ListID, BatchXML)

    Set oSoapClient = Nothing

End Sub

Private Sub myOlItems_ItemAdd(ByVal Item As Object)
    Dim ThisItem As AppointmentItem
    Set ThisItem = Item
    Dim DateString As String
    DateString = Format(ThisItem.Start, "yyyy-MM-ddTHH:mm:ssZ")

    If ThisItem.Categories = "MyCompany Meeting" Then
        Call AddToSharePoint(ThisItem.Subject, ThisItem.Location, DateString, ThisItem.Body)
    Else
    End If

End Sub

Public Sub Application_Startup()
   Set myOlItems = Outlook.GetNamespace("MAPI").GetDefaultFolder(olFolderCalendar).Items
End Sub

Place this code into "ThisOutlookSession" from the VBA editor (Alt+F11 from Outlook).

Upon Application Start up, the code instantiates the myOlItems variable with a list of all the Calendar Items. This is required, because we create an event handler which triggers when an Item is added to this collection.

When an item is added, the Item passed to the event handler is "casted" to an Appointment Item. We must convert the date into ISO-8601 standard before the SharePoint List’s DateTime field will accept it. We then test the Categories property, if the category has been set properly, we pass the details to a function which does the hard work.

This function creates an XML query populated with the details from the SharePoint List. As you can read from the XML, the List is a simple custom list, with the original "Title" column, "Single line of Text" field called Subject and Location, MeetingDate of type DateTime and finally Details of type "Multiple Lines of Text".

A Soap connection is made to the appropriate site’s Web Service and the UpdateListItems function is called, passing the List GUID and the string containing the XML. We clean up the memory space at the end to keep things tidy.

When you use this in your own code, you’ll obviously have to change the XML in the function to match your List Specification, so start small with a few fields whilst you get the code working, then scale the XML code up one field at a time. Sometimes you may find that some fields start to play up as they might have specific rules when accepting data via the Web Service (Such as the "DateTime" field type).

As you may or may not notice, this code only adds new items to the List. If you update a Calendar Item, the List isn’t updated, likewise if you delete an Item it isn’t gone from the List. You may want to look up the Outlook VBA Object Model for more events you can capture in your VBA code, and also visit the Lists.asmx file on your SharePoint environment for more Web Service functions you can call to extend this functionality.

Guest Author: James Love
Chronicles of a Chronic E-Junkie

James Love works as an Information Officer for a small non-profit organisation in York, UK. Whilst developing solutions for the company’s intranet environment, he also spends time looking after IT operations and strategy. As well as web development & design, James has a keen interest in Information Architecture best practices for the corporate environment. He is a regular attendee of Sharepoint User Group UK events in Northern England.

 

Please Join the Discussion

8 Responses to “SharePoint to Outlook via Web Services and VBA”
  1. Jeff Jones says:

    Woah! That’s too cool. I’ve done some VBA work for MS Access before to have a button with “Add to Calendar” but this is taking it to a whole new level. Well done!

    I wonder how this might look with Office 2010/SharePoint 2010 given the launch events today. Something to think about.

    • James Love says:

      Thanks Jeff! :o)

      Yes, that will be interesting to see what new object model features are available in Office as well as possible new Web Services in SharePoint 2010!

  2. MCSD says:

    Hi,
    Can we use this script (vba script) with infopath – let’s say someone wants to work with infopath screen and then implement the entire functionality from infopath- is it possible>
    Sharepoint 2010 Features
    Sharepoint 2010 Features

  3. Nathan Wells says:

    Good work – I will try this out. I’ve used VB web services to populate a sharepoint lists via excel – it works ok, but its a bit dodgy. I see you called one field MeetingDate as opposed to Meeting Date (with a space between words) – am I right in saying that you encountered a similar issue to me, in that it doesn’t like populating list fields with spaces in the name?

    • James Love says:

      I did that to avoid the possibility that I might need to use XML-formatted column names (ie: Internal Field Names). Those just cause even more headaches than they’re worth – and the Display Names can be set to anything as long as you reference in the Internal Name.

  4. Christophe says:

    James, what happens if several people attend the same meeting? Isn’t this approach going to create multiple instances of the same event in SharePoint?

    • James Love says:

      Yes, the aim of the original requirement was to log each individual’s activities, so multiple entries in SharePoint would be a (in this case, desired) consequence.


Notify me of comments to this article:


Speak and you will be heard.

We check comments hourly.
If you want a pic to show with your comment, go get a gravatar!