How to send an email from your ORACLE APEX app

Sending emails from your APEX application using APEX Email Templates is super simple.

The project specification was to add buttons to a form to allow users to press a button to email students whether they have been successful or unsuccessful in their scholarship application.

This is how we did it.  The application form is a standard APEX form where we will add a button to the form that when pressed will send an email;

In this example I will walk you through the creation of the rejection email.  First, we created a new email template for rejection emails with the name REJECTION;

with the following identification;

To send images in the email ensure they are hosted on a publicly available site.  In this case, I hosted the University of Glasgow logo on my Google Drive and put it in the header of the email using the HTML IMG tag;

The body is standard HTML using substitution strings (Special substitution strings available within a template are denoted by the number symbol (#). For example: #ABC#)

Student Name: #FIRST_NAME# #LAST_NAME#<br>

    <br>

    16 September 2021

    <br><br>

    Dear #FIRST

In the Advanced section press 'Load Default HTML' which loads the default HTML that styles the email, you can change this;

Now we create the button on the page with an 'Execute Server-side Code' Dynamic Action;

This is where the code reads the items on the page to send to the template which uses them as special substitution strings.  We are sending 5 items to the template where they get referenced in the HTML body, for example to reference FIRST_NAME in the body HTML we use #FIRST_NAME#. 

p_template_static_id is the name of the email template, REJECTION.

begin

    apex_mail.send (

        p_to                 => apex_json.stringify( :P901_EMAIL_ADDRESS) ,

        p_template_static_id => 'REJECTION',

        p_placeholders       => '{' ||

        '    "FIRST_NAME":' || apex_json.stringify( :P901_FIRST_NAME ) ||

        ',   "LAST_NAME":' || apex_json.stringify( :P901_LAST_NAME ) ||

        ',   "STUDENT_ID":' || apex_json.stringify( :P901_STUDENT_ID ) ||

        ',   "APPLICATION_NUMBER":' || apex_json.stringify( :P901_APPLICATION_NUMBER ) ||

         ',  "TEXT_SCHOLARSHIP_NAME":' || apex_json.stringify( :P901_SCHOLARSHIP_NAME) ||

        '}' );

        apex_mail.push_queue;

end;

Note.  You must set Maintain Session State to 'Per Session (Disk)' for any form values you are sending in the PLSQL;

To finish it off, add another Dynamic Action to the button to alert the user an email has been sent.

That's it, super easy and because the HTML is in the email template it's a nice separation of model/view so easy to maintain going forward.

Conditional HTML

If you have conditional requirements, for example you do not want an ordered list element to appear if there is no value, you can do this in your HTML body;

#RESIDENCY_COMMENT_BEGIN!RAW#

<li>#RESIDENCY#</li>

#RESIDENCY_COMMENT_END!RAW#

In the PLSQL, declare 2 variables;

  l_residency_comment_begin VARCHAR2(100);

  l_residency_comment_end   VARCHAR2(100);

If we do not have a value for the agent's email address, give them the following values;

IF :P901_AGENT_EMAIL_ADDRESS is null THEN

      l_residency_comment_begin := '<!--';

      l_residency_comment_end := '-->';

END IF;

send them to the email template;

',"RESIDENCY_COMMENT_BEGIN":' || apex_json.stringify(l_residency_comment_begin) ||

',"RESIDENCY_COMMENT_END":'   || apex_json.stringify(l_residency_comment_end) ||    

The full PLSQL is;

DECLARE

    l_residency_comment_begin VARCHAR2(100);

    l_residency_comment_end   VARCHAR2(100);

begin

IF :P901_AGENT_EMAIL_ADDRESS is null THEN

      l_residency_comment_begin := '<!--';

      l_residency_comment_end := '-->';

END IF;

apex_debug.enable( apex_debug.c_log_level_engine_trace);

    apex_mail.send (

        p_to                 => 'david.lang@glasgow.ac.uk',

        p_template_static_id => 'GENERICACCEPTANCE',

        p_placeholders       => '{' ||

        ' "FIRST_NAME":' || apex_json.stringify( :P901_FIRST_NAME ) ||

        ',"LAST_NAME":' || apex_json.stringify( :P901_LAST_NAME ) ||

        ',"STUDENT_ID":' || apex_json.stringify( :P901_STUDENT_ID ) ||

        ',"APPLICATION_NUMBER":' || apex_json.stringify( :P901_APPLICATION_NUMBER ) ||

        ',"TEXT_SCHOLARSHIP_NAME":' || apex_json.stringify( :P901_SCHOLARSHIP_NAME) ||

        ',"RESIDENCY":' || apex_json.stringify( :P901_AGENT_EMAIL_ADDRESS) ||  

        ',"RESIDENCY_COMMENT_BEGIN":' || apex_json.stringify(l_residency_comment_begin) ||

        ',"RESIDENCY_COMMENT_END":'   || apex_json.stringify(l_residency_comment_end) ||     

        '}' );

        apex_mail.push_queue;

end;

I did think the easy way of doing this would be to add this CSS, but this does not work.  If anybody does know how to do it with CSS only, a comment would be great.

li:empty {

  display: none;

}