Email Template Option in Oracle APEX 18.2

An interesting feature on Oracle APEX 18.2 and above is the EMAIL Template, It can be found under 

Shared Components => Other Components

Fig 1: Email Templates

It consists of two major parts,

1.     The Template Details.

2.     PL/SQL Procedure.

1. The Template Details:

It has four sections as below,

• Header
• Body
• Footer
• HTML Code Content

Get started by loading  "Sample Email Templates"  (option available @ right hand side).

Fig 2: Sample Templates

Fig 3&4: Template Options

The hash tags like #ORDER_NUMBER#, #CUSTOMER_NAME# are substitutions.

Once you create the template, It can be available under the option, 

Shared Components => Other Components => Email Templates

Fig 5: List of Email Templates

Click here to understand the concept of apex_mail.send API.

2. PL/SQL Procedure:

Sample API Usage:

BEGIN

    apex_mail.send (

        p_to                 => email_address_of_user,

        p_template_static_id => 'ORDERDETAILS',

        p_placeholders       => '{' ||

        '    "CUSTOMER_NAME":'           || apex_json.stringify( :BIND_VARIABLE1) ||

        '   ,"ITEMS_ORDERED":'           || apex_json.stringify( :BIND_VARIABLE2 ) ||

        '   ,"MY_APPLICATION_LINK":'     || apex_json.stringify( :BIND_VARIABLE3) ||

        '   ,"ORDER_DATE":'              || apex_json.stringify( :BIND_VARIABLE4) ||

        '   ,"ORDER_NUMBER":'            || apex_json.stringify( :BIND_VARIABLE5) ||

        '   ,"ORDER_TOTAL":'             || apex_json.stringify( :BIND_VARIABLE6) ||

        '   ,"ORDER_URL":'               || apex_json.stringify( :BIND_VARIABLE7) ||

        '   ,"SHIPPING_ADDRESS_LINE_1":' || apex_json.stringify( :BIND_VARIABLE8) ||

        '   ,"SHIPPING_ADDRESS_LINE_2":' || apex_json.stringify( :BIND_VARIABLE9) ||

        '   ,"SHIP_TO":'                 || apex_json.stringify( :BIND_VARIABLE10) ||

        '}' );

END;

To send an nice email to the customer, then you just create PL/SQL process on apex page and call the below procedure with necessary inputs.

Example: Just run it.

BEGIN

    apex_mail.send (p_to                 => 'karkuvelraja.t@xxyyy.com',

                    p_template_static_id => 'ORDERDETAILS',

                    p_placeholders       => '{' ||

                    '    "CUSTOMER_NAME":'           || apex_json.stringify( 'Karkuvelraja Thangamariappan' ) ||

                    '   ,"ITEMS_ORDERED":'           || apex_json.stringify( 5 ) ||

                    '   ,"MY_APPLICATION_LINK":'     || apex_json.stringify( 'https://www.itchotels.in/hotels/chennai/itcgrandchola/offers.html?source=google&medium=cpc&campaign=ITC_EV_INT_UAE_Brand_BMM&gclid=EAIaIQobChMIqt3Q38ON6AIVStreCh2FCApDEAAYASAAEgJ9DfD_BwE' ) ||

                    '   ,"ORDER_DATE":'              || apex_json.stringify( '09-MAR-2020' ) ||

                    '   ,"ORDER_NUMBER":'            || apex_json.stringify( 'ORD090320200015478' ) ||

                    '   ,"ORDER_TOTAL":'             || apex_json.stringify( 2584.50 ) ||

                    '   ,"ORDER_URL":'               || apex_json.stringify( 'https://www.itchotels.in/hotels/chennai/itcgrandchola/offers.html?source=google&medium=cpc&campaign=ITC_EV_INT_UAE_Brand_BMM&gclid=EAIaIQobChMIqt3Q38ON6AIVStreCh2FCApDEAAYASAAEgJ9DfD_BwE' ) ||

                    '   ,"SHIPPING_ADDRESS_LINE_1":' || apex_json.stringify( 'XXX Solutions Pvt LTD, Sholinganallur' ) ||

                    '   ,"SHIPPING_ADDRESS_LINE_2":' || apex_json.stringify( 'Chennai, Tamil Nadu' ) ||

                    '   ,"SHIP_TO":'                 || apex_json.stringify( 'Shanmuga Lakshmi' ) ||

                    '}',

                    p_application_id    => 31586

                   );

     apex_mail.push_queue();      -- To push all the mails from queue

END;

Note: To avoid the below error, you must pass APP ID (When you are executing this procedure from outside the application).

Output: 

That's it. Happy APEXing!!!...