SQL Server Central is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
Search:  
 
Home Articles Editorials Forums Scripts Blogs QotD SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us
 

Outlook Appointments, ASP and vCalendar

2001/08/30

Total article views: 5364 | Views in the last 30 days: 54
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>

2001/08/30

Total article views: 5364 | Views in the last 30 days: 54
Your response
 
  
Related tags

ASP    
Programming    
 
Related content

Using InterDev's Toolbox

By Leon Platt | Category: .Net
| 3,019 reads

Invisible Comments

By Leon Platt | Category: ASP
| 3,355 reads

Connecting to SQL Server with ASP

By Brian Knight | Category: ASP
| 7,970 reads

IIS Connection Pooling ? ADO Gotcha!

By Leon Platt | Category: ASP
| 6,468 reads
Already registered?  

Free registration required

To read the rest of this article, and access thousands of other articles, we ask you to register on the site and subscribe to our newsletters.

Register

E-mail address:
Password:
Password (confirm):

  

Subscriptions

We ask you to register on the site and subscribe to our newsletters. Subscribing to our newsletters gets you:

  • ALL of our content (thousands of articles, scripts, and forum postings)
  • A daily newsletter (example)
  • A weekly news round up (example)
  • The opportunity to ask and answer questions in our forums
  • A daily Question of the Day to test and help you increase your knowledge of SQL Server.

We ask that you give the newsletter a try for a week. Over 200,000 SQL Server Professionals a day find it entertaining and useful. If not, you are welcome to unsubscribe at anytime.

Steve Jones
Editor, SQLServerCentral.com

 
Copyright © 2002-2008 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use