iCalendar event via email

I recently attended a session by Scott Spendolini at the ODTUG conference. In the session he spoke about ApEx "integration" with Outlook among other things. He touched on both the calendar and contacts. It reminded me of a forum post I did a while ago and inspired me to formalize it with this blog post. In Scott's examples, he demonstrated how to make contacts and events in ApEx load directly into Outlook. He even came up with a good way of letting you know if the contacts information has changed since you last loaded it. These were all great new techniques I'd not thought about before.

In the forum post, I wrote about how to send a calendar request via email. This was a technique that I had used in a work flow scenario as the final step. It was necessary because we use Outlook to schedule meetings and didn't want to have to manually add data to Outlook from something that had been scheduled in an ApEx application so that the time would not appear as "free".

At the time, I thought of this as an ApEx solution but the truth is that it is a PL/SQL solution that just works great in ApEx. The solution requires two ingredients: a function (ical_event) to generate an iCalendar event and a process (send_ical_email) to send the request. The APEX_MAIL.SEND procedure will not work in this case as we need more control over the email contents. In the following examples, just the basics are included to get you started but the code can be modified to suite any additional needs. Additional information on the iCalendar (based on vCalendar) standard can be found here. Also, Google has added some of their own additional fields and information on those can be found here. Lastly, an example of the technique can be found here.

Here's what to do...

1. Compile the following function (replace "Your company name" and "yoururl.com" as needed):
   

   create or replace
   FUNCTION ical_event (
      p_summary         IN VARCHAR2
    , p_organizer_name  IN VARCHAR2
    , p_organizer_email IN VARCHAR2
    , p_start_date      IN DATE
    , p_end_date        IN DATE
    , p_version         IN VARCHAR2 := NULL
    , p_prodid          IN VARCHAR2 := NULL
    , p_calscale        IN VARCHAR2 := NULL
    , p_method          IN VARCHAR2 := NULL
   )
   
      RETURN VARCHAR2 
   
   AS  
   
      l_retval VARCHAR2(32767);
      l_lf     CHAR(1) := CHR(10);
   
   BEGIN
   
      l_retval := ''
         || 'BEGIN:VCALENDAR' || l_lf
         || 'VERSION:' || NVL(p_version,'2.0') || l_lf
         || 'PRODID:' || NVL(p_prodid,'-//Your company name//NONSGML ICAL_EVENT//EN') || l_lf
         || 'CALSCALE:' || NVL(p_calscale,'GREGORIAN') || l_lf
         || 'METHOD:' || NVL(p_method,'REQUEST') || l_lf
         || 'BEGIN:VEVENT' || l_lf
         || 'SUMMARY:' || p_summary || l_lf
         || 'ORGANIZER;CN="' || p_organizer_name || '":MAILTO:' || p_organizer_email || l_lf
         || 'DTSTART:' || TO_CHAR(p_start_date,'YYYYMMDD') || 'T' || TO_CHAR(p_start_date,'HH24MISS') || l_lf
         || 'DTEND:' || TO_CHAR(p_end_date,'YYYYMMDD') || 'T' || TO_CHAR(p_end_date,'HH24MISS') || l_lf
         || 'DTSTAMP:' || TO_CHAR(SYSDATE,'YYYYMMDD') || 'T' || TO_CHAR(SYSDATE,'HH24MISS') || l_lf
         || 'UID:' || RAWTOHEX(SYS_GUID()) || '@yoururl.com' || l_lf
         || 'STATUS:NEEDS-ACTION' ||  l_lf
         || 'END:VEVENT' || l_lf
         || 'END:VCALENDAR';
      
      RETURN l_retval;
         
   END ical_event;

2. Create the following procedure (replace "yoururl" ass needed):

   create or replace
   PROCEDURE send_ical_email (
      p_from      IN VARCHAR2
    , p_to        IN VARCHAR2
    , p_subj      IN VARCHAR2
    , p_body_html IN VARCHAR2
    , p_body_ical IN VARCHAR2
   )
    
   AS
   
      l_connection UTL_SMTP.CONNECTION;
      l_mail_serv  VARCHAR2(50) := 'mail.yoururl.com';
      l_mail_port  PLS_INTEGER := '25';
      l_lf         CHAR(1) := CHR(10);
      l_msg_body   VARCHAR2(32767);
    
   BEGIN
      
      l_msg_body :=
            'Content-class: urn:content-classes:calendarmessage' || l_lf
         || 'MIME-Version: 1.0' || l_lf
         || 'Content-Type: multipart/alternative;' || l_lf
         || '	boundary="----_=_NextPart"' || l_lf
         || 'Subject: ' || p_subj || l_lf 
         || 'Date: ' || TO_CHAR(SYSDATE,'DAY, DD-MON-RR HH24:MI') || l_lf
         || 'From: <' || p_from || '> ' || l_lf 
         || 'To: ' || p_to || l_lf 
         || '------_=_NextPart' || l_lf
         || 'Content-Type: text/plain;' || l_lf
         || '	charset="iso-8859-1"' || l_lf
         || 'Content-Transfer-Encoding: quoted-printable' || l_lf
         || l_lf
         || 'You must have an HTML enabled client to view this message.' || l_lf
         || l_lf
         || '------_=_NextPart' || l_lf
         || 'Content-Type: text/html;' || l_lf
         || '	charset="iso-8859-1"' || l_lf
         || 'Content-Transfer-Encoding: quoted-printable' || l_lf
         || l_lf
         || p_body_html || l_lf
         || l_lf
         || '------_=_NextPart' || l_lf
         || 'Content-class: urn:content-classes:calendarmessage' || l_lf
         || 'Content-Type: text/calendar;' || l_lf
         || '  method=REQUEST;' || l_lf
         || '  name="meeting.ics"' || l_lf
         || 'Content-Transfer-Encoding: 8bit' || l_lf
         || l_lf
         || p_body_ical || l_lf
         || l_lf
         || '------_=_NextPart--';
               
      l_connection := utl_smtp.open_connection(l_mail_serv, l_mail_port);
      utl_smtp.helo(l_connection, l_mail_serv);
      utl_smtp.mail(l_connection, p_from);
      utl_smtp.rcpt(l_connection, p_to);
      utl_smtp.data(l_connection, l_msg_body);
      utl_smtp.quit(l_connection);
      
   END send_ical_email;

3. Use ApEx to create a submit button along with the necessary items to collect the following:
   1. Start Date
   2. End Date
   3. Organizer Name
   4. Organizer Email
   5. Attendee Email
   6. Email Subject
   7. Email Body
4. Create a page process similar to the following that fires when the submit button is pressed (this will vary depending on step 3):

   DECLARE
   
      l_ical_event VARCHAR2(32767);
   
   BEGIN
   
      l_ical_event := ical_event(
         p_start_date      => TO_DATE(:PXX_START_DATE || :PXX_START_TIME,'DD-MON-YYYYHH:MIPM')
       , p_end_date        => TO_DATE(:PXX_END_DATE || :PXX_END_TIME,'DD-MON-YYYYHH:MIPM')
       , p_summary         => :PXX_SUBJ
       , p_organizer_name  => :PXX_USER_NAME
       , p_organizer_email => :PXX_USER_EMAIL
      );
   
      send_ical_email( 
         p_to        => :PXX_TO_ADDRESS
       , p_from      => :PXX_USER_EMAIL
       , p_subj      => :PXX_SUBJ
       , p_body_html => :PXX_BODY_HTML 
       , p_body_ical => l_ical_event
      );
      
   END;

That should do it. Submit the page to send the request.