——————————————————————————————
Below procedure shows how to send email to mutliple recipents
CREATE OR REPLACE PROCEDURE XXCUST.XXX_SMTP_MAIL_NOTIF_PRC(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_smtp_server IN VARCHAR2 default ‘mail.yourcompany.com’, — ask your dba for this server name and port
p_smtp_port IN VARCHAR2 default ’25’,
p_mail_sender IN VARCHAR2 default ‘donotreply@staging.doyensys.com’ )
IS
p_subject varchar2(1000);
l_conn utl_smtp.connection;
l_row_found boolean := false ;
l_mesg varchar2(32767);
l_db_name varchar2(10) := substr(sys.database_name,1,7); — database name
c_mail_boundary constant varchar2(255) default ‘–ABCDEFGHIJKL12345–‘;
c_mail_footer constant varchar2(1000) := ‘<p><b>Note:</b> For any Help please raise IT ticket to Support team.<br> ************* This is auto generated email from the system. Please do not reply *************</p></body></html>’;
crlf constant varchar2(2):= chr(13) || chr(10);
TYPE email_list IS TABLE OF VARCHAR2(240); — collection type definition
rec_list email_list; — collection variable to hold multiple recipients
BEGIN
p_subject :=’Email Subject Here’;
select description bulk collect into rec_list from ar_lookups where lookup_type=’NOTIF_EMAIL_LIST’ and ENABLED_FLAG=’Y’ ; — for getting recipients email id s from database table
FOR i in (
select col1,col2,col3,col4,col5 — if you have email body text from database table
FROM table1,table2
WHERE table1.col6=table2.col5 –joing conditions here
)
LOOP
if not l_row_found then — this will execute one time for each procedure call
l_conn := utl_smtp.open_connection (host => p_smtp_server, port => p_smtp_port ) ;
utl_smtp.helo(l_conn, p_smtp_server);
utl_smtp.mail(l_conn, p_mail_sender );
–utl_smtp.rcpt(l_conn, p_recip);
l_mesg := l_mesg ||’Date: ‘|| TO_CHAR(SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’) ||crlf;
l_mesg := l_mesg ||’To: ‘;
for i in rec_list.FIRST .. rec_list.last loop — if you have mutliple email recipients then you can use this for loop
utl_smtp.Rcpt(l_conn,rec_list(i));
if i < rec_list.last then
l_mesg := l_mesg ||rec_list(i)||’,’;
else
l_mesg := l_mesg ||rec_list(i)||crlf;
end if;
end loop;
l_mesg := l_mesg ||’From: ‘|| p_mail_sender||crlf;
l_mesg := l_mesg ||’Subject: ‘||p_subject||’-‘||l_db_name ||crlf;
— l_mesg := l_mesg || ‘Reply-To: ‘ || c_mail_from || crlf;
l_mesg := l_mesg ||’MIME-Version: 1.0’|| crlf;
l_mesg := l_mesg || ‘Content-Type: multipart/alternative; boundary=’ ||chr(34) || c_mail_boundary || chr(34) || crlf;
l_mesg := l_mesg || ‘content-type: text/html;’ || crlf || crlf;
l_mesg := l_mesg || ‘<html><head><title>’||p_subject||'</title></head><body>’;
utl_smtp.open_data(l_conn);
utl_smtp.write_data(l_conn,l_mesg);
utl_smtp.write_data(l_conn,'<p> This is to notify that the Dearchive program is completed and below is the summary.<br></p>’);
utl_smtp.write_data(l_conn,'<table cellspacing=”0″ cellpadding=”0″ border=”1″>’);
utl_smtp.write_data(l_conn,'<tr bgcolor=”BLUE”>’||
‘<td width=”200″><font face=”Calibri”>’||'<b>Col_HDR1</b>’||
‘<td width=”100″ align=”center”><font face=”Calibri”>’||'<b>Col_HDR2</b>’||
‘<td width=”180″ align=”center”><font face=”Calibri”>’||'<b>Col_HDR3</b>’||
‘<td width=”300″ align=”center”><font face=”Calibri”>’||'<b>Col_HDR4</b>’||
‘<td width=”400″><font face=”Calibri”>’||'<b>Col_HDR5</b>’||
‘</tr>’||crlf
);
l_row_found := true;
end if;
utl_smtp.write_data(l_conn,'<tr>’||
‘<td width=”200″><font face=”Calibri”>’||i.col1||
‘<td width=”100″ align=”center”><font face=”Calibri”>’||i.col2||
‘<td width=”180″ align=”center”><font face=”Calibri”>’||i.col3||
‘<td width=”300″ align=”center”><font face=”Calibri”>’||i.col4||
‘<td width=”400″><font face=”Calibri”>’||i.col5||
‘</tr>’||crlf
);
end loop;
if l_row_found then
utl_smtp.write_data(l_conn,'</table> ‘);
if p_mail_footer is null then
utl_smtp.write_data(l_conn,c_mail_footer);
else
utl_smtp.write_data(l_conn,p_mail_footer);
end if;
utl_smtp.close_data(l_conn);
utl_smtp.quit(l_conn);
end if;
–DBMS_OUTPUT.PUT_LINE(‘At END’);
exception
when others then
retcode:=2;
errbuf:=’error in procedure’;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’Error occured while running this program, please check LOG file for Error Details’);
FND_FILE.PUT_LINE(FND_FILE.LOG,’ Exception Occured , SQL ERROR MESSAGE :’||SQLERRM);
begin utl_smtp.quit(l_conn);
exception when others then
FND_FILE.PUT_LINE(FND_FILE.LOG,’Exception Occured While utl_smtp.quit() , SQL ERROR MESSAGE :’||SQLERRM);
end;
end XXX_SMTP_MAIL_NOTIF_PRC;
/
