Oracle send mail using UTL_SMTP (to multiple parties Cc)

declare
v_From      VARCHAR2(80) := 'abc@xyz.com';
v_Recipient VARCHAR2(80) := 'xyz@xyz.com';
v_Subject   VARCHAR2(80) := 'Checking Cc';
v_Mail_Host VARCHAR2(50) := 'mail.xyz.com';
v_Mail_Conn utl_smtp.Connection;

name_array   DBMS_SQL.varchar2_table;

crlf        VARCHAR2(2)  := chr(13)||chr(10);
CC_parties varchar2(2000);

begin

--populate from table-Cc parties
name_array(1) :=  'def@xyz.com';
name_array(2) :=  'jkl@xyz.com';
name_array(3) :=  'elg@xyz.com';

v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);


utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
FOR i IN name_array.FIRST .. name_array.LAST
LOOP
CC_parties := CC_parties||';'|| name_array(i);
utl_smtp.Rcpt(v_Mail_Conn, name_array(i));
END LOOP;

utl_smtp.Data(v_Mail_Conn,
'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: '   || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: '     || v_Recipient || crlf ||
'Cc: '     || CC_parties|| crlf ||
'Content-Type: text/html;' ||crlf ||
'New Mail from Procedure');
utl_smtp.Quit(v_mail_conn);
end;

Recvd_Mail

(Edited Image)

Hope you enjoy this.

Regards,

Admin

Advertisements

5 thoughts on “Oracle send mail using UTL_SMTP (to multiple parties Cc)

    1. This package works perfectly while you are authenticated by the SMTP server (incase if your company uses one the authentication tokens are in place). On the other hand, you must pass in all the user authentication details to your mail server (Eg: Gmail)

      regards,

      Admin

  1. Margarett

    Thanks for this sample. Took me awhile to see the 2 steps needed. utl_smtp.Rcpt for each receipt & to_party variable with ” user ;user ” for the to: . Sometimes too close to forest to see the trees.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.