Send Email of Dynamic Excel by Automation in Oracle APEX

 declare

l_highlights apex_data_export.t_highlights;

l_context apex_exec.t_context;

l_export apex_data_export.t_export;

l_print_config apex_data_export.t_print_config;

l_id NUMBER;

l_error_message cis_errors.ERROR_MESSAGE%type;

blob_content clob;

l_program_name varchar2(250) :='Automation :Premium Summary';

begin

l_context := apex_exec.open_query_context(

p_location => apex_exec.c_location_local_db,

p_sql_query => 'select rownum as "SR.NO",

PR_LOCATION as "LOCATION",

BR_NAME as "LOC NAME",

THISYEAR_CMP_SALES as "THISYEAR_CMP_SALES",

LASTYEAR_CMP_SALES as "LASTYEAR_CMP_SALES",

THISYEAR_TP_SALES as "THISYEAR_TP_SALES" ,

LASTYEAR_TP_SALES as "LASTYEAR_TP_SALES"

from VPRM_SUM ');

l_export := apex_data_export.export (

p_context => l_context,

p_format => 'XLSX',

p_print_config => l_print_config,

p_highlights => l_highlights );

apex_exec.close( l_context );

l_id := APEX_MAIL.SEND(

p_to =>'receiver@gmail.com',

p_from => 'sender@gmail.com',

p_subj => 'Premium Summary with attachment',

p_body => 'Please review the attachment.',

p_body_html => '<b>Please</b> review the attachment');

APEX_MAIL.ADD_ATTACHMENT(

p_mail_id => l_id,

p_attachment => l_export.content_blob,

p_filename => l_export.file_name,

p_mime_type => l_export.mime_type);

exception

when others then

l_error_message:=l_context;

raise;

end;