title: APEX - Email with .CSV attachment
body: <p>APEX version: 4.1.1</p>
  
  <pre><code>Example:
  </code></pre>
  
  <p><a href="http://apex.oracle.com/pls/apex/f?p=27554:53" rel="nofollow">http://apex.oracle.com/pls/apex/f?p=27554:53</a></p>
  
  <pre><code>User name: demo
  PW: demo
  </code></pre>
  
  <p>I am using the following blog (<a href="http://spendolini.blogspot.com/2006/04/custom-export-to-csv.html" rel="nofollow">http://spendolini.blogspot.com/2006/04/custom-export-to-csv.html</a>) 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.</p>
  
  <p>I am also using the following <a href="http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21676/apex_mail.htm#CHDHCEGF" rel="nofollow">Oracle doc</a> as a guideline.</p>
  
  <p>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)</p>
  
  <pre><code>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 := '&lt;html&gt;
  &lt;head&gt;
  &lt;style type="text/css"&gt;
  body{font-family: Calibri, Arial, Helvetica, sans-serif;
  font-size:11pt;
  margin:30px;
  color:#1F497D;
  background-color:#ffffff;}
  
  &lt;/style&gt;
  &lt;/head&gt;
  &lt;body&gt;';
  
         l_body_html := l_body_html || '&lt;body&gt;&lt;div&gt;&lt;p&gt;&lt;span style="font-size:12.0pt;font-family:Calibri,sans-serif;color:#1F497D;font-weight:bold;"&gt;Attached is the manual edit report for today.&lt;/b&gt;';
  
      l_body_html := l_body_html;
  
     l_body_html := l_body_html;
  
     l_body_html := l_body_html || '&lt;p&gt;' || ' 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.&lt;/p&gt;';
  
   l_body_html := l_body_html || '&lt;/div&gt;&lt;/body&gt;&lt;/html&gt;';
  
  
  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')) &lt;= 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')) &gt;= 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        =&gt; :P53_EMAIL_TO,
      P_TO        =&gt; 'first_name.last_name@company.com',
      P_FROM      =&gt; 'IT.DEPT@company.com',
      P_BODY      =&gt; l_body_html,
      P_BODY_HTML =&gt; l_body_html,
      P_SUBJ      =&gt; 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    =&gt; l_id,
              p_attachment =&gt; c1.blob_content,
              p_filename   =&gt; c1.filename,
              p_mime_type  =&gt; c1.mime_type);
          END LOOP;
      COMMIT;
  
  
      wwv_flow_mail.push_queue(
     P_SMTP_HOSTNAME =&gt; 'mailhost.company.net',
     P_SMTP_PORTNO =&gt; '31'
     );
  end;
  </code></pre>