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