It was tested on an Oracle 11g R2 database with UTF-8 NLS_CHARACTERSET set and an Exchange smtp server.
CREATE OR REPLACE PROCEDURE send_html_email(p_sender_name VARCHAR2, -- name of person sending email p_sender_mail VARCHAR2, -- email of person sending email p_recipient VARCHAR2, -- recipient of contact email p_subject VARCHAR2, -- subject of email p_message VARCHAR2 -- message of email ) is conn UTL_SMTP.CONNECTION; v_smtp_hostname varchar(200) := 'smtp_server'; v_smtp_uname VARCHAR2(200) := 'smtp_server_uname'; v_smtp_passwd VARCHAR2(200) := 'smtp_server_password'; v_sender VARCHAR2(200) := null; CHAR_SET constant varchar(200) := 'Content-Type: text/html;charset=UTF-8' ||UTL_TCP.CRLF; MIME_VERSION constant varchar(200) := 'MIME-version: 1.0' || UTL_TCP.CRLF; CONT_ENCODING constant varchar(200) := 'Content-Transfer-Encoding: quoted-printable ' ||UTL_TCP.CRLF; BEGIN if p_message is null then return; end if; conn := utl_smtp.open_connection(v_smtp_hostname, 25); if v_smtp_uname is not null then UTL_SMTP.ehlo(conn, v_smtp_hostname); UTL_SMTP.command(conn, 'AUTH LOGIN'); UTL_SMTP.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_smtp_uname)))); UTL_SMTP.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_smtp_passwd)))); else UTL_SMTP.helo(conn, v_smtp_hostname); end if; UTL_SMTP.Helo(conn, v_smtp_hostname); UTL_SMTP.Mail(conn, p_sender_mail); UTL_SMTP.rcpt(conn, p_recipient); UTL_SMTP.OPEN_DATA(conn); UTL_SMTP.WRITE_DATA(conn, MIME_VERSION); UTL_SMTP.WRITE_DATA(conn, CHAR_SET); UTL_SMTP.WRITE_DATA(conn, CONT_ENCODING); UTL_SMTP.WRITE_DATA(conn, 'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss') || ' -0800 (GMT)' || UTL_TCP.CRLF); UTL_SMTP.write_raw_data(conn, utl_raw.cast_to_raw('Subject:' || p_subject)); UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF); -- this crlf is for 'Subject' field if p_sender_name is null then v_sender := p_sender_mail; else v_sender := '"' || p_sender_name || '" <' || p_sender_mail || '>'; end if; UTL_SMTP.write_raw_data(conn, utl_raw.cast_to_raw('From:' || v_sender)); UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF); -- this crlf is for 'From' field UTL_SMTP.WRITE_DATA(conn, 'To: ' || p_recipient || UTL_TCP.CRLF); UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF); UTL_SMTP.WRITE_RAW_DATA(conn, UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(p_message))); UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF); UTL_SMTP.CLOSE_DATA(conn); UTL_SMTP.QUIT(conn); END;
Designing HTML email is one of the key steps in email marketing. A good looking HTML email will bring you a higher open rate than a plain text message. But when a HTML email comes in a distorted format which makes it unpleasant or even unreadable, you can't expect a high response from your subscribers.
ReplyDeleteHTML email software
Great!! It helped me a lot! Thank you.
ReplyDeleteReally Great it is working supreb. Thank you for keeping the script. Thanks a lot.
ReplyDeleteThis code works well at sending emails. However, the emails can suffer from "dot stuffing". Has anyone already coded a PL/SQL solution to "dot stuffing". Basically, when the 76th character on a line begins with a '.', the period is removed.
ReplyDeleteI have the same problem - dot/period is removed when it's the 76th character. Is there any PL/SQL function in the UTL_SMTP package that offers a solution for this problem? What was your solution?
Delete(make sure your l_buffer raw(...) is large enough)
Deletel_buffer := UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(p_message));
l_buffer := replace(l_buffer,'D0D0A2E','D0D0A2E2E');--dot stuffing
utl_smtp.write_raw_data(g_connection, l_buffer);
Thanks a lot :)
ReplyDelete