Building Dynamic Email Templates with Attachments in Oracle APEX Using APEX_MAIL: A Step-by-Step Guide 


🔹 Introduction
In this blog, we will build a dynamic Purchase Order Email Notification system using Oracle APEX Email Templates.
You’ll learn how to create reusable templates, use conditional logic, and send emails using PL/SQL. 
📌 PART 1 - Create Email Template
Step 1: Navigate to Email Templates
Go to:
App Builder → Your Application → Shared Components → Email Templates → Create
Step 2: Enter Template Details
Fill the following:
  • Template Name → PO Approvals
  • Static Identifier → PO_APPROVALS
  • From Name → ERP System
  • From Email → noreply@yourcompany.com
  • Subject → PO #PO_NUMBER# - #SUBJECT#
👉 Use placeholders like #PO_NUMBER# to inject dynamic values.
Step 3: Add Header Content
<p><strong>Hello from Our ERP System</strong></p>
<hr/>

Step 4: Add Body Content
  • Include placeholders (#APPROVER_NAME#, #PO_STATUS#)

  • Add HTML table for PO details

  • Use directives:

      

    • {if/} → Conditional display

    • {case/} → Status-based logic
👉 Example use cases:
  • Show warning only for high value

  • Show different messages for Approved / Rejected
Code in Body:

<p>Dear <strong>#APPROVER_NAME#</strong>,</p>

<p>The following Purchase Order requires your attention:</p>

<!-- PO Details Table -->
<table style="width:100%; border-collapse:collapse; 
              font-size:14px; margin:15px 0;">
  <thead>
    <tr>
      <th style="background-color:#4472C4; color:white; 
                 padding:10px; border:1px solid #ccc; 
                 text-align:left;">PO Number</th>
      <th style="background-color:#4472C4; color:white; 
                 padding:10px; border:1px solid #ccc; 
                 text-align:left;">Supplier Name</th>
      <th style="background-color:#4472C4; color:white; 
                 padding:10px; border:1px solid #ccc; 
                 text-align:left;">Amount</th>
      <th style="background-color:#4472C4; color:white; 
                 padding:10px; border:1px solid #ccc; 
                 text-align:left;">Status</th>
      <th style="background-color:#4472C4; color:white; 
                 padding:10px; border:1px solid #ccc; 
                 text-align:left;">Approver</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td style="padding:10px; border:1px solid #ccc; 
                 background:#f9f9f9;">#PO_NUMBER#</td>
      <td style="padding:10px; border:1px solid #ccc; 
                 background:#f9f9f9;">#SUPPLIER_NAME#</td>
      <td style="padding:10px; border:1px solid #ccc; 
                 background:#f9f9f9;">#PO_AMOUNT#</td>
      <td style="padding:10px; border:1px solid #ccc; 
                 background:#f9f9f9;"><strong>#PO_STATUS#</strong></td>
      <td style="padding:10px; border:1px solid #ccc; 
                 background:#f9f9f9;">#APPROVER_NAME#</td>
    </tr>
  </tbody>
</table>

<!-- High Value Warning -->
{if HIGH_VALUE_MESSAGE/}
<p style="color:orange; font-weight:bold; 
          background:#fff3cd; padding:10px;
          border-left:4px solid orange; margin:10px 0;">
  ⚠️ #HIGH_VALUE_MESSAGE#
</p>
{endif/}

<!-- Status Block -->
{case PO_STATUS/}
{when APPROVED/}
<p style="color:green; font-size:18px; font-weight:bold;">
  ✅ This PO has been APPROVED
</p>
{when REJECTED/}
<p style="color:red; font-size:18px; font-weight:bold;">
  ❌ This PO has been REJECTED
</p>
{otherwise/}
<p style="color:gray;">
  🕐 Status: <strong>#PO_STATUS#</strong>
</p>
{endcase/}

<p>Regards,<br/><strong>ERP System</strong></p>


Step 5: Add Footer
<hr/>
<p style="font-size:12px; color:gray;">
  This is an automated message. Please do not reply.
</p>

Step 6: Add Plain Text Version
Provide fallback content for non-HTML emails.
Step 7: Add Custom CSS
  • Go to Advanced → Load Default HTML

  • Add styles for:

      • 

    • Status colors (green/red)

    • Warning messages

    • Table formatting
Code in HTML Template under Advanced:

Replace below style code.

 <style>
  .po-high-value {
    font-size: 1.25em;
    color: orange;
    font-weight: bold;
    background-color: #fff3cd;
    padding: 8px;
  }
  .po-approved {
    font-size: 1.5em;
    color: green;
    font-weight: bold;
  }
  .po-rejected {
    font-size: 1.5em;
    color: red;
    font-weight: bold;
  }
  .my-table {
    width: 100%;
    border-collapse: collapse;
    font-size: smaller;
  }
  .my-table th {
    background-color: #d3d3d3;
    text-align: left;
    padding: 5px;
    border: 1px solid #000;
  }
  .my-table td {
    padding: 5px;
    border: 1px solid #000;
  }
</style>



📌 PART 2 - Page Designer Setup
Step 8: Create Region
  • Create Static Content Region

  • Title: Purchase Order Details
Step 9: Create Page Items
Create these fields:
  • PO Number

  • PO Status (Select List: APPROVED / REJECTED / PENDING)

  • Approver Name

  • Supplier Name

  • PO Amount

  • High Value Message
👉 Tip: Use correct page prefix (P2_, P3_, etc.)
Step 10: Create Button
  • Button Name → SEND_EMAIL

  • Action → Submit Page

  • Style → Hot

📎PART 3 - Adding File Attachment to Email in Oracle APEX
🔹 Step 11: Create File Upload Item
In your page (same region), create a new item:
  • Item Name → P2_ATTACHMENT

  • Type → File Upload

  • Label → Attach Document
Key Settings:
  • Storage Type → Table APEX_APPLICATION_TEMP_FILES

  • Purge File at → End of Request
  • Display As → Inline File Browse

  • Allow Multiple Files → No (or Yes based on need)
👉 This allows users to upload a file before sending email.

📌 PART 4 - Create PL/SQL Process
Step 12: Go to Processing Tab
Step 13: Create Process
  • Name → Send PO Approval Email

  • Type → PL/SQL Code
Step 14: Add PL/SQL Code
Use apex_mail.send with:
  • Template Static ID

  • JSON placeholders

  • Dynamic page item values
👉 Key concepts:
  • apex_json.stringify() for safe values

  • Conditional placeholder for optional fields
Code:

DECLARE
  -- Variables to hold the uploaded file details
  l_file_name     VARCHAR2(255);
  l_mime_type     VARCHAR2(255);
  l_file_content  BLOB;
  l_mail_id       NUMBER;

BEGIN

  -- ─────────────────────────────────────────────
  -- STEP A: Send the email (same as before)
  -- ─────────────────────────────────────────────
  l_mail_id := apex_mail.send (
    p_to                 => 'your_mail@gmail.com',
    p_template_static_id => 'PO_APPROVALS',
    p_application_id     => :APP_ID,
    p_placeholders       => '{'
      || ' "PO_NUMBER":'
      ||   apex_json.stringify(:P2_PO_NUMBER)
      || ',"PO_STATUS":'
      ||   apex_json.stringify(:P2_PO_STATUS)
      || ',"SUBJECT":'
      ||   apex_json.stringify('PO Notification')
      || ',"APPROVER_NAME":'
      ||   apex_json.stringify(:P2_APPROVER_NAME)
      || ',"SUPPLIER_NAME":'
      ||   apex_json.stringify(:P2_SUPPLIER_NAME)
      || ',"PO_AMOUNT":'
      ||   apex_json.stringify(:P2_PO_AMOUNT)
      || CASE
           WHEN :P2_HIGH_VALUE_MSG IS NOT NULL
           THEN ',"HIGH_VALUE_MESSAGE":'
                || apex_json.stringify(:P2_HIGH_VALUE_MSG)
           ELSE ''
         END
      || '}'
  );

  -- ─────────────────────────────────────────────
  -- STEP B: Add attachment only if file uploaded
  -- ─────────────────────────────────────────────
  IF :P2_ATTACHMENT IS NOT NULL THEN

    -- Read the uploaded file from APEX temp table
    SELECT filename,
           mime_type,
           blob_content
      INTO l_file_name,
           l_mime_type,
           l_file_content
      FROM apex_application_temp_files
     WHERE name = :P2_ATTACHMENT;

    -- Attach the file to the email we just sent
    apex_mail.add_attachment (
      p_mail_id    => l_mail_id,    -- Links to the email above
      p_attachment => l_file_content,
      p_filename   => l_file_name,
      p_mime_type  => l_mime_type
    );

  END IF;

  -- ─────────────────────────────────────────────
  -- STEP C: Push the queue to actually send
  -- ─────────────────────────────────────────────
  apex_mail.push_queue;

END;
Step 15: Add Button Condition
Set:
  • When Button Pressed → SEND_EMAIL
Step 16: Success Message
Email sent successfully!

Step 17: Save Application
Save and run the application, enter the required details in the PO form, then click the Send Email Notification button
and verify the email in your inbox.

Final form Output:


Mail Box Output:



Another PO number Mail Box Output:


📌 PART 4 - Testing Scenarios
✅ Scenario 1: Approved + High Value
  • Shows:
    • Table

    • Warning message

    • Green Approved status
❌ Scenario 2: Rejected
  • Shows:
    • Table

    • No warning

    • Red Rejected message
⏳ Scenario 3: Pending
  • Shows:

    • Table

    • Neutral status message
🎯 Conclusion
Using Oracle APEX Email Templates, you can build dynamic, reusable, and professional email systems
with minimal effort. This approach improves maintainability and enables advanced features like conditional
rendering and styling. 
🚀 Future Enhancements
  • Multi-recipient email support
  • Approval workflow integration

  • Email tracking & logging

Comments

Post a Comment

Popular posts from this blog

Building Secure RESTful Services in Oracle APEX 24.2 Using ORDS, OAuth2 Client Credentials, and PL/SQL

Image
📘 Introduction Oracle APEX 24.2 provides powerful capabilities to expose database data as secure RESTful APIs using Oracle REST Data Services (ORDS). In modern enterprise architectures, APIs are essential for enabling system-to-system communication. Instead of direct database access, RESTful services allow controlled and secure data sharing. In this blog, we will walk through how to create a REST API in Oracle APEX 24.2 and secure it using privileges and OAuth authentication. 🎯 Objective The goal of this implementation is to: • Expose database data through REST APIs • Create structured API endpoints • Secure APIs using OAuth • Enable controlled external access 🧱 Prerequisites Before starting, ensure: • Oracle APEX 24.2 is installed • ORDS 20.x or later is configured • RESTful Services are enabled • A working table exists (Example: HR.EMPLOYEES) 🌐 REST API URL Structure The generated API follows this format: https://host:port/ords/schema/module/template ⚙️ Implementat...
Read more

Building a Portfolio Generator in Oracle APEX: A Step-by-Step Guide to Generate Downloadable Portfolio Documents

Image
  Introduction This tutorial demonstrates how to build a Portfolio Generator application in Oracle APEX where users can enter resume details and generate a downloadable portfolio HTML/ PDF with one click . The application collects user details, work experience, skills, and education, then dynamically generates a styled HTML portfolio that can be printed as PDF . Step- by- Step  Implementation  Guide Step 1 -  Create Database Tables First create the tables required to store resume information. We will create 5 tables . 1️⃣ Resume Master Table Stores the main profile information.   CREATE TABLE "RESUME_MASTER"     ( "RESUME_ID" NUMBER GENERATED ALWAYS AS IDENTITY, "FULL_NAME" VARCHAR2(200),  "EMAIL" VARCHAR2(200),  "PHONE" VARCHAR2(50),  "ADDRESS" VARCHAR2(500),  "DOB" DATE,  "OBJECTIVE" CLOB,  "CURRENT_COMPANY" VARCHAR2(200),  "CURRENT_ROLE" VARCHAR2(200),  "C...
Read more

Implementing WhatsApp OTP Verification in Oracle APEX Using UltraMsg API

Image
  🚀 Introduction In today’s digital applications, user verification plays a crucial role in ensuring security and trust. Traditionally, OTPs are sent via SMS — but with the widespread use of WhatsApp, sending OTPs through WhatsApp offers: Faster delivery Better user reach Improved engagement In this blog, we explore how to implement OTP verification in Oracle APEX by integrating WhatsApp messaging using an external API service. 🎯 Objective The goal of this implementation was to: ✔️ Generate OTP dynamically ✔️ Send OTP via WhatsApp ✔️ Integrate external messaging API ✔️ Enhance login/verification security ✔️ Enable real-time communication 🧩 Solution Overview This solution integrates: PL/SQL OTP generation REST API call from Oracle APEX WhatsApp messaging service This allows Oracle APEX applications to send OTP directly to users via WhatsApp instead of SMS.  🧱 Step-by-Step Approach  Step 1: Configure WhatsApp API To send OTP via WhatsApp, ...
Read more