Introduction

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.

Background

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.