Skip to main content

vCalendar with CDO and Script

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


Popular posts from this blog

Testing and Sending email via SMTP using Opportunistic TLS and oAuth in Office365 with PowerShell

As well as EWS and Remote PowerShell (RPS) other mail protocols POP3, IMAP and SMTP have had OAuth authentication enabled in Exchange Online (Official announcement here ). A while ago I created  this script that used Opportunistic TLS to perform a Telnet style test against a SMTP server using SMTP AUTH. Now that oAuth authentication has been enabled in office365 I've updated this script to be able to use oAuth instead of SMTP Auth to test against Office365. I've also included a function to actually send a Message. Token Acquisition  To Send a Mail using oAuth you first need to get an Access token from Azure AD there are plenty of ways of doing this in PowerShell. You could use a library like MSAL or ADAL (just google your favoured method) or use a library less approach which I've included with this script . Whatever way you do this you need to make sure that your application registration  https://docs.microsoft.com/en-us/azure/active-directory/develop/quickstart-register-

How to test SMTP using Opportunistic TLS with Powershell and grab the public certificate a SMTP server is using

Most email services these day employ Opportunistic TLS when trying to send Messages which means that wherever possible the Messages will be encrypted rather then the plain text legacy of SMTP.  This method was defined in RFC 3207 "SMTP Service Extension for Secure SMTP over Transport Layer Security" and  there's a quite a good explanation of Opportunistic TLS on Wikipedia  https://en.wikipedia.org/wiki/Opportunistic_TLS .  This is used for both Server to Server (eg MTA to MTA) and Client to server (Eg a Message client like Outlook which acts as a MSA) the later being generally Authenticated. Basically it allows you to have a normal plain text SMTP conversation that is then upgraded to TLS using the STARTTLS verb. Not all servers will support this verb so if its not supported then a message is just sent as Plain text. TLS relies on PKI certificates and the administrative issue s that come around certificate management like expired certificates which is why I wrote th

The MailboxConcurrency limit and using Batching in the Microsoft Graph API

If your getting an error such as Application is over its MailboxConcurrency limit while using the Microsoft Graph API this post may help you understand why. Background   The Mailbox  concurrency limit when your using the Graph API is 4 as per https://docs.microsoft.com/en-us/graph/throttling#outlook-service-limits . This is evaluated for each app ID and mailbox combination so this means you can have different apps running under the same credentials and the poor behavior of one won't cause the other to be throttled. If you compared that to EWS you could have up to 27 concurrent connections but they are shared across all apps on a first come first served basis. Batching Batching in the Graph API is a way of combining multiple requests into a single HTTP request. Batching in the Exchange Mail API's EWS and MAPI has been around for a long time and its common, for email Apps to process large numbers of smaller items for a variety of reasons.  Batching in the Graph is limited to a m
All sample scripts and source code is provided by for illustrative purposes only. All examples are untested in different environments and therefore, I cannot guarantee or imply reliability, serviceability, or function of these programs.

All code contained herein is provided to you "AS IS" without any warranties of any kind. The implied warranties of non-infringement, merchantability and fitness for a particular purpose are expressly disclaimed.