I’ve been playing with vCalendar and iCalendar with Exchange to solve a few problems and I found it was pretty interesting so I decided to write about a few things I’ve learned.
Background
Some good background information on vCalendar can be found here
vCalendar and Outlook (good general background on what it is)
IMC Product Development Information (This is the best site for vCalendar info and contains documentation of the current implementation)
IMC ietf-calendar page (Good read on iCalendar)
And last but not least this from the exchange SDK
Basically with Exchange each appointment in your calendar has a vCalendar formatted body part.
Putting it to use.
There are plenty of things you can do with vCalendar formated files the most obvious is to use them to import and export calendar appointments where other methods can’t be used. A simple CDO/ADO script that would go though and export all the appointments in a calendar for the past year (past and future) to separate vcs files goes something like this.
Set Rs = CreateObject("ADODB.Recordset")
set Rec = CreateObject("ADODB.Record")
Set Conn = CreateObject("ADODB.Connection")
set msgobj = CreateObject("CDO.message")
set stm1 = CreateObject("ADODB.stream")
calendarurl = "file://./backofficestorage/yourdoman.com/MBX/user/calendar/"
Conn.Provider = "ExOLEDB.DataSource"
Rec.Open calendarurl, ,3
SSql = "Select ""DAV:href"", ""DAV:displayname"" "
SSql = SSql & " FROM scope('shallow traversal of """ & calendarurl & """') "
SSql = SSql & "WHERE (""urn:schemas:calendar:dtstart"" >= CAST(""2004-01-01T00:00:00Z"" as 'dateTime')) "
SSql = SSql & "AND (""urn:schemas:calendar:dtstart"" < CAST(""2005-01-01T00:00:00Z"" as 'dateTime'))"
SSql = SSql & " AND ""DAV:contentclass"" = 'urn:content-classes:appointment' ORDER BY ""urn:schemas:calendar:dtstart"" ASC"
Rs.CursorLocation = 3 'adUseServer = 2, adUseClient = 3
Rs.CursorType = 3
rs.open SSql, rec.ActiveConnection, 3
if Rs.recordcount <> 0 then
Rs.movefirst
while not rs.eof
msgobj.datasource.open rs.fields("DAV:href")
savefile = "d:\vcsexp\" & replace(lcase(rs.fields("DAV:displayname")),".eml",".vcs")
set stm = msgobj.getstream
mstream = stm.readtext
vstream = mid(mstream,instr(mstream,"BEGIN:VCALENDAR"),(instr(mstream,"END:VCALENDAR")+13)-instr(mstream,"BEGIN:VCALENDAR"))
set stm1 = CreateObject("ADODB.stream")
stm1.open
stm1.type = 2
stm1.Charset = "x-ansi"
stm1.writetext vstream,0
stm1.savetofile savefile
set stm1 = nothing
rs.movenext
wend
end if
Because vCalendar is a defined format parsing this out of the message stream is pretty easy it’s just a case of starting at the BEGIN:VCALENDAR and stopping at the END:VCALENDAR. Once you have the files you should then be able to import them into another program that supports vCalendar (but don’t quote me on this).
Importing a vCalendar (VCS file) into a calendar using CDO
This was a little tricky but this is one method I found that worked, it involves first creating a CDO calendar message adding the vcs file as a body part and then saving the calendar message temporary to the inbox. Then all you need to do is open this new calendar message and process it like a normal appointment request by using the accept method of the icalendarmessage interface and then save it (then delete the original message). I created the following function to do this
function importtocal(fname)
set calmes = createobject("CDO.calendarmessage")
set calmes2 = createobject("CDO.calendarmessage")
set rec = createobject("ADODB.Record")
set stm = createobject("ADODB.Stream")
Randomize ' Initialize random-number generator.
rndval = Int((20000000000 * Rnd) + 1)
set calmes1 = calmes.message
stm.open
stm.type = 2
stm.Charset = "x-ansi"
stm.loadfromfile fname
Set iBp = calmes1.BodyPart.AddBodyPart
ibp.ContentClass = "urn:content-classes:calendarmessage"
ibp.ContentMediaType = "text/calendar"
Set Strm = iBp.GetDecodedContentStream
vstream = stm.readtext
Strm.WriteText vstream
Strm.Flush
tmpfname = "file://./backofficestorage/domain.com/MBX/user/Inbox/" & day(now) & month(now) & year(now) & hour(now) & minute(now) & rndval & ".eml"
calurl = "file://./backofficestorage/domain.com/MBX/user/Calendar/"
calmes.datasource.saveto tmpfname
calmes2.datasource.open tmpfname, ,3
For Index = 1 To calmes2.CalendarParts.Count
Set iCalPart = calmes2.CalendarParts(Index)
Set iAppt = iCalPart.GetUpdatedItem(calurl)
iAppt.accept
iAppt.datasource.savetocontainer calurl
Next
rec.open tmpfname, ,3
rec.deleterecord
end function
So to import all the files created by the export script you could front it with the following code.
on error resume next
set fso = createobject("scripting.filesystemobject")
set f = fso.getfolder("d:\vcsexp")
Set fc = f.Files
For Each file1 in fc
importtocal(file1.path)
next
Parsing the vCalendar format
Another thing that vCalendar information can come in handy for sometimes is to access information about an appointment (or meeting) that you may not be able to get using other properties. One such property is the email address of a meeting resource if you are trying to access it remotely with WebDAV. When you have a meeting that uses a resource the BCC property of the message is used to store the address information about the resource. With WebDAV your access to the recipient’s collection of a appointment is limited and you can only ever see the displayname of your resource. So one potential way to solve this is you can get the appointments stream using a WebDAV GET and then parse the attendees of the meeting and extract the resources email address.
Some things you need to keep in mind when parsing the vCalendar format is individual lines in a vCalendar file are delimited using a CRLF but long lines are split using the RFC 822 “folding” technique. This means if you have an email address that doesn’t quite fit on a line with the other text instead of taking the address to the next line it splits the address and put CR/LF immediately followed by an LWSP-character. (which is a fancy name for a Enter then a space). Have a look at this if your interested
What this means is that if you want to parse the information successfully you need to first unfold the data. I found that just doing a replace on any CRLF + Space combinations did the trick for me. The following sample goes though the attendees of a meeting using WebDAV and returns the email address of the resource account by parsing out the vCalendar information.
set Req = createobject("Microsoft.XMLHTTP")
Req.open "GET", "http://yourserver/exchange/mailbox/Calendar/test-5.EML", false
Req.setRequestHeader "Translate","f"
Req.send
attendeearry = split(req.responsetext,"ATTENDEE;",-1,1)
for i = 1 to ubound(attendeearry)
string1 = vbcrlf & " "
stparse = replace(attendeearry(i),string1,"")
attaddress = mid(stparse,(instr(stparse,"MAILTO:")+7),instr(stparse,chr(13)))
attaddress = mid(attaddress,1,instr(attaddress,vbcrlf))
if instr(stparse,"=RESOURCE") then
wscript.echo attaddress
end if
next
Background
Some good background information on vCalendar can be found here
vCalendar and Outlook (good general background on what it is)
IMC Product Development Information (This is the best site for vCalendar info and contains documentation of the current implementation)
IMC ietf-calendar page (Good read on iCalendar)
And last but not least this from the exchange SDK
Basically with Exchange each appointment in your calendar has a vCalendar formatted body part.
Putting it to use.
There are plenty of things you can do with vCalendar formated files the most obvious is to use them to import and export calendar appointments where other methods can’t be used. A simple CDO/ADO script that would go though and export all the appointments in a calendar for the past year (past and future) to separate vcs files goes something like this.
Set Rs = CreateObject("ADODB.Recordset")
set Rec = CreateObject("ADODB.Record")
Set Conn = CreateObject("ADODB.Connection")
set msgobj = CreateObject("CDO.message")
set stm1 = CreateObject("ADODB.stream")
calendarurl = "file://./backofficestorage/yourdoman.com/MBX/user/calendar/"
Conn.Provider = "ExOLEDB.DataSource"
Rec.Open calendarurl, ,3
SSql = "Select ""DAV:href"", ""DAV:displayname"" "
SSql = SSql & " FROM scope('shallow traversal of """ & calendarurl & """') "
SSql = SSql & "WHERE (""urn:schemas:calendar:dtstart"" >= CAST(""2004-01-01T00:00:00Z"" as 'dateTime')) "
SSql = SSql & "AND (""urn:schemas:calendar:dtstart"" < CAST(""2005-01-01T00:00:00Z"" as 'dateTime'))"
SSql = SSql & " AND ""DAV:contentclass"" = 'urn:content-classes:appointment' ORDER BY ""urn:schemas:calendar:dtstart"" ASC"
Rs.CursorLocation = 3 'adUseServer = 2, adUseClient = 3
Rs.CursorType = 3
rs.open SSql, rec.ActiveConnection, 3
if Rs.recordcount <> 0 then
Rs.movefirst
while not rs.eof
msgobj.datasource.open rs.fields("DAV:href")
savefile = "d:\vcsexp\" & replace(lcase(rs.fields("DAV:displayname")),".eml",".vcs")
set stm = msgobj.getstream
mstream = stm.readtext
vstream = mid(mstream,instr(mstream,"BEGIN:VCALENDAR"),(instr(mstream,"END:VCALENDAR")+13)-instr(mstream,"BEGIN:VCALENDAR"))
set stm1 = CreateObject("ADODB.stream")
stm1.open
stm1.type = 2
stm1.Charset = "x-ansi"
stm1.writetext vstream,0
stm1.savetofile savefile
set stm1 = nothing
rs.movenext
wend
end if
Because vCalendar is a defined format parsing this out of the message stream is pretty easy it’s just a case of starting at the BEGIN:VCALENDAR and stopping at the END:VCALENDAR. Once you have the files you should then be able to import them into another program that supports vCalendar (but don’t quote me on this).
Importing a vCalendar (VCS file) into a calendar using CDO
This was a little tricky but this is one method I found that worked, it involves first creating a CDO calendar message adding the vcs file as a body part and then saving the calendar message temporary to the inbox. Then all you need to do is open this new calendar message and process it like a normal appointment request by using the accept method of the icalendarmessage interface and then save it (then delete the original message). I created the following function to do this
function importtocal(fname)
set calmes = createobject("CDO.calendarmessage")
set calmes2 = createobject("CDO.calendarmessage")
set rec = createobject("ADODB.Record")
set stm = createobject("ADODB.Stream")
Randomize ' Initialize random-number generator.
rndval = Int((20000000000 * Rnd) + 1)
set calmes1 = calmes.message
stm.open
stm.type = 2
stm.Charset = "x-ansi"
stm.loadfromfile fname
Set iBp = calmes1.BodyPart.AddBodyPart
ibp.ContentClass = "urn:content-classes:calendarmessage"
ibp.ContentMediaType = "text/calendar"
Set Strm = iBp.GetDecodedContentStream
vstream = stm.readtext
Strm.WriteText vstream
Strm.Flush
tmpfname = "file://./backofficestorage/domain.com/MBX/user/Inbox/" & day(now) & month(now) & year(now) & hour(now) & minute(now) & rndval & ".eml"
calurl = "file://./backofficestorage/domain.com/MBX/user/Calendar/"
calmes.datasource.saveto tmpfname
calmes2.datasource.open tmpfname, ,3
For Index = 1 To calmes2.CalendarParts.Count
Set iCalPart = calmes2.CalendarParts(Index)
Set iAppt = iCalPart.GetUpdatedItem(calurl)
iAppt.accept
iAppt.datasource.savetocontainer calurl
Next
rec.open tmpfname, ,3
rec.deleterecord
end function
So to import all the files created by the export script you could front it with the following code.
on error resume next
set fso = createobject("scripting.filesystemobject")
set f = fso.getfolder("d:\vcsexp")
Set fc = f.Files
For Each file1 in fc
importtocal(file1.path)
next
Parsing the vCalendar format
Another thing that vCalendar information can come in handy for sometimes is to access information about an appointment (or meeting) that you may not be able to get using other properties. One such property is the email address of a meeting resource if you are trying to access it remotely with WebDAV. When you have a meeting that uses a resource the BCC property of the message is used to store the address information about the resource. With WebDAV your access to the recipient’s collection of a appointment is limited and you can only ever see the displayname of your resource. So one potential way to solve this is you can get the appointments stream using a WebDAV GET and then parse the attendees of the meeting and extract the resources email address.
Some things you need to keep in mind when parsing the vCalendar format is individual lines in a vCalendar file are delimited using a CRLF but long lines are split using the RFC 822 “folding” technique. This means if you have an email address that doesn’t quite fit on a line with the other text instead of taking the address to the next line it splits the address and put CR/LF immediately followed by an LWSP-character. (which is a fancy name for a Enter then a space). Have a look at this if your interested
What this means is that if you want to parse the information successfully you need to first unfold the data. I found that just doing a replace on any CRLF + Space combinations did the trick for me. The following sample goes though the attendees of a meeting using WebDAV and returns the email address of the resource account by parsing out the vCalendar information.
set Req = createobject("Microsoft.XMLHTTP")
Req.open "GET", "http://yourserver/exchange/mailbox/Calendar/test-5.EML", false
Req.setRequestHeader "Translate","f"
Req.send
attendeearry = split(req.responsetext,"ATTENDEE;",-1,1)
for i = 1 to ubound(attendeearry)
string1 = vbcrlf & " "
stparse = replace(attendeearry(i),string1,"")
attaddress = mid(stparse,(instr(stparse,"MAILTO:")+7),instr(stparse,chr(13)))
attaddress = mid(attaddress,1,instr(attaddress,vbcrlf))
if instr(stparse,"=RESOURCE") then
wscript.echo attaddress
end if
next