This content has been marked as final. Show 20 replies
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.
Ok, I'll get some code together and post it tonight...
Speak out Dan :)
I agree Dan, I would love to see it.
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...
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...
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…
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.
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 );
Good luck and happy coding.
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;
I forgot to define l_lf...
Message was edited by:
Could you post the procedure you're using to format the body of the vCalendar appointment?
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.
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.
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 ??
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:
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;
I removed the exception block so make sure to add one back in!
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 forgot to define g_lf. I updated it to l_lf.
Message was edited by:
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?
Check out this blog post... Check out the example app to see if it works.
That works...it's a problem with my code then! Thanks!
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!