Sending emails with the "APEX_MAIL" API (APEX Mail #2)

Sending emails with the "APEX_MAIL" API (APEX Mail #2)

Introduction

As mentioned in the previous blog, we want to share everything about sending emails with Oracle APEX. So continue with the second topic.

Background

If you have an APEX version older than 21.2 in your environment, email templates can only be sent with a few lines of PL/SQL code. But it is still not difficult and we will explain how to do it step by step.

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 or follow all steps up to "Create a process to send the email".

Then we need a new (or second) button to send an email by using the API. So add a button "Text and Icon (Hot)" to the "Close" position. Name the button "Send_API" and use e.g. "fa-send-o" as the icon and "Send email with API" as the label.

What we now have is an application with a form to send an email based on an email template.

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

Create a new process to send the email

Next, we need a process to send an email when we hit the "Send email with API" button.

Therefore, switch to Processes in the tree structure and create a new process. For example, name it "Send mail with API" and select the type "Execute Code".

Under Source, enter the following PL/SQL Code:

begin
    -- This procedure adds a mail to the mail queue of Oracle APEX
    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 ) || 
        '}'
    );    

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

Note: If you have a deeper look into your email template, you can see an example of using the API at the bottom. This can be very helpful with more complex templates ;-)

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_API

Let's try to send an email by clicking on the "Send email with API" button.

And again, this is how the result of the email should look like.

So, that's it...here is the link for the demo app.

References