How to Send Email from Oracle APEX Using APEX_MAIL

Email notifications are a core feature of almost every business application. Sending an approval request, a password reset link, a report digest, or an automated alert — all of these require your Oracle APEX application to send email reliably and programmatically.

APEX provides the APEX_MAIL package for exactly this purpose. In this guide, we cover everything from initial setup to HTML emails, file attachments, and error handling.

How APEX_MAIL Works

When you call APEX_MAIL.SEND, APEX does not send the email immediately. It adds the message to an internal mail queue (the APEX_MAIL_QUEUE table). A background process — typically scheduled to run every few minutes — actually delivers the queued messages to your SMTP server.

This queue-based approach means:

• Email sending does not slow down your transaction
• Failed deliveries are automatically retried
• You can monitor the queue and identify delivery failures

Step 1: Configure the SMTP Server in APEX

Before any email can be sent, you need to configure your SMTP settings in APEX Instance Administration:

1. Log in to APEX as an Instance Administrator
2. Go to Instance Administration → Instance Settings → Email
3. Enter your SMTP server details:
   • SMTP Host Address: smtp.yourdomain.com
   • SMTP Host Port: 25 (standard), 587 (STARTTLS), or 465 (SSL)
   • Use SSL/TLS: Recommended for production
   • SMTP Authentication Username/Password: If your SMTP server requires authentication
   • Default From Email Address: noreply@yourdomain.com
4. Save the settings and use the Send Test Email button to verify

Sending a Basic Plain Text Email

BEGIN
  APEX_MAIL.SEND(
    p_to      => 'recipient@example.com',
    p_from    => 'noreply@yourdomain.com',
    p_subj    => 'Your Order Has Been Approved',
    p_body    => 'Dear Customer,' || CHR(10) || CHR(10) ||
                 'Your order #12345 has been approved and is being processed.' || CHR(10) ||
                 'You will receive a shipment notification within 24 hours.' || CHR(10) || CHR(10) ||
                 'Thank you for your business.'
  );
  
  -- Optionally flush the queue immediately (in development/testing)
  APEX_MAIL.PUSH_QUEUE;
END;

Use CHR(10) for line breaks in plain text emails. The APEX_MAIL.PUSH_QUEUE call forces immediate delivery — useful in development to test emails without waiting for the scheduled job.

Sending an HTML Email

HTML emails provide much better formatting. Use the p_body_html parameter alongside p_body (which is the plain text fallback for email clients that do not render HTML):

DECLARE
  v_html_body CLOB;
BEGIN
  v_html_body := '


  
  


  

    
Order Approval Notification
  
  

    
Dear ' || :P10_CUSTOMER_NAME || ',
    
Your order #' || :P10_ORDER_ID || ' has been approved.
    
Order Total: $' || TO_CHAR(:P10_ORDER_TOTAL, ''FM999,990.00'') || '
    
      Track Your Order
    
  

';

  APEX_MAIL.SEND(
    p_to        => :P10_CUSTOMER_EMAIL,
    p_from      => 'orders@yourdomain.com',
    p_subj      => 'Order #' || :P10_ORDER_ID || ' Approved',
    p_body      => 'Your order has been approved. Please view this email in an HTML-capable client.',
    p_body_html => v_html_body
  );
END;

Sending to Multiple Recipients

-- Multiple TO recipients (comma-separated)
APEX_MAIL.SEND(
  p_to   => 'user1@example.com,user2@example.com',
  p_from => 'noreply@yourdomain.com',
  p_cc   => 'manager@yourdomain.com',   -- CC recipient
  p_bcc  => 'audit@yourdomain.com',     -- BCC recipient (hidden from others)
  p_subj => 'Monthly Report Available',
  p_body => 'The monthly report is ready for review.'
);

Sending Email with an Attachment

To attach a file, first call APEX_MAIL.SEND to create the message and get its ID, then call APEX_MAIL.ADD_ATTACHMENT:

DECLARE
  v_mail_id   NUMBER;
  v_blob_data BLOB;
  v_mime_type VARCHAR2(100) := 'application/pdf';
BEGIN
  -- Get the attachment from somewhere (database BLOB column, generated PDF, etc.)
  SELECT report_pdf
  INTO   v_blob_data
  FROM   monthly_reports
  WHERE  report_month = '2024-12';
  
  -- Create the email first
  v_mail_id := APEX_MAIL.SEND(
    p_to   => 'finance@yourdomain.com',
    p_from => 'reports@yourdomain.com',
    p_subj => 'December Monthly Report',
    p_body => 'Please find the December report attached.'
  );
  
  -- Add the attachment using the mail ID
  APEX_MAIL.ADD_ATTACHMENT(
    p_mail_id    => v_mail_id,
    p_attachment => v_blob_data,
    p_filename   => 'December_2024_Report.pdf',
    p_mime_type  => v_mime_type
  );
  
  APEX_MAIL.PUSH_QUEUE;
END;

Building Dynamic Recipient Lists

Often you need to notify a dynamic list of users — everyone in a department, all approvers for a specific workflow, etc.:

DECLARE
  v_recipients VARCHAR2(4000);
BEGIN
  -- Build comma-separated recipient list from a query
  SELECT LISTAGG(email, ',') WITHIN GROUP (ORDER BY last_name)
  INTO   v_recipients
  FROM   app_users
  WHERE  department_id = :P5_DEPT_ID
  AND    notify_enabled = 'Y'
  AND    email IS NOT NULL;
  
  IF v_recipients IS NOT NULL THEN
    APEX_MAIL.SEND(
      p_to   => v_recipients,
      p_from => 'notifications@yourdomain.com',
      p_subj => 'Action Required: New Request Pending',
      p_body => 'A new request has been submitted and requires your review.'
    );
  END IF;
END;

Monitoring the Email Queue

You can check email delivery status from the APEX administration interface:

• In your workspace: App Builder → Workspace Utilities → Email Log
• As instance admin: Instance Administration → Monitor Activity → Mail Log

Or query the queue directly:

-- Check pending emails in the queue
SELECT message_id, mail_to, mail_subject, 
       created_on, send_count, send_error
FROM   apex_mail_queue
ORDER BY created_on DESC;

Common Issues and Solutions

Email stays in queue and is not delivered: Check that the APEX mail background job is enabled (Instance Administration → Manage Instance → Background Jobs). Also verify SMTP connectivity from the database server.

Authentication error from SMTP server: Verify the SMTP username and password in Instance Settings. Some providers (like Gmail) require app-specific passwords or less-secure app access.

Emails going to spam: Set up SPF and DKIM records for your sending domain. Use a dedicated sending domain/subdomain for application emails. Avoid spam trigger words in subjects and bodies.

CLOB too large for HTML email: The p_body_html parameter accepts a CLOB, so very large HTML bodies are supported. Avoid inlining large base64-encoded images in the HTML — use hosted image URLs instead.

Conclusion

APEX_MAIL is one of those packages that you configure once and use everywhere. Once your SMTP settings are in place and tested, adding email notifications to any workflow is just a few lines of PL/SQL. Use HTML emails with proper inline CSS for professional-looking notifications, and always include a plain text fallback for maximum compatibility.

Email is often the most visible part of an application’s user experience — a well-formatted, timely notification builds confidence; a plain or missing one raises doubts. Take the time to do it right.