Getting something useful from bstrURL

In my last post I managed to get an Exchange Event Script working which dumped out the URL of the item when it changed. All well and good, but I need to get a lot more than just the URL.

I'm dealing with extracting information from a Public Folder containing Journal items. As this isn't one of the 'standard' items dealt with by CDO I had to get a lot of the properties using their MAPI Property Tags. It seems as though a lot of people out there have asked for a standard list of these property tags, the closest list being one over at CDO Live, http://www.cdolive.com/cdo10.htm. However this still doesn't provide all the information I needed to get all of the fields I needed. So much like everyone else has, I installed a tool to exam the items via MAPI and provide me with the relevant Property Tags. Microsoft provide the 'Microsoft Exchange Server MAPI Editor' which can be downloaded from http://www.microsoft.com/downloads/details.aspx?familyid=55FDFFD7-1878-4637-9808-1E21ABB3AE37&displaylang=en

So once downloaded and extracted, I ran the mfcmapi.exe and navigate down to my public folder and then selected an item. This provided a full list of properties with values etc making it easy for me to spot the Property Tags.

The next bit wasn't so straight-forward to arrive at!!! The first thing I needed to do was get the Entry ID for the item from the URL using ExOLEDB, which the next bit of code does:

itemURL = "file://./backofficestorage/ctscomputing.com/Public Folders/Customer Services/Time/FW%3A Exclaimer News - November 2006-382499488.EML"

providerURL = "file://./backofficestorage/ctscomputing.com/Public Folders/"

Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.Provider = "ExOLEDB.DataSource"
Conn.Open providerURL
Rec.Open bstrURL
byteEntryID = Rec.Fields.Item("http://schemas.microsoft.com/mapi/proptag/x0FFF0102")
strEntryID = OctetToHexStr(byteEntryID)
Rec.Close
set rec = nothing
set conn = nothing

The Item URL is the one we got from the event sink script.
The Provider URL is just a reference to the Public Folder Store.
So we open a connection, then open the record, once we have this we get the EntryID which is Property Tag 0FFF0102.
We have to convert this value, the function to do this will be shown later on.

So now that we have the Entry ID of the Item we can use CDO an MAPI to get to that item and extract some more properties:

'Log on via CDO 1.21.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "", "", False, True, 0, True, "CTSNTS01" & vbLf & "Administrator"

'Get the message based on the message entry ID.
Set objpubstore = objSession.InfoStores("Public Folders")
Set objMsg = objSession.GetMessage(strEntryID,objpubStore.id)

VB handled most of the conversion of fields automatically, the only ones I needed to pay attention to were those that stored more than one value, PT_MV_STRING8, which is array based, all I wanted was to join these to store in a simple field.

So for most of the properties the following worked:
WScript.Echo "Subject :" & objMsg.Fields(&H0037001E).Value

And for multi-value fields I simply did the following:
MultiValue=objMsg.Fields(&H819B101E)
WScript.Echo "Contacts :" & Join(MultiValue, ";")

The full script for dumping out the information is as follows:
itemURL = "file://./backofficestorage/ctscomputing.com/Public Folders/Customer Services/Time/FW%3A Exclaimer News - November 2006-382499488.EML"
providerURL = "file://./backofficestorage/ctscomputing.com/Public Folders/"

GetJournalItem itemURL

Sub GetJournalItem(bstrURL)

On Error Resume Next

Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")

Conn.Provider = "ExOLEDB.DataSource"
Conn.Open providerURL
Rec.Open bstrURL

byteEntryID = Rec.Fields.Item("http://schemas.microsoft.com/mapi/proptag/x0FFF0102")
strEntryID = OctetToHexStr(byteEntryID)

Rec.Close

set rec = nothing
set conn = nothing

'Log on via CDO 1.21.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "", "", False, True, 0, True, "CTSNTS01" & vbLf & "Administrator"

'Get the message based on the message entry ID.
Set objpubstore = objSession.InfoStores("Public Folders")
Set objMsg = objSession.GetMessage(strEntryID,objpubStore.id)

WScript.Echo "URLName :" & objMsg.Fields(&H6707001E).Value
WScript.Echo "StoreEntryID :" & objMsg.Fields(&H0FFB0102).Value
WScript.Echo "EntryID :" & objMsg.Fields(&H0FFF0102).Value

WScript.Echo "ProjectTitle :" & objMsg.Fields(&H8308001E).Value
WScript.Echo "Subject :" & objMsg.Fields(&H0037001E).Value
MultiValue=objMsg.Fields(&H819B101E)
WScript.Echo "Contacts :" & Join(MultiValue, ";")
MultiValue=objMsg.Fields(&H813A101E)
WScript.Echo "Company :" & Join(MultiValue, ";")
MultiValue=objMsg.Fields(&H82E1101E)
WScript.Echo "Employees :" & Join(MultiValue, ";")
WScript.Echo "Start :" & objMsg.Fields(&H83100040).Value
WScript.Echo "End :" & objMsg.Fields(&H830F0040).Value
WScript.Echo "Duration :" & objMsg.Fields(&H830B0003).Value
WScript.Echo "RequestID :" & objMsg.Fields(&H82E2001E).Value
WScript.Echo "BillingCategory :" & objMsg.Fields(&H8307001E).Value
WScript.Echo "BillingDuration :" & objMsg.Fields(&H83060003).Value
WScript.Echo "Type :" & objMsg.Fields(&H830C001E).Value
MultiValue=objMsg.Fields(&H80DB101E).Value
WScript.Echo "Categories :" & Join(MultiValue, ";")

WScript.Echo "Creator :" & objMsg.Fields(&H3FF8001E).Value
WScript.Echo "CreationTime :" & objMsg.Fields(&H30070040).Value
WScript.Echo "LastModifier :" & objMsg.Fields(&H3FFA001E).Value
WScript.Echo "LastModifiedTime:" & objMsg.Fields(&H30080040).Value

WScript.Echo "Body :" & objMsg.Fields(&H1000000A).Value

Set objMsg = Nothing

objSession.Logoff
Set objSession = Nothing
End Sub

Function OctetToHexStr(arrbytOctet)
' Function to convert OctetString (byte array) to Hex string.
Dim k
OctetToHexStr = ""
For k = 1 To Lenb(arrbytOctet)
OctetToHexStr = OctetToHexStr & Right("0" & Hex(Ascb(Midb(arrbytOctet, k, 1))), 2)
Next
End Function

So I'm now getting some useful information from the URL provided by the event sink. I've hard-coded the URL in the above script because I'm still playing. So there's no link between the event sink and this script as yet, but that should be easy enough to change!

My next step is to get this stuff in to an SQL DB, but that's for another session.