Solved

Attaching Document in a UTL_SMTP email

  • 23 October 2023
  • 6 replies
  • 112 views

Badge +1

I found code on here that allowed me to attach a document to an email. This worked fine but it uses COMMAND_SYS: 

PROCEDURE EMAIL_DOC(mailto_ varchar2,mailfrom_ varchar2,doc_class_ varchar2,doc_no_ varchar2,doc_rev_ varchar2,doc_sheet_ varchar2,title_ varchar2,subject_ varchar2,message_ varchar2) IS
file_name_ VARCHAR2(2000);
file_names_ VARCHAR2(2000);
doc_type_ VARCHAR2(2000);
f_ VARCHAR2(2000);
error_msg_ VARCHAR2(32000);
rec_ Plsqlap_Record_API.type_record_;
doc_ref_ VARCHAR2(32000);

CURSOR get_doc_reference IS
SELECT doc_object_desc
FROM DOC_REFERENCE_OBJECT
WHERE doc_class = doc_class_
AND doc_no = doc_no_;
BEGIN

OPEN get_doc_reference;
FETCH get_doc_reference INTO doc_ref_;
IF get_doc_reference%FOUND THEN
EDM_FILE_API.REFERENCE_EXIST(f_, doc_class_, doc_no_, doc_sheet_, doc_rev_,'VIEW');
IF f_ = 'TRUE' THEN
doc_type_ := 'VIEW';
ELSE
doc_type_ := 'ORIGINAL';
END IF;

file_name_ := EDM_FILE_API.GET_NEW_LOCAL_FILE_NAME(doc_class_, doc_no_, doc_sheet_, doc_rev_,doc_type_,'1');
BATCH_TRANSFER_HANDLER_API.Download_From_DB(error_msg_, doc_class_, doc_no_, doc_sheet_, doc_rev_, doc_type_, 1, file_name_);
DBMS_OUTPUT.PUT_LINE('Error: '||error_msg_);

IF(error_msg_ is not null) THEN
COMMAND_SYS.MAIL(
FROM_USER_NAME_ => mailfrom_,
TO_USER_NAME_ => mailto_,
CC_ => 'HARDCODED_EMAIL',
TEXT_ => message_,
SUBJECT_ =>subject_);

ELSE
COMMAND_SYS.MAIL(
FROM_USER_NAME_ => mailfrom_,
TO_USER_NAME_ => mailto_,
CC_ => 'HARDCODED_EMAIL',
TEXT_ => message_,
SUBJECT_ =>subject_,
ATTACH_ => file_name_ );
END IF;

END IF;
CLOSE get_doc_reference;
END EMAIL_DOC;

I would like to use UTL_SMTP instead but I can not seem to get it working for this. My main reasons for this is that I need to have the address come from different users and HTML formatted emails are preferrable for formatting. 

icon

Best answer by hhy38 23 October 2023, 16:11

View original

6 replies

Userlevel 5
Badge +14

Hi @nstacktk 

 

You can use HTML in the body. Just set the Content-Type of your Mail Sender. And you can give permission to users. Don’t forget to add the “ifsapp.” prefix before APIs and Views.

 

 

 
Badge +1

Hi @nstacktk 

 

You can use HTML in the body. Just set the Content-Type of your Mail Sender. And you can give permission to users. Don’t forget to add the “ifsapp.” prefix before APIs and Views.

 

 

 

Hi @hhy38 ,

Thanks for your reply. My only issue is that we have a “DEFAULT_MAIL_SENDER” set and this does not seem to get overridden when I run the script. Is there a way around that without removing the default? 

 

Userlevel 5
Badge +14

@nstacktk You can create a new sender. Just click RMB on Connector Senders and create a new one.  Then, Set the mail procedures' the mail_sender parameter.

 

 

Userlevel 5
Badge +14

@nstacktk 

 

There is an example below. I hope it will help you.

 

BEGIN
ifsapp.command_sys.mail(sender_ => 'IFSAPP',
from_ => 'IFSAPP',
to_list_ => 'hasanyucel34@gmail.com',
subject_ => 'Test Mail',
text_ => 'HTML <b>TEST</b> Mail! <br><br> Lorem Ipsum <br><br> Hasan Hüseyin YÜCEL',
attach_ => '',
mail_sender_ => 'NEW_MAIL_SENDER1'
);
END;

 

 

Badge +1

@nstacktk

 

There is an example below. I hope it will help you.

 

BEGIN
ifsapp.command_sys.mail(sender_ => 'IFSAPP',
from_ => 'IFSAPP',
to_list_ => 'hasanyucel34@gmail.com',
subject_ => 'Test Mail',
text_ => 'HTML <b>TEST</b> Mail! <br><br> Lorem Ipsum <br><br> Hasan Hüseyin YÜCEL',
attach_ => '',
mail_sender_ => 'NEW_MAIL_SENDER1'
);
END;

 

 

That all works, thank you so much!

Userlevel 5
Badge +14

@nstacktk Pleasure. You are welcome.

Reply