Friday, June 20, 2008

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.

44 comments:

  1. Hi Dan,
    I am trying to use this with one of the apex applications that i am building. I have hit a small problem during this and thought if you could help me with it.

    The emails seem to be firing and recipient is receiving the emails. But it does not display as normal calendar event in outlook. Instead it is displayed as a normal text message with "You must have an HTML enabled client to view this message." on the top followed by

    ------_=extPart
    Content-class: urn:content-classes:calendarmessage
    Content-Type: text/calendar;
    method=QUEST;
    name=eeting.ics"
    Content-Transfer-Encoding: 8bit
    .
    .
    .

    Whereas emails from the example application that your post points to is behaving perfectly and appear as a calendar event with accept /reject button on top. Would you have any idea as to why it is not working for me?

    Many thanks,
    Bhavesh

    ReplyDelete
  2. I too am having a problem...

    You must have an HTML enabled client to view this message.

    ------_=_NextPart
    Content-Type: text/html;
    charset="iso-8859-1"
    Content-Transfer-Encoding: quoted-printable

    test

    ------_=_NextPart
    Content-class: urn:content-classes:calendarmessage
    Content-Type: text/calendar;
    method=REQUEST;
    name="meeting.ics"
    Content-Transfer-Encoding: 8bit

    BEGIN:VCALENDAR
    VERSION:1.0
    PRODID:-//My Company//NONSGML ICAL_EVENT//EN CALSCALE:GREGORIAN METHOD:REQUEST BEGIN:VEVENT
    SUMMARY:test1
    ORGANIZER;CN="Admin":MAILTO:admin@***.com
    DTSTART:20081001T120000
    DTEND:20081001T130000
    DTSTAMP:20081001T114241
    UID:15245400A9C24021800949B400183991@***.com
    STATUS:NEEDS-ACTION
    END:VEVENT
    END:VCALENDAR

    ------_=_NextPart--

    Any help would be greatly appreciated!

    Thanks,

    Chris

    ReplyDelete
  3. Chris,
    You can try using
    l_lf CHAR(2) := CHR(13)||CHR(10);
    as line feed characters in both of them . That resolved my issue.

    Regards,
    Bhavesh

    ReplyDelete
  4. Thanks for your quick response Bhavesh, but it hasn’t solved the problem. Still getting the same message.

    ReplyDelete
  5. Chris,

    Could you please send me an email? I don't see any contact information for you anywhere.

    Regards,
    Dan

    ReplyDelete
  6. Dan,
    This sounds like the solution I am looking for, but I am unable to get the meeting invite to appear. I do get the infamous "You must have an HTML enabled client to view this message" notification when the invite/message is received, but the message body relfects the intended message.
    Can this be adapted to work with a non-HTML enabled client? If so, how? I tried changing the Content-Type reference from tect/html to tect/plain, but this did not have any noticeable affect.
    As a security policy, our mail clients are not permitted to render HTML but only plain text. Thanks.


    Ed

    ReplyDelete
  7. Hi Dan

    I am encountering the same issue with the sample provided, do you know if a solution was ever found

    Thanks

    Ger

    ReplyDelete
  8. Dan
    I am trying unsuccessfully to test you solution, however I appear to be getting the same error message as Ed. Do you know if this issue was ever resolved.

    Thanks

    Ger

    ReplyDelete
  9. Ger,

    I didn't see that Ed got an error message. Please email me as I don't see your email address anywhere. What system are you testing in? Have you tested the solution in an environment that can already work with such a request?

    Regards,
    Dan

    ReplyDelete
  10. Dan,
    Is there a way to implement inserting an iCalendar event via email without using an HTML enabled client? Thanks.

    Ed

    ReplyDelete
  11. Ed,

    Sorry for not responding sooner. Yours is an interesting question that frankly would depend on the client. The only two email clients I tested against were Outlook and Gmail but I used HTML emails.

    I'll look into this over the weekend and let you know what I find.

    Regards,
    Dan

    ReplyDelete
  12. Dan,
    We are using Microsoft Outlook for our email client, but they are configured to send and receive messages in Outlook Rich Text format ONLY.

    Thanks.
    Ed

    ReplyDelete
  13. Hi Dan,

    Just wondering if you can take this further and have as an option to flag the iCalendar meeting in an APEX application calendar?

    Also, can this also be used to send normal emails? I cant see why it couldnt but just checking :-).

    How does it handle replies if the recipient declines a meeting in Outlook?

    Does the user need to know the recipients full email address or can it cross reference a contact list in APEX?

    BTW, brilliant site. I only just discovered it today.

    Kindest regards
    Ros

    ReplyDelete
  14. Ros,

    Thanks for you comments. I've been thinking about revisiting this code and expanding on it but I'm a little tied up right now.

    If you like, send me an email and we can talk more about some of the features you're looking for.

    Regards,
    Dan

    ReplyDelete
  15. I've been working on this iCalendar event via email example and I continue to have one small problem. Calendar requests always end up in the Junk E-mail box of Office 2007. However, whenever I send from your working example, http://apex.shellprompt.net/pls/apex/f?p=566:4, the example works fine and the message appears in my inbox. I have checked, and double-checked the code yet found no errors (in fact, it matches the example almost 100%). Does anyone have any idea on why this is happening for me locally yet not in the posted sample?

    ReplyDelete
  16. Randy,

    You're spam/garbage filter seems to have picked up on something in the email - could be a header. To find the cause you'll need to view the email in it's entirety and look for differences.

    If you've not worked with a pop3 server before an easy way to view the email is to send it to a gmail account. Open the mail there and click the down arrow and then on Show original.

    Regards,
    Dan

    ReplyDelete
  17. Dan,

    Thanks for the great post. I was able to successfully implement your sample on our development side, which uses a Microsoft Exchange server. However, in production is a different story. We are using Domino 8.5 as our SMTP relay server. When the email gets sent through this server, it gets stuck in the queue and causes the server to stop sending any further emails until we delete the ical email from the queue. Any ideas?

    ReplyDelete
  18. Kris,

    Good timing... I'm currently revisiting this subject. I'm surprised that something would choke your mail relay. Have you already tried changing the linefeed from chr(10) to chr(13)||chr(10) or vice versa?

    Regards,
    Dan

    ReplyDelete
  19. Dan,

    I originally used the chr(10) in development with the exchange server, but had to change it to chr(13)||chr(10) for it to work properly. I had not thought of using chr(10) for Domino until you suggested it. Interestingly, after changing it back to chr(10), the email with the ical does get sent out, but when the email arrives in my inbox, there is nothing in the email. There is no subject, no body, and the TO: says undisclosed-recipients. As soon as I change the LF back to chr(13)||chr(10), then the email gets stuck in the Domino inbox, and won't send. The admin had to delete it so that the other emails would send.

    Thanks,
    Kris

    ReplyDelete
  20. Hi Dan,
    I'm trying to test you code. I receive email with an attachment "not supported calendar message.ics". When I open it I see meeting details. What should I do to receive meeting as an email and not as attachment. We use Outlook 2007.

    Thanks a lot!
    Grigoriy

    ReplyDelete
  21. Grigoriy,

    I'm actually revisiting this topic this weekend. I'll release an update soon which may fix your issue.

    Regards,
    Dan

    ReplyDelete
  22. Hi Dan,

    I am looking for an option to send a recurring meeting request. The meeting is supposed to span over a from date and to date. Is there a way for this? I could also do with a way to send requests which just sends requests and blocks the calendar and doesnt require a user response. Thanks in advance for your help!

    Regs,
    Aha

    ReplyDelete
  23. Aha,

    Please email me to discuss this further...

    Regards,
    Dan

    ReplyDelete
  24. Hi Dan,

    It is working fine for desktops with Outlook, but for Outlook Web Acces, I receive the same MR as an e-mail instead of MR. Have you seen this before?

    ReplyDelete
  25. Anonymous,

    Please email me to discuss this further...

    Regards,
    Dan

    ReplyDelete
  26. Thanks Dan for the wonderful post. I've successfully been able to create a calendar invite for one of our applications. However we have a requirement to have the calendar responses be tracked and when I create a calendar using pl/sql and assign myself as an organizer, I don't see the tracking tab even though I invite myself to the event. When I create an outlook calendar directly, I have an email in my sent folder and I also see a tracking tab. Can this feature be achieved programmatically? I think if somehow I can get the sent email and a copy of the calendar created in my outlook, the tracking tab would show up. Is there any way to achieve this?

    Thanks for your help.

    ReplyDelete
  27. Anonymous,

    Please contact me via email...

    ReplyDelete
  28. Hi Dan,
    great blog! Your iCal solution for pl/sql helped me alot with a project I'm working on and, definitely, taught me somthing new and interesting today.

    Kind regards,
    Davor

    ReplyDelete
  29. Is this the current code? I tried the chr(13)||chr(10). That did not help. Is the read as HTML setting server level or user level? In outlook 2007 I see how to set the outgoing message as HTML but not how to read the message as HTML. It looks like Outlook is ignoring CR/LF.

    ReplyDelete
  30. Harry,

    Please contact me via email.

    ReplyDelete
  31. Hi Dan,
    This works great for me if I open the e-mail in Gmail, however if I open the e-mail in Outlook 2003 I simply get a flat e-mail with subject and body populated, but no calendar invitation (not even the "You must have an HTML enabled client to view this message" message that others have referred to)?

    Any idea what I could be missing?

    Thanks,

    Mike

    ReplyDelete
  32. Is there a way to add the meeting request to the calendar, without prompting for the meeting to be accepted/declined?

    ReplyDelete
  33. Mike,

    That's a good question. It's been a while since I worked on this... Are you able to do it in Outlook normally? If so, you have to examine that email using a tool that shows you everything about the email. Then just copy that technique.

    Regards,
    Dan

    ReplyDelete
  34. Mike,

    As to your first question. Perhaps you need to change your carriage return... If that's not it then there's probably some other kind of formatting issue.

    It's always best to deconstruct an email that works and compare that to one that doesn't. However, you have to see the ENTIRE email. An easy way to do this is with gmail. Open an email, click on the "down arrow", and select Show Original.

    Regards,
    Dan

    ReplyDelete
  35. Hello,

    the code worked for me and that's really cool. Still, as a very minor improvement I would suggest to use utl_smtp.crlf as a replacement for your lf constant so you don't need experiment with the line endings.

    Cheers
    Jochen

    ReplyDelete
  36. Hi Jochen,

    I'm glad it worked for you (it's an old post) :)

    Dan

    ReplyDelete
  37. CoffeeMan,

    I don't remember why I used UTL_SMTP over UTL_MAIL. This is an old post. You'll have to test to find out.

    Regards,
    Dan

    ReplyDelete
  38. Hi. I know this blog was from a while ago, but I am trying to follow these instructions, but I have to use APEX_MAIL. I set this up before I read that you couldn't do it from APEX_MAIL. Now I am receieiving emails with the always classic "You must have HTML enabled client" Can I sort this error out or am I going to have to attach the calendar event (APEX_MAIL.ADD_ATTACHMENT) This might be a problem because the attachment has to be a BLOB


    Thanks,
    Amber

    ReplyDelete
  39. Hi Amber,

    I'm not sure if you can do this with the APEX_MAIL package. It will just require some careful testing and analysis. Unfortunately, I don't have time to do it myself...

    Regards,
    Dan

    ReplyDelete
  40. I just tried your example link, but it shows this errer:

    "Access denied by Page security check"

    Does it need updating?

    Thanks,

    ReplyDelete
  41. Anonymous,

    Actually, no. Well, I guess ;)

    I shut it down because some people started using it to send spam out.

    I may put up another example in the future with a capcha or something. I just need to find the time...

    Regards,
    Dan

    ReplyDelete
  42. Hi,
    It is working fine. If I confirm yes then add the gmail calendar otherwise it does not display or auto sync in gmail calendar. But i need auto sync in gmail calendar. What can i do? Please any one help me.

    ReplyDelete
  43. I'm not sure I follow. What do you mean by auto sync? If you add the event to your calendar, then you should see the event on any device that's able to view the calendar.

    ReplyDelete