Sending emails with attachment (APEX Mail #4)

 

As we already mentioned, here you will find everything you need to know about sending emails with Oracle APEX. Let's continue with the topic number four.

#4 Sending emails with attachment

If you want to add attachments to an email, you can do this declaratively in the APEX builder again or use the "APEX_MAIL" API. In this blog we will show you both options.

Let´s start by creating (or modifying) a demo app

To avoid having to create a new application again, let's take the demo app from the previous blog post. 

Then we need a new button to send an email with attachment. So, add a button "Text and Icon (Hot)" to the "Close" position. Name the button "SEND_ATTACHMENT" and use e.g. "fa-paperclip" as the icon and "Send email with attachment" as the label.

To send an email by using the API, add another "Text and Icon (Hot)" button at the "Close" position. Name the button "SEND_ATTACHMENT_API" and use e.g. "fa-paperclip" as an icon and "Send email with attachment (API)" as caption.

If you start the application now, it should look like this.

Add attachment to your application

For our example we upload a file to the static files which we attach to the email. However, you can also use files from existing database tables if you wish.

To do this, go to "Static application files" under "Files" of the "Shared components" section. Then click "Create File" and select a file you want to attach. 

In this example, the file "apex_logo.png" was uploaded and will be used in the further course. So adjust the filename if necessary.

Create processes to send the email with attachment

Next, we need two processes to send an email with attachment. The first one when we click on the "Send email with attachment" button and the second one when we click on the "Send email with attachment (API)" button.

Therefore, switch to Processes in the tree structure and create a new process. For example, name it "Send mail with attachment" and select the type "Send E-Mail". 

Under Settings, enter the following:

From: &APP_EMAIL.

To: &P1_TO.

Email Template: Hello World

Placeholder Values: Placeholder -> Contact / Value -> &P1_CONTACT.

Attachment SQL: 

select blob_content,

       filename,

       mime_type

  from apex_application_files

 where flow_id = :APP_ID

   and filename = 'apex_logo.png';

Send immediately: true

Success Message: Email sent

Error Message: Email sent failed!

When Button Pressed: SEND_ATTACHMENT

So that's all we need to do for the declarative solution. 

Now let's move on to the "APEX_MAIL" API process.

Create again a new process. For example, name it "Send mail with attachment (API)" and select the type "Execute Code". 

 

Under Source, enter the following PL/SQL Code: 

declare

    l_id number;

begin

    -- this functions sends an email message from a Oracle APEX application

    -- this function returns a NUMBER

    -- the NUMBER returned is the unique numeric identifier associated with the mail message

    l_id := apex_mail.send(

        -- Valid email address to which the email is sent

        p_to                 => :P1_TO,

        -- Email address from which the email is sent

        p_from               => :APP_EMAIL,

        -- Static identifier string, used to identify the shared component email template

        p_template_static_id => :P1_EMAIL_TEMPLATE,

        -- JSON string representing the placeholder names along with the values, to be substituted

        p_placeholders       => '{' ||

        '    "CONTACT":'     || apex_json.stringify( :P1_CONTACT ) ||

        '}'

    );  

    -- to add multiple attachments to a single email,

    -- APEX_MAIL.ADD_ATTACHMENT can be called repeatedly in a loop

    for rec in (

        select filename, blob_content, mime_type

          from apex_application_files

         where flow_id = :APP_ID

           and filename = 'apex_logo.png'

    )

    loop

        -- this procedure adds an attachment to an email        

        apex_mail.add_attachment(

            p_mail_id    => l_id,

            p_attachment => rec.blob_content,

            p_filename   => rec.filename,

            p_mime_type  => rec.mime_type);

    end loop;

    -- Oracle APEx stores unsent email messages in a table named APEX_MAIL_QUEUE.

    -- You can manually deliver mail messages stored in this queue to the

    -- specified SMTP gateway by invoking the APEX_MAIL.PUSH_QUEUE procedure.

    apex_mail.push_queue;

end;

Finally, enter the success and error message and the server-side condition when the process should be executed.

Success Message: Email sent

Error Message: Email sent failed!

When Button Pressed: SEND_ATTACHMENT_API

That´s it. Let's try to send an email with attachment by clicking on the "Send email with attachment" and/or "Send email with attachment (API)" button.

And this is how the result of the email should look like. As you can see, the attachment was attached to the e-mail.

And here is the link for the demo app.

 

Quellen:

• https://bit.ly/3y4zGKT