APEX - Email with .CSV attachment

Question

APEX version: 4.1.1

Example:

http://apex.oracle.com/pls/apex/f?p=27554:53

User name: demo
PW: demo

I am using the following blog (http://spendolini.blogspot.com/2006/04/custom-export-to-csv.html) to create a .CSV file, but how do I add that .CSV file as an attachment to an email that is created/generated in APEX? I want to have 1 page process that creates the .CSV file, adds that .CSV file to an APEX generated email and sends that email when a button is click on the APEX page.

I am also using the following Oracle doc as a guideline.

Current page process code when 'Apply Updates' button is clicked: (when the code runs a .CSV file is created and an email is sent, but the email does not contain the .CSV file as an attachment)

declare
  l_body_html varchar2(4000);
  l_subj varchar2(200);
  l_from varchar2(200);
  l_cc_address varchar2(2000);
  l_cc varchar2(500);
  l_id NUMBER;

begin

         l_subj := '***TEST - DQ MANUAL EDIT - Daily Field Report - ***TEST';

l_body_html := '<html>
<head>
<style type="text/css">
body{font-family: Calibri, Arial, Helvetica, sans-serif;
font-size:11pt;
margin:30px;
color:#1F497D;
background-color:#ffffff;}

</style>
</head>
<body>';

       l_body_html := l_body_html || '<body><div><p><span style="font-size:12.0pt;font-family:Calibri,sans-serif;color:#1F497D;font-weight:bold;">Attached is the manual edit report for today.</b>';

    l_body_html := l_body_html;

   l_body_html := l_body_html;

   l_body_html := l_body_html || '<p>' || ' will contact you in the near future to prioritize the request and discuss an estimated completion date. Please note that many requests are received daily and it can take several days to couple of weeks on some requests.  If this request is of a high critical nature, please feel free to contact directly or reply to this email.</p>';

 l_body_html := l_body_html || '</div></body></html>';


begin
-- Set the MIME type
owa_util.mime_header( 'application/octet', FALSE );
-- Set the name of the file
htp.p('Content-Disposition: attachment; filename="Manual_Edit_Selected_Records.csv"');
-- Close the HTTP Header
owa_util.http_header_close;
-- Loop through all rows in EMP
htp.prn('Data Fields,Grouping,Channel,Month,Year,Edits' || chr(13));
for x in (select 
dq_attribute,
dq_grouping, 
case 
when DQ_CHANNEL = 'Retail' then 'Retail/CD'
when DQ_CHANNEL = 'Consumer Direct' then 'Retail/CD'
else DQ_CHANNEL 
end as dq_channel,
to_char(to_date(INSERT_DATE,'yyyy-mm'),'Month') as Month,
to_char(to_date(INSERT_DATE,'yyyy-mm'),'YYYY') as Year,
case when count(reason_for_change)=0 then 'N/A' else to_char(count(reason_for_change)) end  as Edits
from DQ_MANUAL_EDIT
where
decode(NVL(:P50_DQ_ANALYST, 'ALL'), 'ALL','ALL',DQ_ANALYST) = NVL(:P50_DQ_ANALYST, 'ALL')
and decode(NVL(:P50_DQ_FIELD, 'ALL'), 'ALL','ALL',DQ_ATTRIBUTE) = NVL(:P50_DQ_FIELD, 'ALL')
and decode(NVL(:P50_DQ_CHANNEL, 'ALL'), 'ALL','ALL',DQ_CHANNEL) = NVL(:P50_DQ_CHANNEL, 'ALL')
and decode(NVL(:P50_DQ_GROUPING, 'ALL'), 'ALL','ALL',DQ_GROUPING) = NVL(:P50_DQ_GROUPING, 'ALL')
and decode(NVL(:P50_DQ_IMS, 'ALL'), 'ALL','ALL',DQ_IMS_NUM) = NVL(:P50_DQ_IMS, 'ALL')
and NVL(to_char(to_date(:P50_EDIT_DATE_FROM,'MM/DD/YYYY'),'MM/DD/YYYY'), to_date(sysdate,'MM/DD/YYYY')) <= trunc(INSERT_DATE)
and NVL(to_char(to_date(:P50_EDIT_DATE_TO, 'MM/DD/YYYY'),'MM/DD/YYYY'), to_date(sysdate-28,'MM/DD/YYYY')) >= trunc(INSERT_DATE)
Group by 
dq_attribute, 
INSERT_DATE,
dq_grouping, 
dq_channel
order by to_char(to_date(INSERT_DATE,'yyyy-mm'),'YYYY') desc,
to_char(to_date(INSERT_DATE,'yyyy-mm'),'mm'), dq_attribute asc
)
loop
 -- Print out a portion of a row,
 -- separated by commas and ended by a CR
 htp.prn(
'"' ||x.dq_attribute||'","'|| 
x.dq_grouping ||'","'||  
x.dq_channel ||'","'||  
x.Month ||'","'||
x.Year ||'","'||  
x.Edits || '"' || chr(13));
end loop;
-- Send an error code so that the
-- rest of the HTML does not render
htmldb_application.g_unrecoverable_error := true;
end;



  l_id := APEX_MAIL.SEND(
    --P_TO        => :P53_EMAIL_TO,
    P_TO        => '[email protected]',
    P_FROM      => '[email protected]',
    P_BODY      => l_body_html,
    P_BODY_HTML => l_body_html,
    P_SUBJ      => l_subj);

      FOR c1 IN (SELECT filename, blob_content, mime_type 
        FROM APEX_APPLICATION_FILES
        WHERE ID IN (123,456)) LOOP

        APEX_MAIL.ADD_ATTACHMENT(
            p_mail_id    => l_id,
            p_attachment => c1.blob_content,
            p_filename   => c1.filename,
            p_mime_type  => c1.mime_type);
        END LOOP;
    COMMIT;


    wwv_flow_mail.push_queue(
   P_SMTP_HOSTNAME => 'mailhost.company.net',
   P_SMTP_PORTNO => '31'
   );
end;

Maybe I'm just being dense today, but I'm not sure what the question is here. You've posted some code that looks at least vaguely correct. Are you getting an error when you run the code? If so, what error? Is there a problem with the email? If so, what is the problem? — Justin Cave
@Justin Cave My question is: how to create a .CSV file and include that as an attachment to an email, in one page process? The code above basically works as two separate entities: the .CSV file is created and the mail is sent (without the attachment). I want to combine them into one process. — Jeremy F.

Richard Pascual Richard Pascual · Accepted Answer · 2012-12-04T01:20:42

Found a useful discussion on Oracle Forums related to this topic which will help answer your question: https://forums.oracle.com/forums/thread.jspa?threadID=989396

The discussion is that an attachment to an email is not a physical file separate from the email itself, which may be your issue when you first create a physical file and then draft an email to send it. It doesn't work in the way we see it when we send an email using a webmail client or email software application. See Bill Verreyne's explanation of how the utl_mail package works which shows how to add an attachment by modifying the MIME-BODY of your email message.

In a nutshell, the concept is to just put your data in the body of the email itself, but identify what portion is supposed to be treated as an "attachment" by appropriate mime headers. UTL_MAIL looks like the simplest way to manage this, though you could probably handcraft some simple headers with some patience and trial and error, or experiment further with the APEX_MAIL package.

I copied the most important part, which is what an example email body looks like with an "attachment"... note there is no physical file within the email at all. It reads from start to finish like one single email message:

From: [email protected]
To: [email protected]
Subject: =?ISO-8859-1?Q?Sample=20Mail=20from=20UTL=5FMAIL?=
X-Priority: 3
Content-Type: multipart/mixed; boundary="------------4D8C24=_23F7E4A13B2357B3"
Return-Path: [email protected]
Date: 19 Nov 2009 07:45:15 +0200
Mime-Version: 1.0

This is a multi-part message in MIME format.
--------------4D8C24=_23F7E4A13B2357B3
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

This is the e-mail message part that the mail reader will display.


--------------4D8C24=_23F7E4A13B2357B3
Content-Type: text/csv; name="sample.csv"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline; filename="sample.csv"

col1,col2,col3
col1,col2,col3
col1,col2,col3
--------------4D8C24=_23F7E4A13B2357B3--

APEX - Email with .CSV attachment

1 Answers