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...
- 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; - 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; - Use ApEx to create a submit button along with the necessary items to collect the following:
- Start Date
- End Date
- Organizer Name
- Organizer Email
- Attendee Email
- Email Subject
- Email Body
- 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.
