I am trying to send a output spool file name having dates that means the spool file name is not constant
and want to email the file as and attachment to recipients. I am trying to use utl_smtp package to send
the attachment but I don't know how to call the file name. Here is what I am trying:

column spool_text_source new_value spool_text

select 'Audit_Reports_'||
(case
when to_char(sysdate,'MM') between '01' and '03' then to_date('01-JAN' || '-' || to_char(sysdate,'YYYY'))
when to_char(sysdate,'MM') between '04' and '06' then to_date('01-APR' || '-' || to_char(sysdate,'YYYY'))
when to_char(sysdate,'MM') between '07' and '09' then to_date('01-JUL' || '-' || to_char(sysdate,'YYYY'))
when to_char(sysdate,'MM') between '10' and '12' then to_date('01-OCT' || '-' || to_char(sysdate,'YYYY'))
else to_date(null)
end ) ||'_'||
(case
when to_char(sysdate,'MM') between '01' and '03' then to_date('31-MAR' || '-' || to_char(sysdate,'YYYY'))
when to_char(sysdate,'MM') between '04' and '06' then to_date('30-JUN' || '-' || to_char(sysdate,'YYYY'))
when to_char(sysdate,'MM') between '07' and '09' then to_date('30-SEP' || '-' || to_char(sysdate,'YYYY'))
when to_char(sysdate,'MM') between '10' and '12' then to_date('31-DEC' || '-' || to_char(sysdate,'YYYY'))
else to_date(null)
end ) ||'.html' spool_text_source from dual;

Sorry if I missed the point, I want to make it automated process and call the email procedure from the same script and email the spool file to recipients. Here I am able to generate the spool file but same I can not call to the email procedure as a variable.

You need to help us by following the Posting Guidelines as stated below.http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.