Hi,

I am using utl_smtp for sending mail in my function now i want to attach the text file with every mail along with the message.

I have following problem i am able to get only message(i.e.body of email) / attachment but not both.

i have forwarded the code please suggest me how do i do that using the utl_smtp.


Regards
Raj
The code is as follows,

FUNCTION deliver (
in_from IN VARCHAR2 DEFAULT NULL,
in_to IN VARCHAR2 DEFAULT NULL,
in_subject IN VARCHAR2 DEFAULT NULL,
in_content IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2
IS
mailhost VARCHAR2(30) := 'cob353.dn.net';
-- mailhost VARCHAR2(30) := 'mail.finhost.fi';
mail_conn utl_smtp.connection;
response utl_smtp.reply;
status VARCHAR2(2000):='MAIL ID FORMAT ERROR';

mesg varchar2(4000);
attach1 varchar2(4000);
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
BEGIN

IF instr(in_to,'@',1,1) > 0 AND instr(in_from,'@',1,1) > 0 THEN
status := 'OK';
mail_conn := utl_smtp.open_connection(mailhost, 25);

response:=utl_smtp.helo(mail_conn, mailhost);
IF response.code<>250 AND response.code<>221 THEN
status:='error';
END IF;

response:=utl_smtp.mail(mail_conn, in_from);
IF response.code<>250 AND response.code<>221 THEN
status:='error';
END IF;

response:=utl_smtp.rcpt(mail_conn,in_to);
IF response.code<>250 AND response.code<>221 AND response.code<>421 THEN
status:='error';
END IF;

if v_filename is null then
response:=utl_smtp.data(mail_conn, 'Subject: '||in_subject||chr(10)||in_content);
IF response.code<>250 AND response.code<>221 THEN
status:='error';
END IF;
else

--filehandle:=UTL_FILE.FOPEN(v_path,v_filename,'r');
attach1:='Client Name|Address|Product|Campaign Name|Publication Date|Insertion Order No|Media Name|Size/Color of Ad|List Price|Discount|Net Price|Fee';
mesg:= 'Subject: '|| in_subject || ': Completed ' ||in_content|| crlf ||
'Your query has been completed. The output can be viewed below. '||crlf||''||crlf||
'MIME-Version: 1.0'||crlf||
'Content-Type: text/plain'||crlf||
'Content-Transfer-Encoding: 7bit'||crlf||
'Content-Disposition: attachment; filename=output.log'||crlf||''|| crlf ||
'Your query results have exc
eeded the maximum output size. The first block is below. The entire output can b
e viewed here.'||
attach1; -- Data to appear in attachment.

response:=utl_smtp.data(mail_conn,mesg);
IF response.code<>250 AND response.code<>221 THEN
status:='error';
END IF;
end if;


response:=utl_smtp.quit(mail_conn);
IF response.code<>250 AND response.code<>221 THEN
status:='error';
END IF;
END IF;

RETURN status;

EXCEPTION
WHEN OTHERS THEN
-- Handle cookie exceptions
IF SQLCODE=-20101 OR SQLCODE=-20102 THEN
com360.err_msg.print(in_address=>'login.main_index',in_errmsg=>'User sessio error, please login ('||SQLCODE||')');
ELSE
com360engine.logs.error_log(in_sqlcode=>SQLCODE,in_errm=>SQLERRM);
END IF;
END;