Including an image from APEX Static Application Files in an email

Some people use APEX Office Print (AOP) to send out HTML emails based on a custom HTML template they created. Today we got a question in our AOP support inbox how to include images in those emails.

In an AOP Template, you can use a tag {%image} and AOP will replace that tag with a base64 encoded image. This works great when you open the resulting HTML file in a browser, but when using emails it's not so great as some email clients don't support base64 encoded images e.g. Outlook.

That the images don't always work is not really an AOP issue, it's more an issue with HTML emails.
Here's a good read on supporting images in HTML emails.

In short, it's better to include an URL to the image as that is supported by most, if not all, email clients. Typically the images are stored in a BLOB column in a table or people want to reference an image that they uploaded in APEX > Shared Components > Static Application Files.

This blog post shows a technique to reference an image stored in the database through a URL.

1. Create a new Public Page in an Oracle APEX Application e.g. Page 22

2. Create a region on the page

3. Create a hidden item on the page e.g. P22_FILE_NAME

4. Create a Before header Process "Retrieve image from DB" with following PL/SQL Code:

declare
  l_file_name     apex_application_static_files.file_name%type;
  l_mime_type     apex_application_static_files.mime_type%type;
  l_file_charset  apex_application_static_files.file_charset%type;  
  l_file_content  apex_application_static_files.file_content%type;
begin
  select nvl(file_name, 'output') as file_name, 
         nvl(mime_type, 'application/octet-stream') as mime_type, 
         nvl(file_charset, 'utf-8') as file_charset, 
         file_content
    into l_file_name, 
         l_mime_type, 
         l_file_charset, 
         l_file_content  
    from apex_application_static_files 
   where application_id = :APP_ID 
     and file_name      = :P22_FILE_NAME;
  
  sys.htp.flush;
  sys.htp.init;
  owa_util.mime_header(l_mime_type, false);
  sys.htp.p('Content-length:'||dbms_lob.getlength(l_file_content));
  sys.htp.p('Content-Disposition:attachment; filename="'||l_file_name||'"; '||'filename*='||l_file_charset||''''''||l_file_name);
  owa_util.http_header_close;
  wpg_docload.download_file(l_file_content);
  apex_application.stop_apex_engine;

exception
  when no_data_found
  then
    sys.htp.p('');
    apex_application.stop_apex_engine;
end;  

5. That's it.

Now you can reference the image with the following URL: https://my_server/ords/f?p=MY_APP:22:0::::P22_FILE_NAME:my_image.png

If you want to test the functionality in an email, go to SQL Workshop >SQL Commands and use the following code:

begin
  apex_mail.send(
    p_to        => 'my@email.com',
    p_from      => 'my@email.com',
    p_subj      => 'Mail from APEX with inline image',
    p_body      => 'Please review the email in html.',
    p_body_html => 'Here the inline image: <br/> <img alt="Image" height="150" width="150" src="https://my_server/ords/f?p=MY_APP:22:0::::P22_FILE_NAME:my_image.png">');

  apex_mail.push_queue;
end;

Now you can send HTML emails in APEX with references to images that are stored in the database.