This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Sep 4, 2013 5:59 AM by user769418 RSS

Any interest in sending emails as Outlook appt. request???

dmcghan Oracle ACE
Currently Being Moderated
All,

I recently had a business need to send emails as meeting requests in Outlook. There are two ways to do this:
1. The appt is sent as an attachment that a user can open and accept.
2. The appt is sent as the email.

The latter was the business requirement. This is done be combining a vcalendar standard with some things that Outlook needs to see the email as an appointment.

It was a pain in the butt but I've made progress and got it working. It will NOT work with APEX_MAIL.

I'm posting this because I know this is not just ApEx specific but if some people are interested, and the forum administrators don't mind, I'll post some details to get the ball rolling for others.

Please respond to this if you are interested.

Regards,
Dan
  • 1. Re: Any interest in sending emails as Outlook appt. request???
    Jes Oracle ACE
    Currently Being Moderated
    Hi Dan,

    I think that would be useful to a lot of people, and I don't see why anyone would mind about you posting your solution.

    John.


    ---------------------------------------------
    http://jes.blogs.shellprompt.net
  • 2. Re: Any interest in sending emails as Outlook appt. request???
    dmcghan Oracle ACE
    Currently Being Moderated
    John,

    Ok, I'll get some code together and post it tonight...

    Regards,
    Dan
  • 3. Re: Any interest in sending emails as Outlook appt. request???
    478676 Journeyer
    Currently Being Moderated
    Speak out Dan :)

    Thanks,
    Paulo
  • 4. Re: Any interest in sending emails as Outlook appt. request???
    598220 Newbie
    Currently Being Moderated
    I agree Dan, I would love to see it.
  • 5. Re: Any interest in sending emails as Outlook appt. request???
    dmcghan Oracle ACE
    Currently Being Moderated
    All,

    Of the two methods I mentioned before I will explain the second: sending the appointment as the email. The trick here, and the reason APEX_MAIL will not work, is that the email is a MIME multipart message that consists of two parts: the icalendar object and the regular message data. APEX_MAIL, at least for now ;), is not robust enough to allow us to create and specify the custom MIME types needed. For the basics on multipart messages see the following...

    http://en.wikipedia.org/wiki/MIME#Multipart_Messages

    For those of you that have the ability to use Java, the javax.mail package will work BUT REQUIRES MODIFICATION. See the following for details...

    http://forum.java.sun.com/thread.jspa?threadID=169827&start=0&tstart=0

    If you would prefer to keep your solution in PL/SQL or do not have the ability to use Java, I've included a specification and body for a basic procedure, SEND_MAIL, that can be added to any package. This is only a basic procedure to help get you started and requires EXECUTE ON UTL_SMTP.

    All you have to do is call the SEND_MAIL procedure passing a valid icalendar object to the p_body_cal parameter. For details on that see the following…

    http://en.wikipedia.org/wiki/ICalendar

    I HIGHLY recommend creating a function to create your icalendar objects for you. Let me know if you would like an example of such a function.


    SEND_MAIL spec
       PROCEDURE send_mail (
          p_from         IN  VARCHAR2
        , p_to           IN  VARCHAR2
        , p_subj         IN  VARCHAR2
        , p_body_html    IN  VARCHAR2
        , p_body_cal     IN  VARCHAR2
       );
    SEND_MAIL body
       PROCEDURE send_mail (
          p_from         IN  VARCHAR2
        , p_to           IN  VARCHAR2
        , p_subj         IN  VARCHAR2
        , p_body_html    IN  VARCHAR2
        , p_body_cal     IN  VARCHAR2
       )
        
       AS
      
          l_connection   UTL_SMTP.CONNECTION;
          l_mail_server  VARCHAR2(255) := 'mail.yourserver.com';
          l_mail_port    PLS_INTEGER := 25;
          l_msg_hrd      VARCHAR2(32767);
          l_msg_body     VARCHAR2(32767);
          l_lf           VARCHAR2(2) := CHR(13)||CHR(10);
       
       BEGIN
    
          l_msg_body := '' 
             || 'Date: ' || TO_CHAR(SYSDATE,'DAY, DD-MON-RR HH24:MI') || l_lf 
             || 'From: <' || p_from || '> ' || l_lf 
             || 'Subject: ' || p_subj || l_lf 
             || 'To: ' || p_to || l_lf 
             || 'MIME-Version: 1.0' || l_lf
             || 'Content-type: multipart/mixed; boundary="frontier"' || l_lf
             || '--frontier' || l_lf
             || 'Content-Type: text/calendar' || l_lf
             || 'charset="utf-8"' || l_lf
             || 'method=REQUEST' || l_lf
             || 'component=vevent' || l_lf 
             || l_lf
             || p_body_cal || l_lf
             || '--frontier' || l_lf
             || 'Content-Type: text/html' || l_lf
             || 'charset="utf-8"' || l_lf 
             || l_lf
             || p_body_html || l_lf
             || '--frontier--';
                   
          l_connection := utl_smtp.open_connection(l_mail_server, l_mail_port);
          utl_smtp.helo(l_connection, l_mail_server);
          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_mail;
    Good luck and happy coding.

    Regards,
    Dan

    I forgot to define l_lf...

    Message was edited by:
    dmcghan
  • 6. Re: Any interest in sending emails as Outlook appt. request???
    507127 Newbie
    Currently Being Moderated
    Could you post the procedure you're using to format the body of the vCalendar appointment?
  • 7. Re: Any interest in sending emails as Outlook appt. request???
    622195 Newbie
    Currently Being Moderated
    This is new territory for me also. I would also like to see how you developed the code to put together the vCalendar and the html body.
  • 8. Re: Any interest in sending emails as Outlook appt. request???
    ApexSMC Journeyer
    Currently Being Moderated
    Thanks for this Dan. I've been generating the ics files on the server and then giving a link to download/open the file, but this seems like a smarter way to handle it.
  • 9. Re: Any interest in sending emails as Outlook appt. request???
    642198 Newbie
    Currently Being Moderated
    Hi Dan,

    I want to update employee calendar using send mail option, I tried your code which u have had posted on this thread but its not working. Is there any changes i have to made in outlook or is there any other setup which i have to do ??



    Regards,

    Mayur
  • 10. Re: Any interest in sending emails as Outlook appt. request???
    dmcghan Oracle ACE
    Currently Being Moderated
    All,

    OMG. I can't believe I forgot that... I don't know why I was not being notified of posts on this thread. I use the following for the vCalendar event:

    Here's the spec:
       FUNCTION ical_event (
          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;
    And here's the body:
       FUNCTION ical_event (
          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_lf     CHAR(2) := CHR(13)||CHR(10);
          l_retval VARCHAR2(32767);
    
       BEGIN
       
          l_retval := ''
             || 'BEGIN:VCALENDAR' || l_lf
             || 'VERSION:' || NVL(p_version,'2.0') || l_lf
             || 'PRODID:' || NVL(p_prodid,'-//This is a generic ID//NONSGML ICAL_EVENT//EN') || l_lf
             || 'CALSCALE:' || NVL(p_calscale,'GREGORIAN') || l_lf
             || 'METHOD:' || NVL(p_method,'PUBLISH') || l_lf
             || 'BEGIN:VEVENT' || 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()) || '@companyname.com' || l_lf
             || 'STATUS:NEEDS-ACTION' ||  l_lf
             || 'END:VEVENT' || l_lf
             || 'END:VCALENDAR';
          
          RETURN l_retval;
             
       END ical_event;
    I removed the exception block so make sure to add one back in!

    Regards,
    Dan

    I forgot to define g_lf. I updated it to l_lf.


    Message was edited by:
    dmcghan
  • 11. Re: Any interest in sending emails as Outlook appt. request???
    ApexSMC Journeyer
    Currently Being Moderated
    Hi again Dan,

    I tested the function and procedure you copied above with Outlook 2007 and the appointment request is not coming through. It's just an e-mail with the subject and body text but nothing else.

    It's also kicking the e-mail into the Junk Mail folder, although I'm not sure the two problems are related.

    Have you been able to test these at all with Outlook '07?

    Aaron
  • 12. Re: Any interest in sending emails as Outlook appt. request???
    dmcghan Oracle ACE
    Currently Being Moderated
    Aaron,

    Check out this blog post... Check out the example app to see if it works.

    http://www.danielmcghan.us/2008/06/icalendar-event-via-email.html

    Regards,
    Dan

    http://danielmcghan.us/
    http://sourceforge.net/projects/tapigen/
  • 13. Re: Any interest in sending emails as Outlook appt. request???
    ApexSMC Journeyer
    Currently Being Moderated
    That works...it's a problem with my code then! Thanks!
  • 14. Re: Any interest in sending emails as Outlook appt. request???
    ApexSMC Journeyer
    Currently Being Moderated
    I've compiled the latest from your blog and it's working. I've tied this into a Calendar application built in Apex so that I can fire my calendar events straight into Outlook. Very nice bit of code thanks again!
1 2 Previous Next