1 Reply Latest reply: Dec 5, 2012 5:25 PM by AndyPol RSS

    Email with .CSV attachment

    596740
      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 (http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21676/apex_mail.htm#CHDHCEGF) 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;
            csv_email_attachment CLOB; 
       
          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 || '<p>' || 'Additional information here.</p>';
          
           l_body_html := l_body_html || '</div></body></html>';
          
          --Creates .CSV file
          begin
          -- Set the MIME type
          owa_util.mime_header( 'application/octet', FALSE );
          -- Set the name of the file
          csv_email_attachment :=('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;
          --end of creating .CSV file
            
             
            l_id := APEX_MAIL.SEND(
              --P_TO        => :P53_EMAIL_TO,
               P_TO        => 'first_name.last_name@company.com',
              P_FROM      => 'IT.DEPT@company.com',
              P_BODY      => l_body_html,
              P_BODY_HTML => l_body_html,
              P_SUBJ      => l_subj);
          
             
                  APEX_MAIL.ADD_ATTACHMENT(
                      p_mail_id    => l_id,
                      p_attachment => csv_email_attachment,
                      p_filename   => 'Manual_Edit_Selected_Records.csv',
                      p_mime_type  => 'text/csv');
         
                      
              wwv_flow_mail.push_queue(
             P_SMTP_HOSTNAME => 'mailhost.company.net',
             P_SMTP_PORTNO => '31'
             );
          end;
      Edited by: Sid_244 on Nov 29, 2012 2:42 PM

      Edited by: Sid_244 on Dec 5, 2012 1:35 PM

      Edited by: Sid_244 on Dec 5, 2012 2:04 PM