Hello,
I have a stored procedure to send e-mails using UTL_SMTP. It's prepared to accept a list of e-mail addresses in the "To" and the "CC" parameter.
And functionally, it works as expected: it sends the e-mail to the list provided.
The issue is, when the recipients open the e-mail, they only see the first e-mail address on the list, and if they want to reply-all they won't be able to do it, because the rest of the e-mail addresses does not display.
For example, if I use the following code:
BEGIN
IVC_SEND_MAIL('no-reply@mydomain.com', 'alex@mydomain.com', 'one@mydomain.com;two@mydomain.com;three@mydomain.com',NULL, 'Subject', 'Body');
END;
It will send the e-mail to alex, one, two and three; but, the e-mail will show the header of the e-mail as:
To: alex@mydomain.com
Cc: one@mydomain.com
and it won't show two@mydomain.com nor three@mydomain.com
So, the question is: what am I missing? How can I make sure the whole distribution list is displayed?
Thank you.
This is the code for the procedure I'm using:
CREATE OR REPLACE PROCEDURE IVC_SEND_MAIL (v_from in varchar2, v_to in varchar2, v_cc in varchar2, v_bcc in varchar2, v_subj in varchar2,v_body in varchar2)
IS
v_crlf VARCHAR2(2) := CHR( 13 ) || CHR( 10 );
v_mesg VARCHAR2(10000);
v_conn utl_smtp.connection;
v_email_svr VARCHAR2(4) := 'mailserver';
v_port NUMBER := 25;
v_cc_msg VARCHAR2(2000);
BEGIN
v_conn := utl_smtp.open_connection( v_email_svr, v_port );
utl_smtp.helo( v_conn, v_email_svr );
utl_smtp.mail( v_conn, v_from);
FOR x IN (SELECT LEVEL AS id, REGEXP_SUBSTR(v_to, '[^;]+', 1, LEVEL) AS TO_EMAIL_NAME FROM DUAL
CONNECT BY REGEXP_SUBSTR(v_to, '[^;]+', 1, LEVEL) IS NOT NULL) LOOP
utl_smtp.Rcpt(v_conn,x.TO_EMAIL_NAME);
END LOOP;
IF v_cc IS NOT NULL THEN
FOR x IN (SELECT LEVEL AS id, REGEXP_SUBSTR(v_cc, '[^;]+', 1, LEVEL) AS CC_EMAIL_NAME FROM DUAL
CONNECT BY REGEXP_SUBSTR(v_cc, '[^;]+', 1, LEVEL) IS NOT NULL) LOOP
utl_smtp.Rcpt(v_conn,x.CC_EMAIL_NAME);
END LOOP;
v_cc_msg := 'CC: ' || v_cc || v_crlf;
ELSE
v_cc_msg := '';
END IF;
IF v_bcc IS NOT NULL THEN
FOR x IN (SELECT LEVEL AS id, REGEXP_SUBSTR(v_bcc, '[^;]+', 1, LEVEL) AS BCC_EMAIL_NAME FROM DUAL
CONNECT BY REGEXP_SUBSTR(v_bcc, '[^;]+', 1, LEVEL) IS NOT NULL) LOOP
utl_smtp.Rcpt(v_conn,x.BCC_EMAIL_NAME);
END LOOP;
END IF;
v_mesg := 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || v_crlf ||
'From:' || v_from || v_crlf ||
'Subject: ' || v_subj || v_crlf ||
'To: ' || v_to || v_crlf ||
v_cc_msg ||
'Mime-Version: 1.0;' || v_crlf ||
'Content-Type: text/html; charset="ISO-8859-1";' || v_crlf ||
'' || v_crlf ||
''|| v_crlf||v_body;
utl_smtp.data( v_conn, v_mesg );
utl_smtp.quit( v_conn );
END;
/
You need a CC line for *each* recipient, hence something like
IF v_cc IS NOT NULL THEN
FOR x IN (SELECT LEVEL AS id, REGEXP_SUBSTR(v_cc, '[^;]+', 1, LEVEL) AS CC_EMAIL_NAME FROM DUAL
CONNECT BY REGEXP_SUBSTR(v_cc, '[^;]+', 1, LEVEL) IS NOT NULL) LOOP
utl_smtp.Rcpt(v_conn,x.CC_EMAIL_NAME);
v_cc_msg := 'CC: ' || x.CC_EMAIL_NAME || v_crlf;
END LOOP;
ELSE
v_cc_msg := '';
END IF;