Catch Un-dismissed Calendar Reminders Sink         Download Scripts
Updated 05/2004

The following sink is designed to catch any meeting or appointment reminders that are set on a mailbox that haven't been dismissed and then perform some form of action. This action could be to send an email to an SMS gateway or run a script that sends a Instant message to a LCS or a MSN messenger user or use SAPI and TAPI  to call a phone number form active directory and tell the person using speech that they are about to miss an appointment or something else just as inexplicable. The way I've implemented this sink is different from all the other sinks in my column, for this sink I've used an on_timer event. On timer events are a lot like the windows scheduler service (or Scheduled Tasks) they essentially trigger an event at intervals you configure, you can configure a start time, end time and interval (In minutes) that the event will occur at. The way I've used the ontimer event in this article is to fire an event every 10 minutes that checks to see if there are any meeting starting in the next 10 minutes that have a reminder set on. This brings us to reminders (something that is very thinly documented)  there are a few ways you could find appointments that have that reminder set on, the method I've used in this script is to query the /NON_IPM_SUBTREE/reminders search folder (which is what Outlook creates and uses). The reminders search folder is a hidden folder that sits in the Root mailbox folder usually you can't get to it without using a program such as Mdbvw32.exe or OutlookSPY. Because the reminders folder is a search folder its doesn't actually contain any objects just a list of objects that match its search range and for the reminders folder the search range is calendar appointments with the reminder set on. When a person dismisses a calendar appointment it turns the reminder attribute on that appointment object off which will remove it from the reminders search folder list. So the way this sink works is it queries the reminders folder of the mailbox the ontimer event fired on and returns a record-set of appointments that are starting in the next 10 minutes that have the reminder attribute still set on.

The Code

I've used a two step approach to firing this event that adds a layer of abstraction for the Exchange event sinks. The event sink code looks as follows, the script expects that the calsink1.vbs script is located in a d:\scripts directory on the server.

<SCRIPT LANGUAGE="VBScript">

Sub ExStoreEvents_OnTimer(bstrURLItem, iFlags)
set WshShell = CreateObject("WScript.Shell")
strrun = WshShell.run ("d:\scripts\calsink1.vbs " & chr(34) & bstrURLItem & chr(34))
set WshShell = nothing
End Sub

</SCRIPT>

Calsink1.vbs

This is the main block of code that is called by the event sink code, the front end of the code takes the file url of the timer event sink object and then separates the mailbox name of the calling object which is used in the query of the reminders folder.

Set obArgs = WScript.Arguments
towrite = lcase(obArgs.Item(0))
uname = mid(towrite,(instr(towrite,"mbx")+4),(instr(towrite,"calendar") - (instr(towrite,"mbx")+5)))

The next part of the code deals with setting up some time variables to query with, the first part of the code uses that ActiveTimeBias registry entry to work out the GMT offset to convert the query times to UTC, to do the conversion the dateadd function is used. The next part uses dateadd to get two different time variables that are 10 minutes apart. After this the isodateit function is called which converts the date query values into ISO date format which is required to perform a Exoledb query. The isodateit function uses day, month and year functions to split the date into ISO  yyyy-mm-ddThh:mm:ssZ format.

strValueName = "HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
minTimeOffset = shell.regread(strValueName)
toffset = datediff("h",DateAdd("n", minTimeOffset, now()),now())
dtListFrom = DateAdd("n", minTimeOffset, now())
dtListTo = isodateit(DateAdd("n",10,dtListFrom))
dtListFrom = isodateit(dtListFrom)

function isodateit(datetocon)
	strDateTime = year(datetocon) & "-"
	if (Month(datetocon) < 10) then strDateTime = strDateTime & "0"
	strDateTime = strDateTime & Month(datetocon) & "-"
	if (Day(datetocon) < 10) then strDateTime = strDateTime & "0"
	strDateTime = strDateTime & Day(datetocon) & "T" & formatdatetime(datetocon,4) & ":00Z"
	isodateit = strDateTime
end function

 After we've got all the time variables into ISO format then we can get down to the business of querying the reminders folder which looks something like this.

CalendarURL = "file://./backofficestorage/yourdomain.com.au/MBX/" & uname & "/NON_IPM_SUBTREE/reminders"
Conn.Provider = "ExOLEDB.DataSource"
Rec.Open CalendarURL
Set Rs.ActiveConnection = Rec.ActiveConnection
Rs.Source = "SELECT ""DAV:href"", " & _
                  " ""urn:schemas:httpmail:subject"", " & _
                  " ""urn:schemas:calendar:dtstart"", " & _
                  " ""urn:schemas:calendar:dtend"", " & _
		  " ""urn:schemas:calendar:organizer"", " & _
		  " ""urn:schemas:calendar:location"", " & _
		  " ""DAV:contentclass"" " & _
            	  "FROM scope('shallow traversal of """ & CalendarURL & """') " & _
		 "WHERE (""urn:schemas:calendar:dtstart"" >= CAST(""" & dtListFrom & """ as 'dateTime')) " & _
                 "AND (""urn:schemas:calendar:dtstart"" < CAST(""" & dtListTo & """ as 'dateTime'))" & _
		 " AND ""DAV:contentclass"" = 'urn:content-classes:appointment'" & _
		 "ORDER BY ""urn:schemas:calendar:dtstart"" ASC"
         
Rs.Open

You need to change the Calendar URL to reflect your own Exoledb path you can find this out by looking at the M: drive of your server, if you don't have an M: drive your exoledb path should be based on  your primary email domain.

After you have created your record-set the first thing to do is check if its empty if its not then you have a reminder that hasn't been dismissed and you can then perform whatever action you want. In this example I'll send an email to an email address that gets retrieved from querying Active Directory and retrieving the custom attribute_1 of the mailbox where the eventsink fired.. Custom attributes can be configured in Active Directory Users and Computer in the Exchange Advanced Tab.

if not rs.eof then
	on error resume next
	Set objEmail = CreateObject("CDO.Message")
	objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "yourserver"
	objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
	objEmail.from = "calreminders@yourdomain.com.au"
	objEmail.subject = "Reminding you that you have a Appoitnment at " & formatdatetime(dateadd("h",toffset,rs.fields("urn:schemas:calendar:dtstart")),3)
	objEmail.textbody = "The Description of this Appointment is " &  rs.fields("urn:schemas:httpmail:subject") & vbCrLf
	if instr(rs.fields("urn:schemas:calendar:organizer"),">") = "" then 
		objEmail.textbody = objEmail.textbody & "The Organizer of this Appointment is " & replace(left(rs.Fields("urn:schemas:calendar:organizer"),instr(rs.Fields("urn:schemas:calendar:organizer"),"<")-1),chr(34)," ") & vbCrLf
	else
		objEmail.textbody = objEmail.textbody & "The Organizer of this Appointment is " & rs.Fields("urn:schemas:calendar:organizer") & vbCrLf
	end if
	if not (rs.fields("urn:schemas:calendar:location")) = "" then objEmail.textbody = objEmail.textbody & "Location of appointment is " & rs.fields("urn:schemas:calendar:location") & vbCrLf
	DomainName = "yourdomain.com.au"
	Set oRoot = GetObject("LDAP://" & DomainName & "/rootDSE")
	strDefaultNamingContext = oRoot.get("defaultNamingContext")

	CUserID = uname

	GALQueryFilter = "(&(&(&(& (mailnickname=*) (| (&(objectCategory=person)(objectClass=user)(!(homeMDB=*))(!(msExchHomeServerName=*)))(&(objectCategory=person)(objectClass=user)(|(homeMDB=*)(msExchHomeServerName=*))) )))(objectCategory=user)(samAccountName=" & CUserID & ")))"

	strQuery = "<LDAP://" & DomainName & "/" & strDefaultNamingContext & ">;" & GALQueryFilter & ";distinguishedName,displayName,extensionattribute1,msExchHideFromAddressLists;subtree"
	Set oConn = CreateObject("ADODB.Connection") 'Create an ADO Connection
	oConn.Provider = "ADsDSOOBJECT"              ' ADSI OLE-DB provider
	oConn.Open "ADs Provider"

	Set oComm = CreateObject("ADODB.Command") ' Create an ADO Command
	oComm.ActiveConnection = oConn
	oComm.Properties("Page Size") = 1000
	oComm.CommandText = strQuery

	Set rs = oComm.Execute
	objEmail.To = rs.fields("extensionattribute1")
	objEmail.Send
end if

There are a few hard-coded lines in this code you have to change to get it to work in your environment the first is the mail object configuration which you must set to the server you want to send mail though. The next is the sender address you want this mail to appear to come from and the third is the DomainName variable this should be set to your active directory domain name.

That's it if you want to change the action the code performs all you need to do is change the above routine for example if you wanted to send a Instant message via Exchange IM, LCS or MSN messenger you could shell out to another script see my other articles for examples. The other example I've done is using some TAPI and SAPI code that I've explained in my other article is to change the above routine to write out the content of the reminder to a text file and then insert a record into a queuing database. Another piece of code will then read this database and using TAPI call a phone number that is retrieved via a active directory query and then using Speech  tell the person called that they have an appointment reminder. I've included this sink in the download for this article in the CALSAPI.zip file.

Registering this script

Registering a Ontimer event sinks differs from registering a normal sync or async event sink as it requires setting at least 2 of the following 3 eventsink properties. "http://schemas.microsoft.com/exchange/events/TimerInterval",  (Required)
"http://schemas.microsoft.com/exchange/events/TimerStartTime",  (Required)
"http://schemas.microsoft.com/exchange/events/TimerExpiryTime",  (Optional)

Two methods you can use to register event sinks are using exchange explorer or regevent.vbs, with On_timer event sinks your need to be careful of the following

Exchange Explorer

When registering an onTimer eventsink with Exchange explorer make sure you download the latest copy from MSDN (Its Included in the Exchange SDK samples), the start time you enter when registering the sink is in UTC time. Unless you want the event to start at a particular time its safer to set the start date to a few days in the past to avoid any problems with UTC time. To check that the event has registered properly with Exchange explorer have a look at the properties of the event sink object check the TimerstartInterval should be datatype datetime.tz and the property value should be In ISO dateformat and the TimerInterval property should be datatype Interger (Int).

Regevent.vbs

I had a lot of problems initially trying to register ontimer events with regevent.vbs the main problem I ran into was that although the sink registered okay it wouldn't fire. After a lot of false positives (and a few frustrating hours) I eventually found a method that worked consistently over a number of machines. Like Exchange Explorer the date and time that you enter for the start and end time are in UTC time (not local time).  When registering a event sink with the regevent.vbs script it sets the Timerstart , Timerend and TimerInterval datatypes to the string datatype and it also doesn't store the dates in the normal exchange ISO format. The weird thing is this doesn't seem to mater it looks like the ontimer event handles dates in this format. Whether this causes problems over the long term leaving these dates stored in the string datatype format ???.  I created a modified AddNewStoreEvent subroutine for the regevent.vbs script that handles converting the date/time retrieved from the commandline into a proper date format using the cdate function and the interval using the cint function. I've posted the new routine here (changes are in red) all you need to do is cut and paste it into your regevent.vbs file over the existing routine (its up to you whether you want to do this though as it seems to works either way).

Updated 05/2004

Someone recently pointed out a inaccuracy in this article regarding my comments on the On-timer event sink registration so here's a correction

If your using OnTimer event sink with the Exoledb Script host you need to be careful of the following, If you are using the "-file" command line parameter to reference your event sink script in the registration make sure that you don't register your event sink with the extension .eml . (just use no extension the sample in the regevent.vbs file will work fine) if you do use a .eml extension the sink will register by not fire.

What happens when you use the -file reference to do an event sink registration is it copies the code from your event sink script into the eventsink object itself and then it sets the scripturl property of the eventsink registration object to reference itself.

 An alternate method of registering an ontimer eventsink is use the -url command line parameter to reference your VBS script. This means that you need to upload your script into the mailbox somewhere before you can register the event sink to point to the script url. One method I've used is the below code fragment which will create an object in your mail store and upload the code form the d:\scripts\yourscript.vbs script file to that object.

Const adDefaultStream = -1
Set stm = CreateObject("ADODB.Stream")
set Rec = CreateObject("ADODB.Record")
set Rec1 = CreateObject("ADODB.Record")
Set Conn = CreateObject("ADODB.Connection")
mailboxurl = "file://./backofficestorage/yourdomain.com.au/MBX/yourmailbox/calendar/emit5.eml"
Conn.Provider = "ExOLEDB.DataSource"
Rec.Open mailboxurl, ,3,0
Set Stm = Rec.Fields(adDefaultStream).Value
stm.charset = "us-ascii"
stm.loadfromfile "d:\scripts\emit5.vbs"
stm.flush

You can then run the following event sink registration to register an ontimer event that points to the following object you created (runs at an interval of 10 minutes)

cscript regevent.vbs add "Ontimer" ExOLEdb.ScriptEventSink.1 file://./backofficestorage/yourdomain.com.au/mbx/yourmailbox/calendar/calsink  "01/02/2004 01:00:00 AM" 10 -url "file://./backofficestorage/yourdomain.com.au/MBX/yourmailbox/calendar/emit5.eml"

To register using the -file method use

cscript regevent.vbs add "Ontimer" ExOLEdb.ScriptEventSink.1 file://./backofficestorage/yourdomain.com.au/mbx/yourmailbox/calendar/calsink "01/02/2004 01:00:00 AM" 10 -file "d:\emit5.vbs"

Note the Expiry date is optional.

For details on using VBS Event sinks with Exchange see my previous article Using VBS Event Sink scripts with the Web Storage System. Or have a look in the ESDK search for regevent.vbs.   

Download Scripts