Skip to main content
Solved

Attaching Document in a UTL_SMTP email


Forum|alt.badge.img+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. 

Best answer by hhy38

@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;

 

 

View original

6 replies

hhy38
Superhero (Customer)
Forum|alt.badge.img+16
  • Superhero (Customer)
  • 319 replies
  • October 23, 2023

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.

 

 

 

Forum|alt.badge.img+1
  • Author
  • Do Gooder (Customer)
  • 4 replies
  • October 23, 2023
hhy38 wrote:

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? 

 


hhy38
Superhero (Customer)
Forum|alt.badge.img+16
  • Superhero (Customer)
  • 319 replies
  • October 23, 2023

@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.

 

 


hhy38
Superhero (Customer)
Forum|alt.badge.img+16
  • Superhero (Customer)
  • 319 replies
  • Answer
  • October 23, 2023

@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;

 

 


Forum|alt.badge.img+1
  • Author
  • Do Gooder (Customer)
  • 4 replies
  • October 23, 2023
hhy38 wrote:

@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!


hhy38
Superhero (Customer)
Forum|alt.badge.img+16
  • Superhero (Customer)
  • 319 replies
  • October 23, 2023

@nstacktk Pleasure. You are welcome.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings