Tag Archive for outlook

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;

}

Outlook Custom Forms – first attempts

I have always struggled with the great divide between Microsoft Outlook and our other information systems. Outlook can do so much, but I hesitate to make full use of it because its information is not stored in a way that is easily accessible to our web site. I also have not been too keen to fully immerse myself into the VBScript required to write a set of export routines to migrate data in that fashion.

I was pleased this week to discover a middle ground — Outlook custom forms. While they don’t improve the portability of information between Outlook and other systems, custom forms do give us some of the qualities of web forms within Outlook. I can create new data fields that don’t already exist in Outlook. I have full control over the layout of a calendar entry form, for instance. I can perform data validation and require certain fields. My main resource so far has been this Microsoft article.

As this is a new area for me, I could use some tips on how to get them to behave well. I have been stymied by the following problems:

  • I am not allowed to delete custom fields that I created yesterday.
  • I cannot create a scrolling text field with word wrap for free-form text entry.
  • A moderated calendar with a custom form shows the custom form to the user who is making the initial data entry but not to the moderator who receives the email notification containing the new event.
  • I have pulled away from setting up rooms and equipment as resources, because users must find them amongst all our users and groups in the Global Address List, whereas I can present the user with only the relevant options if I use a simple value list that is not linked to Active Directory objects.

Please let me know if you have some experience in this area!

Outlook screen 1

Outlook screen 2