Publish a Meeting Room Calender as Web page script    Download Script

 The following script was designed to produce a Web page for a resource such as a meeting room mailbox that will , show its status (busy or available) and show all of the days scheduled meetings. Here's is a sample of the page it will produce. This script uses ADO in conjunction with exoledb and some simple file system commands to create a HTML output file

How it works

As I've mentioned above this script uses Exoledb and ADO so it must be run on a Exchange 2000 server for more information on  Exoledb please see my other article SQL for Exchange 2000 the Bluffers Guide.  What this script does is query a meeting room calendar via  ADO and retrieves all the calendar appointments between now and the end of the current day. When dealing with calendar appointments there are a few things one must do, All calendar appointments are stored in UTC time format so you must first convert the time you wish to query within into UTC time (correlates with GMT time in my sample I'm using Sydney time which is +10 UTC which is CdoTimeZoneId 18). Once you have converted the time to UTC you must convert it to the ISO time format to be able to use it in a SQL query and then the last thing you need to do is CAST this into the datetime datatype in the query. For a better explanation of working with time and dates in SQL and Exoledb see my other article.

ConvertUTC function

This function is an VBS adaptation of an Exchange SDK sample function called getdate it takes care of converting a time and date from one time zone to another by using a dummy CDO appointment and then modifying the time zone attribute of this appointment which will then intern update the time you have set the appointment to. The parameters that are sent to this function are 1, the time and date to be converted 2, the source CdoTimeZoneId, 3 the destination CdoTimeZoneId. In my example to convert the current time in Sydney to UTC time I would send ConvetUTC(now(),18,0). For CdoTimeZoneId's please refer to the Exchange SDK

The first part of this code is dedicated to getting today's current time and date into ISO format, in VBS there is no equivalent to the format command in VB so to get a proper ISO format the date and time must be broken up into its parts and made sure its in double digit format. To get the end range of the query 23:59:59 is used which signifies the end of a day.

set WshShell = CreateObject("WScript.Shell")
rem on error resume next
Set Rs = CreateObject("ADODB.Recordset")
Set fso = CreateObject("Scripting.FileSystemObject")
now1 = convertUTC(now(), 18, 0)
now2 = convertUTC(date & " " & timevalue("23:59:59"), 18, 0)
tyear = year(now1)
etyear = year(now2)
tmonth = month(now1)
etmonth = month(now2)
if tmonth < 10 then
	tmonth = 0 & tmonth
end if
if etmonth < 10 then
	etmonth = 0 & etmonth
end if
stday = day(now1)
etday = day(now2)
if stday < 10 then
	stday = 0 & stday
end if
if etday < 10 then
	etday = 0 & etday
end if
sttime = formatdatetime(now1,4)
ettime = formatdatetime(now2,4)
qdatest = tyear & "-" & tmonth & "-" & stday & "T"
qdateed = etyear & "-" & etmonth & "-" & etday & "T"
qdatest1 = qdatest & sttime & ":" & "00Z"
qdatesed = qdateed & ettime & ":" & "00Z" 

 This next section handles the ADO query of the calendar, this query is an example of a ADO expansion query which is necessary when you query a Exchange calendar that may contain reoccurring appointments. For the reoccurring appointments to be expanded you must specify a start  and or end date in the where clause of your query. This query is looking for any appointments that are ending between now and the end of the day.

set Rec = CreateObject("ADODB.Record")
Set Conn = CreateObject("ADODB.Connection")
CalendarURL = "file://./backofficestorage/your.domain.com/MBX/mailboxalais/calendar/"
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"", " & _
		  " ""DAV:contentclass"" " & _
            	  "FROM scope('shallow traversal of """ & CalendarURL & """') " & _
		 "WHERE (""urn:schemas:calendar:dtend"" >= CAST(""" & qdatest1 & """ as 'dateTime')) " & _
                 "AND (""urn:schemas:calendar:dtend"" <= CAST(""" & qdatesed & """ as 'dateTime'))" & _
		 " AND ""DAV:contentclass"" = 'urn:content-classes:appointment'" & _
		 "ORDER BY ""urn:schemas:calendar:dtstart"" ASC"
Rs.Open

This next section of code handles building the body Htm files. The way I've gone about doing this is a little bit different from the norm. The first part of the code builds a file that indicates if a meeting room is currently in use and if so who is using it.. It does this by seeing if any records in the current record set have a start date and time that is before the current time. If so it flags a variable that is used later in this script to indicate that the meeting room is busy. Once the availability is worked out the rest of the script goes through any remaining records in the current recordset that correspond to any meetings that have been scheduled during that day. Again the ConvertUTC function is used but this time in reverse because the data that is returned from the query is in UTC time, so it must be converted back to the time zone you wish to present it to the user in. In  my example the time returned is converted back to Sydney time to do this  you need to reverse the last two parameters when calling the ConvertUTC function eg convertUTC(timedate,0,18).

if RS.Recordcount <> 0 then
Rs.movefirst
while not rs.eof
	if convertUTC(rs.Fields("urn:schemas:calendar:dtstart").value, 0, 18) <= now() then
		if fopen <> 1 then
			mbusy = 1
			set wfile =  fso.opentextfile("d:\scripts\busy.htm",2,true)
			wfile.write "<tr>" & vbCrLf
   			wfile.write "<td width=""35%""><font color=""#800080"">" & rs.Fields("urn:schemas:httpmail:subject").value & "</td>" & vbCrLf
 			wfile.write "<td width=""16%""><font color=""#800080"">" & FormatDateTime(convertUTC(rs.Fields("urn:schemas:calendar:dtstart").value, 0, 18), 3) & "</td>" & vbCrLf
 			wfile.write "<td width=""16%""><font color=""#800080"">" & FormatDateTime(convertUTC(rs.Fields("urn:schemas:calendar:dtend").value, 0, 18), 3) & "</td>" & vbCrLf
			wfile.write "<td width=""33%""><font color=""#800080"">" & replace(rs.Fields("urn:schemas:calendar:organizer").value,chr(34)," ") & "</td>" & vbCrLf
  			wfile.write "</tr>" & vbCrLf	
			wfile.write "</table>" & vbCrLf	  
  			wfile.close
		end if		
	else
		if fopen <> 1 then
			set wfile =  fso.opentextfile("d:\scripts\todayM2.htm",2,true)
			fopen = 1
		end if 
		wfile.write "<tr>" & vbCrLf
   		wfile.write "<td width=""35%""><font color=""#800080"">" & rs.Fields("urn:schemas:httpmail:subject").value & "</td>" & vbCrLf
 		wfile.write "<td width=""16%""><font color=""#800080"">" & FormatDateTime(convertUTC(rs.Fields("urn:schemas:calendar:dtstart").value, 0, 18), 3) & "</td>" & vbCrLf
 		wfile.write "<td width=""16%""><font color=""#800080"">" & FormatDateTime(convertUTC(rs.Fields("urn:schemas:calendar:dtend").value, 0, 18), 3) & "</td>" & vbCrLf
		wfile.write "<td width=""33%""><font color=""#800080"">" & replace(rs.Fields("urn:schemas:calendar:organizer").value,chr(34)," ") & "</td>" & vbCrLf
  		wfile.write "</tr>" & vbCrLf	
		end if
	rs.movenext
wend
end if
rs.close
if fopen = 1 then
	wfile.write "</table>" & vbCrLf	 
	wfile.write "<p>&nbsp;</p>The page will automatically refresh every 30 Seconds Last updated on" & now() & vbCrLf
	wfile.write "</body>" & vbCrLf
	wfile.write "</html>" & vbCrLf
	wfile.close 	 	 
else
	set wfile =  fso.opentextfile("d:\scripts\todayM2.htm",2,true)
	wfile.write "</table>" & vbCrLf	 
	wfile.write "<p>&nbsp;</p>The page will automatically refresh every 30 Seconds Last updated on" & now() & vbCrLf
	wfile.write "</body>" & vbCrLf
	wfile.write "</html>" & vbCrLf
	wfile.close 
end if

Finally the last section of code depending weather the meeting room is busy or available copies the generated files together with template files to create the output html file using a DOS copy. So basically to create the final output file several template files are copied together with files generated by the code. Depending on wether the meeting room is busy or available a different header htm file is used.

if mbusy <> 1 then
	strrun = WshShell.run ("cmd.exe /C copy d:\scripts\mheada.htm + d:\scripts\mmetbod.htm + d:\scripts\todaym2.htm D:\scripts\mmet2.htm",1,TRUE)
else
	strrun = WshShell.run ("cmd.exe /C copy d:\scripts\mheadb.htm + d:\scripts\busy.htm + d:\scripts\mmetbod.htm + d:\scripts\todaym2.htm D:\scripts\mmet2.htm",1,TRUE)
end if


function convertUTC(dtconv, tzfr, tzTo)
	Set tapptobj = CreateObject("CDO.Appointment")
	Set tapptconf = CreateObject("CDO.Configuration")
	tapptobj.Configuration = tapptconf
	tapptconf.Fields("urn:schemas:calendar:timezoneid").Value = tzfr
	tapptconf.Fields.Update
	tapptobj.StartTime = dtconv
	tapptconf.Fields("urn:schemas:calendar:timezoneid").Value = tzTo
	tapptconf.Fields.Update
	convertutc = tapptobj.StartTime
end function

Using this Code

Before you can use this code you need to first change the parameters of the code to suit your mail system and file paths.

Change CalendarURL = "file://./backofficestorage/yourdomain.com/MBX/meetingroom/calendar/" to your own domain name if your not sure what it is have a look at the M: drive on your server and change d:\scripts to your own path where you have placed the HTM template files

Template files
In the script download there is another zip file called templatehtms these are the Html template files that are used in the copy command explained in the last section of the code. You need to unzip these files and place them in the path that you have defined in the code (eg d:\scripts). If you want you can modify the Htm files yourself you should however be careful to have a look at the format used in these files as they are designed to be copied together to produce an end file. (If your going to do any mods I recommend you just use Notepad to change text names.)

You also need to find the CdoTimeZoneId for your timezone and change each of the convertUTC function calls so it sends and converts the correct times for your timezone. To run this code you must execute it under a user context that has access to the Meeting Rooms calendar you might want to look at using the NT schedule service to run this code on a regular basis during the day (for example I used a scheduled task that runs it every 5 minutes)

Download Script