Skip to Main Content
  • Questions
  • Sending an e-mail with UTL_SMTP to multiple recipients shows only the first recipient in the list

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alejandro.

Asked: October 27, 2020 - 6:17 am UTC

Answered by: Connor McDonald - Last updated: October 29, 2020 - 2:46 am UTC

Category: SQL - Version: 12g

Viewed 100+ times

You Asked

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;
/

and we said...

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; 

and you rated our response

  (1 rating)

Reviews

Excellent and prompt response

October 28, 2020 - 4:28 pm UTC

Reviewer: Alejandro Aguilar from El Centro, CA USA

Thank you thank you thank you!
I researched so much and couldn't find the answer to my issue.
The solution was so simple and yet effective and exactly what I needed.

The only change I made to the proposed solution was to concatenate the list of addresses.

v_cc_msg := v_cc_msg || 'CC: '  || x.CC_EMAIL_NAME || v_crlf;


Again, thank you so much!
Connor McDonald

Followup  

October 29, 2020 - 2:46 am UTC

Glad we could help

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.