Calendar request web form for MS Outlook

For a while, we have used the moderated folder and custom form features of Microsoft Outlook and Microsoft Exchange to allow users to request school vehicles for trips and other purposes. It didn’t work well. Our users view this public folder calendar through Outlook, Entourage, and Outlook Web Access. Only Outlook supports custom forms. Public folders were difficult to find. They don’t sync well with Entourage. It was a mess.

With our upgrade to Exchange 2007, we decided to move away from public folders and create buses as resources instead. At the same time, we wanted to build an easier-to-use request form for users. We wondered whether we could build a simple web script (in Perl or PHP) and send Outlook a calendar object that it could use.

It worked! Uncharacteristically, Microsoft uses an open, standard format for calendar entries. Below, I have reproduced part of a script I wrote that sends a .vcs (vcalendar) file as an attachment that our transportation coordinator may open in Outlook, review, and add to a calendar. May other potential uses for this exist.

Please do try this code and let me know any improvements you make. This is only part of our script code. It won’t work on your server as is!

#!/usr/bin/perl

use Time::Local;
use CGI qw(:standard);
use Net::SMTP;

sub write_form {

param(-name=>'action', -value=>'save_form');

$output = start_form .
p . "Please complete the following form to request a school vehicle." .
"<div style=\"border:1px solid black; padding:5px; margin:20px 0 20px 0;\"><b>Required Information</b>" .
p . "Activity: " . textfield(-name=>'activity', -size=>60) .
p . "Destination: " . textfield(-name=>'destination', -size=>60) .
p . "Depart date: " . popup_menu(-name=>'depart_month', -values=>['',@months], -labels=>\%month_labels) . ' ' . popup_menu(-name=>'depart_day', -values=>['',@mdays]) . ' ' . popup_menu(-name=>'depart_year', -values=>['',@years], -default=>$year) . ' Hour: ' . popup_menu(-name=>'depart_hour', -values=>['',@hours], -labels=>\%hours_labels) . ' Min: ' . popup_menu(-name=>'depart_min', -values=>['',@mins]) .
p . "Return date: " . popup_menu(-name=>'return_month', -values=>['', @months], -labels=>\%month_labels) . ' ' . popup_menu(-name=>'return_day', -values=>['',@mdays]) . ' ' . popup_menu(-name=>'return_year', -values=>['',@years], -default=>$year) . ' Hour: ' . popup_menu(-name=>'return_hour', -values=>['',@hours], -labels=>\%hours_labels) . ' Min: ' . popup_menu(-name=>'return_min', -values=>['',@mins]) .
p . "Driver: " . textfield(-name=>'driver', -size=>'30') .
'  ' . "Contact: " . textfield(-name=>'contact', -size=>'30') .
p . "Number of students + adults: " . textfield(-name=>'total_number', -size=>'3') .
p . "Depart from: " . checkbox_group(-name=>'depart_from', -values=>['Gym','Theater']) .
p . "Division/Department: " . textfield(-name=>'division_department', -size=>'20') . "</div>" .

"<div style=\"border:1px solid black; padding:5px; margin:20px 0 20px 0;\"><b>Optional Information</b>" .
p . "Rental vehicle types (e.g., minivan, SUV, cargo van): " . textfield(-name=>'rental_vehicle_types', -size=>'40') .
p . "Other details: " . checkbox_group(-name=>'other_details', -values=>['Cargo van','Drop off only','Pick up only','Overnight trip']) .
p . "Special requests: " . textfield(-name=>'special_requests', -size=>'60') . "</div>" .
p . submit(-name=>'submit', -value=>'Submit Request') . hidden(-name=>'action') . endform;

$template =~ s/\$body/$output/;
$template =~ s/\$title/Request Transportation/g;
print header . $template;

}

sub save_form {

# check for empty minute fields
if (!param('depart_min')) {param(-name=>'depart_min', -value=>'00');}
if (!param('return_min')) {param(-name=>'return_min', -value=>'00');}

# convert dates to GMT
$depart_localtime = timelocal(0,param('depart_min'),param('depart_hour'),param('depart_day'),param('depart_month'),param('depart_year'));
$return_localtime = timelocal(0,param('return_min'),param('return_hour'),param('return_day'),param('return_month'),param('return_year'));
($s,$n,$h,$d,$m,$y) = gmtime($depart_localtime); $y+=1900;
$vcs_depart_time = $y . sprintf("%02d",$m) . sprintf("%02d",$d) . 'T' . sprintf("%02d",$h) . sprintf("%02d",$n) . sprintf("%02d",$s) . 'Z';
($s,$n,$h,$d,$m,$y) = gmtime($return_localtime); $y+=1900;
$vcs_return_time = $y . sprintf("%02d",$m) . sprintf("%02d",$d) . 'T' . sprintf("%02d",$h) . sprintf("%02d",$n) . sprintf("%02d",$s) . 'Z';

# format vcs file
$vcs = "BEGIN:VCALENDAR
PRODID:-//Microsoft Corporation//Outlook MIMEDIR//EN
VERSION:1.0
BEGIN:VEVENT
DTSTART:$vcs_depart_time
DTEND:$vcs_return_time
LOCATION:" . param('destination') . "
DESCRIPTION;ENCODING=QUOTED-PRINTABLE:Activity: " . param('activity') . "=0D=0ADestination: " . param('destination') . "=0D=0ADepart time: " . param('depart_month') . "/" . param('depart_day') . "/" . param('depart_year') . " " . param('depart_hour') . ":" . param('depart_min') . "=0D=0AReturn time: " . param('return_month') . "/" . param('return_day') . "/" . param('return_year') . " " . param('return_hour') . ":" . param('return_min') . "=0D=0ADriver: " . param('driver') . "=0D=0AContact: " . param('contact') . "=0D=0ANumber of students + adults: " . param('total_number') . "=0D=0ADepart from: " . join(", ", param('depart_from')) . "=0D=0ADivision/Department: " . param('division_department') . "=0D=0ARental vehicle types: " . join(", ", param('rental_vehicle_types')) . "=0D=0AOther details: " . join(", ", param('other_details')) . "=0D=0ASpecial requests:" . param('special_requests') . "=0D=0A
SUMMARY:" . param('activity') . "
PRIORITY:3
END:VEVENT
END:VCALENDAR";

# send mail
$smtp = Net::SMTP->new('localhost');
$smtp->mail($username.'@yourdomain.com');
$smtp->to('facilitiestransportationcoordinator@yourdomain.com');
$smtp->data();
$smtp->datasend("To: Transportation Coordinator\n");
$smtp->datasend("From: $fullname\n");
$smtp->datasend("Subject: Transportation request\n");
$smtp->datasend("MIME-Version: 1.0\n");
$smtp->datasend("Content-Disposition: attachment; filename=\"request.vcs\"\n");
$smtp->datasend("Content-Type: application/text; name=request.vcs\n");
$smtp->datasend("\n");
$smtp->datasend($vcs . "\n\n");
$smtp->dataend;
$smtp->quit;

# return html
$output = "Thank you. The transportation coordinator will review this request and then assign it to a vehicle. Please check the transportation calendar in a day or two to confirm your reservation.<P><a href=$cgiurl>Submit another transportation request</a>";
$template =~ s/\$body/$output/;
$template =~ s/\$title/Transportation Request Sent/g;
print header . $template;

}

One comment

  1. Steve Taffee says:

    Richard, you are a god! You can actually make a MS product do what you want it to. I bow before you.