Appending Image to The Email using Email Template Feature in Oracle APEX

Overview

This document talks about how to Customize your html tags to show your images inline in Email body using Email Template feature in Oracle Apex.

Technologies and Tools Used

The following technologies have been used to implement this requirement.

SQL
Oracle Apex
HTML

Use Case

Assume that there is a requirement to send images inline in the Email body using Oracle Apex Email template for some business use cases. The Main concerns with respect to images is that it must be accessible to the Email clients of different users. So, we have html tags to handle that inline content view along with some workaround with respect to oracle Apex.

Architecture

Following steps explains in detail,

Step 1: Create a sample template to show an image in your body of the email using email template feature in oracle Apex.

Step 2: Upload an image in static application file and using html tag refer it in Email Body.

Go back to your template and edit it to add image tage.

For Example,

Step 3: Create a PL/SQL Process and Button to Send Email From Oracle Apex Page.

Sample Code Given As below,

Code:

DECLARE L_EMAIL VARCHAR2(2000);
l_temp_static_ID VARCHAR2(100):= ‘EXAMPLE1’;
/* template Static Id*/
l_id number;
l_file_name VARCHAR2(100);
l_blob blob;
l_mime VARCHAR2(100);
BEGIN     l_id := apex_mail.send (
p_to                 => ‘aishwarya.rajagopal@doyensys.com’,
p_template_static_id => l_temp_static_ID,
p_placeholders       => ‘{‘ ||         ‘    “SERVICE_USER”:’         || apex_json.stringify(‘Aishwarya’) ||         ‘   ,”SERVICE_NAME”:’         || apex_json.stringify(‘IT Services’) ||         ‘   ,”OUTAGE_START”:’         || apex_json.stringify(‘Today 9.AM IST’) ||         ‘   ,”OUTAGE_DURATION”:’      || apex_json.stringify(‘Today 12 PM IST’) ||         ‘   ,”OUTAGE_TIMEFRAME”:’     || apex_json.stringify(‘3 Hours’) ||         ‘   ,”OUTAGE_REASON”:’        || apex_json.stringify(
‘There is Maintenance work going on Network Services’
) ||         ‘   ,”OUTAGE_CONTACT”:’       || apex_json.stringify(‘Techsuppport.com’) ||         ‘   ,”SERVICE_TEAM”:’         || apex_json.stringify(‘TechnTeam’) ||         ‘   ,”MY_APPLICATION_LINK”:’ || apex_json.stringify(
apex_mail.get_instance_url || apex_page.get_url(“Some URL”)
) ||         ‘}’
);

select
filename,
blob_content,
mime_type into l_file_name,
l_blob,
l_mime
from
apex_application_files
where
flow_id = : APP_ID
and filename = ‘illustration-characters-fixing-cogwheel_53876-40796.avif’;
/* Image attachment */
apex_mail.add_attachment(
p_mail_id => l_id,
p_attachment => l_blob,
p_filename => l_file_name,
p_mime_type => l_mime
);
APEX_MAIL.PUSH_QUEUE;
END;

That’s it, On click of Send Email Button, The Mail has been send along with the image appended.

Screenshot

Conclusion

There are some concerns in displaying images inline in different mail servers like Gmail, Outlook etc..,
We have other options like using Rest API and Application process which will help you to achieve the Similar outcome.

Aishwarya Rajagopal