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.
on
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.
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!
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
I’m not too sure, I myself have never used InfoPath just yet, but feel free to ask on http://www.sharepointoverflow.com to see what others think.
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?
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.
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?
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.
Thanks Jeff for that greate example. It tried working with WebServices and SharePoint-Lists bevore but never got so far. Great!
Hi,
I tried this with Outlook 2007 on Vista, but the SoapClient30 is giving errors;
“User defined type not defined”
Should I install some MS Soap Toolkit to refer SoapClient30 ? If so, how do I refer that in VBA ?
Cheers,
Sunil
Are you using 64 bit OS? if so that is why. SOAP Toolkit is not supported. I am downloading the Windows 7 SDK to try and replace SOAP toolkit 3.0 with WWSAPI which is suppossed to run in 32 and 64 bit.
Will report my findings. Anyone else been able to get around this?
Jamie, I just wanted to say thanks for your helpful comment.
No problem!
It was because of SOAP 3 not working in 64 Bit and I decided to just use XMLHttp. Worked just fine. A lot messier but works great:
Dim BatchXML As String
BatchXML = “”
BatchXML = BatchXML & “”
BatchXML = BatchXML & “” & Subject & “”
BatchXML = BatchXML & “” & Location & “”
BatchXML = BatchXML & “” & MeetingDate & “”
BatchXML = BatchXML & “” & Details & “”
BatchXML = BatchXML & “”
Set objHTTP = CreateObject(”Microsoft.XMLHTTP”)
strEnvelope = “”
strEnvelope = strEnvelope & “”
strEnvelope = strEnvelope & “”
strEnvelope = strEnvelope & “”
strEnvelope = strEnvelope & “” & ListID & “”
strEnvelope = strEnvelope & “” & BatchXML & “”
strEnvelope = strEnvelope & “”
strEnvelope = strEnvelope & “”
Dim URL
URL = “https://yoursharepointsite.com/_vti_bin/Lists.asmx”
With objHTTP
.Open “post”, URL, False
.setRequestHeader “Content-Type”, “text/xml; charset=utf-8″
.setRequestHeader “SOAPAction”, “http://schemas.microsoft.com/sharepoint/soap/UpdateListItems”
.Send strEnvelope
End With
Sorry… my code all got stripped out of that.
Hi,
I could do it by adding a reference (Tools:Reference List) to MSSOAP30.dll which is normally located in /Program Files/Common Files/microsoft shared/office12 folder. Now I can use SoapClient30, but ran into new issues.
Am trying to get an item from a custom list and populate into a custom form in Outlook VBA. And later I need t update this back into CustomList.
So…
With oSoapClient.GetListItems() I get error which indicates me that a XMLNode type parameter is excpected for BatchXML (which is string in this example). But apparently, I couldnt get around how to define XMLNode data type. Still working on this, but any of you could give some leads, appreciate that.
This is the latest version I tried, referring many articlaes/forums;
Set oSoapClient = New SoapClient30
Call oSoapClient.MSSoapInit(par_WSDLFile:=”https://sww03.my.shell.com/_vti_bin/Lists.asmx?WSDL”)
MsgBox “mssoapinit complete”
strReturnXml = oSoapClient.GetListItems(strSPListID, strSPViewId, BatchXML, Nothing, Nothing, Nothing, Nothing)
Here, as per Sharepoint documentations, the ‘BatchXML’ parm is expected in XMLNode data type. I still tried this as a string, but get errors and fails at this last statement.
I tried the below but i dont think I made much progress;
‘Dim xmlDoc As System.XML.XMLDocument
‘Dim ndQuery As XmlNode =
‘ xmlDoc.CreateNode(XmlNodeType.Element, “Query”, “”)
‘Dim ndViewFields As XmlNode =
‘ xmlDoc.CreateNode(XmlNodeType.Element, “ViewFields”, “”)
‘Dim ndQueryOptions As XmlNode =
‘ xmlDoc.CreateNode(XmlNodeType.Element, “QueryOptions”, “”)
‘Dim query As Xmlnode
Any idea; if I can use a String type, and if not, then how to define XMLNode ?
Cheers,
Sunil
This looks great! Do you have any examples of doing the exact opposite of this. I am wanting to get shared date information (paydates – no standard frequency, shareholders meetings etc) and pull it into outlook personal calendar (so it is available on everyones smartphones without needing additional apps) read only and updated from Sharepoint but creat a category (if it doesn;t exist called) “Company Dates” and then populate the appointments classified time as “Available”.
Any thoughts?