Send email with attachment(s) using APEX_MAIL

[admin]

CREATE OR REPLACE DIRECTORY USER_DIR AS '/user_dir'; << in case of RAC it should be shared location

GRANT EXECUTE, READ, WRITE ON DIRECTORY USER_DIR TO XXXXXX WITH GRANT OPTION;
GRANT READ ON DIRECTORY TEST TO PUBLIC;

-- The code can be used in Oracle Application R12 as well.

DECLARE
l_id NUMBER;
l_bfile BFILE;
l_blob BLOB;
l_body_html CLOB;
l_directory varchar2(100) default 'USER_DIR';
l_filie_name varchar2(100) default 'test.log';
l_to_email varchar2(100) default 'info@erpstuff.com';
l_from_email varchar2(100) default 'admin@erpstuff.com';
l_subject varchar2(100) default 'Email with Attachment using APEX'';
l_workspace_id number;

BEGIN
-- If out of APEX environment
APEX_180200.wwv_flow_api.set_security_group_id(APEX_180200.apex_util.find_security_group_id('APPS'));

DBMS_LOB.createtemporary(l_blob, TRUE); -- Initialize the BLOB.
l_bfile := BFILENAME(l_directory, l_filie_name);
DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
l_body_html := '<p />The following tasks have been added.';

l_id := APEX_MAIL.SEND(
p_to => l_to_email,
p_from => l_from_email,
p_subj => l_subject,
p_body => 'Test email using ERPstuff Sample code.',
p_body_html => l_body_html;


APEX_MAIL.ADD_ATTACHMENT(
p_mail_id => l_id, << if multiple attachements then use same l_id and use loop
p_attachment => l_blob,
p_filename => l_filie_name,
p_mime_type => 'application/pdf');

DBMS_LOB.fileclose(l_bfile);
DBMS_LOB.freetemporary(l_blob);
COMMIT;
apex_mail.push_queue;
END;