TM-APEX: Sending emails with the "APEX

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

#2 Sending emails with the "APEX_MAIL" API

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