Automating Email Notifications With APEX_MAIL and the Mail Queue

Email Notifications From APEX

Sending email notifications from an APEX application is a common requirement for workflows, alerts, and user communications. The APEX_MAIL package provides a straightforward API for composing and sending emails, and APEX’s internal mail queue ensures reliable delivery even when the mail server is temporarily unavailable.

Basic Email Sending

Before sending any email, configure the SMTP settings in your APEX instance administration (Instance Settings, then Email). Once configured, sending an email from PL/SQL is simple:

BEGIN
    APEX_MAIL.SEND(
        p_to   => 'manager@example.com',
        p_from => 'noreply@myapp.com',
        p_subj => 'Purchase Order #' || :P50_PO_NUMBER || ' Requires Approval',
        p_body => 'A new purchase order requires your approval. ' ||
                  'Total amount: $' || TO_CHAR(:P50_TOTAL, 'FM999,999.00'),
        p_body_html => '
Purchase Order Approval Required
' ||
                       '
PO Number: ' || :P50_PO_NUMBER || '
' ||
                       '
Total: $' || TO_CHAR(:P50_TOTAL, 'FM999,999.00') ||
                       '
' ||
                       '
Review in Application
'
    );

    -- Push the mail queue to send immediately
    APEX_MAIL.PUSH_QUEUE;
END;
/

Always provide both p_body (plain text) and p_body_html (HTML). Email clients that cannot render HTML will fall back to the plain text version.

Attachments

Adding attachments is a two step process. First call APEX_MAIL.SEND to create the email and capture the returned mail ID, then call APEX_MAIL.ADD_ATTACHMENT:

DECLARE
    l_mail_id NUMBER;
BEGIN
    l_mail_id := APEX_MAIL.SEND(
        p_to   => 'customer@example.com',
        p_from => 'invoices@myapp.com',
        p_subj => 'Your Invoice #' || l_invoice_number,
        p_body => 'Please find your invoice attached.',
        p_body_html => '<p>Please find your invoice attached.</p>'
    );

    APEX_MAIL.ADD_ATTACHMENT(
        p_mail_id    => l_mail_id,
        p_attachment  => l_pdf_blob,
        p_filename    => 'Invoice_' || l_invoice_number || '.pdf',
        p_mime_type   => 'application/pdf'
    );

    APEX_MAIL.PUSH_QUEUE;
END;
/

The Mail Queue

APEX does not send emails synchronously. When you call APEX_MAIL.SEND, the email is placed in an internal queue table. Calling APEX_MAIL.PUSH_QUEUE attempts to deliver all queued messages. If the SMTP server is down, the messages remain in the queue and can be retried later. You can schedule a DBMS_SCHEDULER job to call PUSH_QUEUE every few minutes to ensure reliable delivery without calling it manually after every send.

Monitoring and Troubleshooting

View the mail queue and delivery status in App Builder under Utilities then Mail Queue, or query APEX_MAIL_QUEUE and APEX_MAIL_LOG views directly. Failed deliveries appear in the log with the SMTP error message, which is usually enough to diagnose configuration issues.