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

4 Responses to Oracle send mail using UTL_SMTP (to multiple parties Cc)

  1. NT says:

    How you can send the email without login ?

    • windows7bugs says:

      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

  2. Margarett says:

    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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: