Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Outlook Appointments, ASP and vCalendar

Outlook Appointments, ASP and vCalendar

What is vCalendar
I recently posted some VB Script code that exploits the MS Outlook object model to create an Outlook Appointment Item in a Public Folder. I received several replies from programmers who downloaded the code, but had trouble getting it to work in an ASP page. It seems that it's not possible to set an object to the MAPI Namespace from VB Script in an ASP page. The following code returned the error, "Server unable to create object" ...


    Set objOutlook = CreateObject("Outlook.Application")
    Set objNamespace = objOutlook.GetNameSpace("MAPI")

Since we couldn't get around this seeming limitation of Outlook on IIS, I decided to pursue a different solution-- vCalendar.

vCalendar defines a transport and platform-independent format for exchanging calendaring and scheduling information in an easy, automated, and consistent manner. It captures information about event and "to-do" items that are normally used by applications such as a personal information managers (PIMs) and group schedulers. Programs that use vCalendar can exchange important data about events so that you can schedule meetings with anyone who has a vCalendar-aware program.

Beginning in December, 1996, the Internet Mail Consortium took on responsibility for the development and promotion of this important technology.

A vCalendar record is just a text file. If you do not have an automated facility to process vCalendar records, you can open them with a text editor and use the information. The content of a vCalendar file will vary with the information inserted by the file creator. The format we will use from our custom VB DLL looks like the following in a text editor:
    BEGIN:VCALENDAR
    VERSION:1.0
    BEGIN: VEVENT
    DTStart:20001122T153000Z
    DTEnd:20001122T210000Z
    LOCATION;ENCODING=QUOTED-PRINTABLE:The Batcave
    SUMMARY;ENCODING=QUOTED-PRINTABLE:Weekly Meeting with Alfred
    DESCRIPTION;ENCODING=QUOTED-PRINTABLE:Discuss Batmobile Maintenance ...
    UID:20000922T07000020000922T070000
    PRIORITY:3
    End:VEVENT
    End:VCALENDAR
The above format omits some information specific to MS Outlook. This was done to make the file more compatible with non-Microsoft PIMs, which may be accessing the our vCalendar-enabled web page. To create a vCalendar file in MS Outlook format, follow these steps:
  1. In a Calendar folder, click to select an appointment for which you want a vCalendar file.
  2. On the File menu, click Save As.
  3. In the "Save file as type" list, click to select vCalendar Format (*.vcs).
  4. In the Save In list, select the folder where you want to save the vCalendar file, and then click Save.
NOTE: the DTSTART and DTEND entries above are a combination of the date and time in the format, YYYYMMDDThhmmssZ, where YYYY=year, MM=month, DD=day of the month, T=start time character, hh=hour, mm=minutes, ss=seconds, Z=end character. This string expresses the time as Greenwich Mean Time (GMT), on a 24-hour clock so must be adjusted to your time zone.

For example, if you are in the Mountain Time zone as I am, your time is 7 hours behind GMT. So, you would subtract 7 hours from the start and end times in the vCalender text to derive the correct time range for the appointment. In the appointment above the start time would be 153000-070000. Notice that a 24-hour clock is used and times are converted from A.M/P.M to military time.

Well, that's it for vCalendar. Now we'll look at how we can create a custom VB Class to handle the string parsing and vCalendar file creation. Then, the whole thing can come together in an ASP page which invokes the method to create the vCal file and the executes the VB Script necessary to insert our appointment in the Web User's Outlook Calendar.

The Custom VB vCalendar Class and DLL
I created a custom Visual Basic class to serve as a wraper around the process of creating the vCalendar text file. The class does limited date validation (that responsibility is left to the ASP code), but does some time zone adjustment and parses the date into a vCalendar-ready text string, prior to writing the formatted text to a .vcs file.

This class exposes only the most basic properties of the Outlook Appointment item. If more properties need to be set, you could determine the format by following the instructions on the previous page for using Outlook to create a vCalendar template and add those properties to the VB Class. Note that Property Let and Property Get procedures are used to populate the following private variables:
    Private dtmStart As Date
    Private dtmEnd As Date
    Private strLocation As String
    Private strSubject As String
    Private strDescription As String
    Private strOutputFilePath As String
    Private intTimeZoneBias As Integer
The class has only one Method which parses the variables into strings and creates the .vcs file. This method returns the path to the .vcs file as a string value. This file path is needed by the ASP page, as we will see later. Notice the relatively simple code involved with this proces:


    Public Function CreateVCSEvent() As Boolean
        On Error Resume Next
    
        Dim strStartDate As String
        Dim strEndDate As String
        Dim strToday As String
        Dim iFileNumber As Integer
        Dim strVCalFile As String
    
        ' VCS uses GMT so all values must be adjusted according to
        ' your Time Zone.  Mountain Time (Denver) is -7
        ' TimeZone Offset from GMT is always Negative.  Make it positive.
        intTimeZoneBias = intTimeZoneBias * (-1)
        
        dtmStart = DateAdd("h", intTimeZoneBias, dtmStart)
        dtmEnd = DateAdd("h", intTimeZoneBias, dtmEnd)
        
        ' Format user-supplied data in VCS required format.
        ' Note that the style "Hhnnss" returns time in 24-hour format.
        strStartDate = Format(dtmStart, "yyyymmdd") & "T" & Format(dtmStart, "Hhnnss")
        strEndDate = Format(dtmEnd, "yyyymmdd") & "T" & Format(dtmEnd, "Hhnnss")
        
        ' If no date was passed, for example, "8:30 am", the date defaults to 12/30/1899
        ' Since this date is of no value, test for it and convert it to the current date.
        ' (Code could also be added to handle dates passed with no TIME value.)
        If Left(strStartDate, 8) = "18991230" Then
            strToday = Format(Date, "yyyymmdd")
            strStartDate = strToday & Mid(strStartDate, 9)
        End If
    
        If Left(strEndDate, 8) = "18991230" Then
            strToday = Format(Date, "yyyymmdd")
            strEndDate = strToday & Mid(strEndDate, 9)
        End If
  
        ' Create the file based on the path provided by the user.  Use the
        ' Subject as the name of the file.
        iFileNumber = FreeFile
        If Right(strOutputFilePath, 1) = "\" Then
            strVCalFile = strOutputFilePath & strSubject & ".vcs"
        Else
            strVCalFile = strOutputFilePath & "\" & strSubject & ".vcs"
        End If

        ' Create the .vcs file.  Use the Start and End dates and Subject  as a unique UID
        Open strVCalFile For Output As #iFileNumber
    
        Print #iFileNumber, "BEGIN:VCALENDAR"
        Print #iFileNumber, "VERSION:1.0"
        Print #iFileNumber, "BEGIN: VEVENT"
        Print #iFileNumber, "DTStart:" & strStartDate
        Print #iFileNumber, "DTEnd:" & strEndDate
        Print #iFileNumber, "Location;ENCODING=QUOTED-PRINTABLE:" & strLocation
        Print #iFileNumber, "SUMMARY;ENCODING=QUOTED-PRINTABLE:" & strSubject
        Print #iFileNumber, "DESCRIPTION;ENCODING=QUOTED-PRINTABLE:" & strDescription
        Print #iFileNumber, "UID:" & strStartDate & strEndDate & strSubject
        Print #iFileNumber, "PRIORITY:3"
        Print #iFileNumber, "End:VEVENT"
        Print #iFileNumber, "End:VCALENDAR"
        
        Close #iFileNumber
		
        ' Note that more properties could easily be added
    End Function

In order for this class to be used by our ASP page, it is necessary to compile it into an Active-X dll and register it on the server. The download for this article includes a VB project with the code and an ASP page to execute it. I named the DLL VCSWrap and if you unzip the contents of the download into a folder named vCal under the C:\Inetpub\wwwroot\ directory, then you can register it with this line:
    regsvr32 C:\Inetpub\wwwroot\Vcal\VCalWrap.dll

vCalendar and ASP

Now comes the fun part ... seeing it work!

Below is posted the text of the ASP page, but due to difficulties with displaying ASP code within an ASP page, I suggest that you download the code and use that copy of the page instead of trying to copy and paste the code below.

The page is mostly self-explanatory for those familiar with ASP. In a nutshell, it draws several input boxes on the screen that collect the information we want in our appointment item: Start, End, Subject, Location and Description. The user is also asked for a directory (on the server) where the file should be created. Of course this wouldn't be done in production, but I included it in the ASP code since it is necessary for the VB Class, and must be provided somewhere. It could be hard-coded and in my example, defaults to "C:\" if the user provides nothing.

The most important part of the code is where the VCSWrap.ApptItem class is instantiated, its properties are set and the CreateVCSEvent method is invoked. The vCalendar file is created and its path is passed to the Response.Redirect method of the ASP page. Since a vcs file cannot be displayed, it is opened automatically. If the user's PIM application is designed to process .vcs files, as Outlook is, an appointment item is opened with all the properties set from our web page.


        Dim objVCal, VCalFile
        Set objVcal = Server.CreateObject("VCalWrap.ApptItem")

        With objVCal
            .DTStart = DTStart
            .DTEnd = DTEnd
            .Subject = Subject
            .Location = Location
            .Description = Description
            .OutputFilePath = OutputFilePath
            .TimeZoneBias = -7
            .CreateVCSEvent
            VCalFile = .VCalFile
        End With
        Response.Redirect(VCalFile)
Conclusion
Some of you may not immediately see the benefits of this technology while others are getting excited and anxious to try out the code. Addmittedly, I was not at first convinced that vCalendar could add value to our web application. Then, our ASP developer put together code similar to the above and did a demo for the sales and marketing guys. Their excitement made up for my lack of it and I decided to give the idea a second look. Although it doesn't solve every problem, it does make it easy to "calendar-enable" your web application ... and it's cool to play with!


********* BEGIN ASP PAGE CODE HERE ************************************************ 

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">  
 <HTML><HEAD>  
 <META content="text/html; charset=unicode" http-equiv=Content-Type>  
 <META content="MSHTML 5.00.3207.2500" name=GENERATOR></HEAD>  
 <BODY>  
 <FORM method=post action="TestVCal.asp" name="VCal">  
 <pre>  
 <P align=center><FONT face=Verdana size=4>  
     <STRONG>VCalendar Test Page</STRONG></FONT></P>  
 <%  
    Dim DTStart, DTEnd, Subject, Location  
    Dim Description, OutputFilePath  
    Dim fMissingCriticalData, strMsg, Pass  

    DTStart = Request.Form("DTStart")  
    DTEnd = Request.Form("DTEnd")   
    Subject = Request.Form("Subject")  
    Location = Request.Form("Location")  
    Description = Request.Form("Description")  
    OutputFilePath = Request.Form("OutputFilePath")  
    Pass = Request.Form("Pass")  

    fMissingCriticalData=0  
    strMsg = ""  
    ' Put some default values in input boxes  
    If Len(Subject) = 0 Then Subject = "Undetermined"  
    If Len(Location) = 0 Then Location = "Undetermined"  
    If Len(Description) = 0 Then Description = "Undetermined"  
    If Len(OutputFilePath) = 0 Then  OutputFilePath ="C:\"  
    If Len(Pass) = 0 Then Pass = 0  
    If Pass > 0 Then  
        If Len(DTStart)= 0 Or Not IsDate(DTStart) Then   
            fMissingCriticalData=1  
           strMsg = strMsg & "<Font color=red>Missing valid start date and time.</Font><BR>"  
        End If
 
        If Len(DTEnd)= 0 Or Not IsDate(DTEnd) Then   
            fMissingCriticalData=1  
           strMsg = strMsg & "<Font color=red>Missing valid end date and time.</Font><BR>"  
        End If  
    Else  
        strMsg = "<B>Provide the following information to create the appointment item.</b>"  
    End If  

    If fMissingCriticalData=1 Or pass=0 Then   
       Response.Write(strMsg)  
 %>  
       <FONT face=Veranda size=3>  
          <INPUT id=txtPass name=Pass style="VISIBILITY: hidden" value="<%=Pass + 1 %>">  
       <TABLE Border=0 align=center>  
         <tr>  
            <td>Start Date and Time:</td>  
            <td><INPUT align=right id=txtDTStart name=DTStart value=<%=DTStart %></td>  
            <td>(Format:  mm/dd/yy hh:nn am/pm)</td>  
         </tr>  
         <tr>  
            <td>End Date and Time:</td>  
            <td><INPUT align=right id=txtDTEnd name=DTEnd value=<%=DTEnd %></td>  
            <td>(Format:  mm/dd/yy hh:nn am/pm)</td>  
         </tr>  
         <tr>  
            <td>Subject:</td>  
            <td><INPUT align=right id=txtSubject name=Subject value=<%=Subject %>></td>  
            <td></td>  
         </tr>  
         <tr>  
            <td>Location:</td>  
            <td><INPUT align=right id=txtLocation name=Location value=<%=Location %>></td>  
            <td></td>  
         </tr>  
         <tr>  
            <td>Description:</td>  
            <td><INPUT align=right id=txtDescription name=Description value=<%=Description %>></td>  
            <td></td>  
         </tr>  
         <tr>  
            <td>Output File Path:</td>  
            <td><INPUT align=right id=txtOutputFilePath name=OutputFilePath value=<%=OutputFilePath %>></td>  
            <td></td>  
         </tr>  
         <tr>  
            <td></td>  
            <td><INPUT id=submit1 name=submit1 type=submit value=Submit></td>  
            <td><INPUT id=reset1 name=reset1 type=reset value=Reset></td>  
         </tr>  
       </FONT>  
       </TABLE>  
 <%  
    Else
       If Len(Subject) = 0 Then Subject = "(No Subject Given)"  
       If Len(Location) = 0 Then Location = "(No Location Given)"  
       If Len(Description) = 0 Then Description = "(No Description Given)"  
       Dim objVCal, VCalFile  
       Set objVcal = Server.CreateObject("VCalWrap.ApptItem")  
       With objVCal  
          .DTStart = DTStart  
          .DTEnd = DTEnd  
          .Subject = Subject  
          .Location = Location  
          .Description = Description  
          .OutputFilePath = OutputFilePath  
          .TimeZoneBias = -7  
          .CreateVCSEvent  
          VCalFile = .VCalFile  
       End With 
       Response.Redirect(VCalFile)  
   
    End If
 %>  
 </FORM>  
 </BODY></HTML>  
 

Total article views: 5919 | Views in the last 30 days: 2
 
Related Articles
FORUM

Subject and descriptions for posts

Is there anything thing we can do, some setting client side or whatnot to be able to see more of the...

FORUM

SQL Server table backups location

SQL Server table backups location

FORUM

Formatting Dates

Weird results with formatting dates in different locations

FORUM

Hresult: 0x80004005 Description:

Hresult: 0x80004005 Description:

FORUM

Index Location?

Index Location?

Tags
asp    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones