Friday 17 October 2014

Sending E-mail with Excel Attachment Using UTL_SMTP


The utl_smtp package is used to send e-mails from any database to a valid recipient/receipents with or without attachements.
The generic code is given to send e-mail with excel attachment , it can be modifed to match with any business requirement.


DECLARE
   i                          NUMBER              := 1;
   j                          NUMBER              := 1;
   p_to                       VARCHAR2 (100)      := 'Valid Email Id';
   lv_smtp_server             VARCHAR2 (100)      := '199.1.1.77';
   lv_domain                  VARCHAR2 (100);
   lv_from                    VARCHAR2 (100)      := 'Valid Domain';
   v_connection               UTL_SMTP.connection;
   c_mime_boundary   CONSTANT VARCHAR2 (256)      := '--AAAAA000956--';
   v_clob                     CLOB;
   ln_len                     INTEGER;
   ln_index                   INTEGER;
   ln_count                   NUMBER;
   ln_code                    VARCHAR2 (10);
   ln_counter                 NUMBER              := 0;
   lv_instance                VARCHAR2 (100);
   ln_cnt                     NUMBER;
   ld_date                    DATE;
  
BEGIN
   ld_date := SYSDATE;
   lv_domain := lv_smtp_server;

   BEGIN
      v_clob := 'Number' || ',' || 'Name' || UTL_TCP.crlf;
      v_connection := UTL_SMTP.open_connection (lv_smtp_server); --To open the connection      UTL_SMTP.helo (v_connection, lv_domain);
      UTL_SMTP.mail (v_connection, lv_from);
      UTL_SMTP.rcpt (v_connection, p_to); -- To send mail to valid receipent     

      UTL_SMTP.open_data (v_connection);
      UTL_SMTP.write_data (v_connection, 'From: ' || lv_from || UTL_TCP.crlf);

      IF TRIM (p_to) IS NOT NULL
      THEN
         UTL_SMTP.write_data (v_connection, 'To: ' || p_to || UTL_TCP.crlf);
      END IF;

      UTL_SMTP.write_data (v_connection,
                           'Subject: Item Creation Report' || UTL_TCP.crlf
                          );
      UTL_SMTP.write_data (v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);
      UTL_SMTP.write_data (v_connection,
                              'Content-Type: multipart/mixed; boundary="'
                           || c_mime_boundary
                           || '"'
                           || UTL_TCP.crlf
                          );
      UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
      UTL_SMTP.write_data (v_connection,
                              'This is a multi-part message in MIME format.'
                           || UTL_TCP.crlf
                          );
      UTL_SMTP.write_data (v_connection,
                           '--' || c_mime_boundary || UTL_TCP.crlf
                          );
      UTL_SMTP.write_data (v_connection,
                           'Content-Type: text/plain' || UTL_TCP.crlf
                          );
      ln_cnt := 1;

      /*Condition to check for the creation of csv attachment*/
      IF (ln_cnt <> 0)
      THEN
         UTL_SMTP.write_data
                            (v_connection,
                                'Content-Disposition: attachment; filename="'
                             || 'Emp_details'
                             || TO_CHAR (ld_date, 'dd-mon-rrrr hh:mi')
                             || '.csv'
                             || '"'
                             || UTL_TCP.crlf
                            );
      END IF;

      UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
      FOR i IN (SELECT x.*
                  FROM emp x)
      LOOP
         ln_counter := ln_counter + 1;

         IF ln_counter = 1
         THEN
         UTL_SMTP.write_data (v_connection, v_clob);--To avoid repeation of column heading in csv file

         END IF;
         BEGIN
            v_clob := '="' || i.ID || '"' || ',' || i.NAME || UTL_TCP.crlf;
         EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line (fnd_file.LOG, SQLERRM);
         END;

         UTL_SMTP.write_data (v_connection, v_clob); --Writing data in csv attachment.     
      END LOOP;
      UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
      UTL_SMTP.close_data (v_connection);
      UTL_SMTP.quit (v_connection);
      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SQLERRM);
   END;
END;



Note: To send e-mail to multiple receipents, logic should be build to loop over 'UTL_SMTP.rcpt (l_mail_conn, p_to)' command

4 comments:

  1. Hi Their, I was looking at this functionality and have a question at this step
    v_clob := '="' || i.ID || '"' || ',' || i.NAME || UTL_TCP.crlf; lets say i have a name column data with commas in it so, how do i consider it as one column in the generated CSV file. Please let me know. Thanks.

    ReplyDelete
  2. This is very informative blog.Thanks for sharing this blog. We have got so much information.
    Gmail Email Attachment Downloader

    ReplyDelete
  3. Hi. I am trying to add a Message body in this solution however I am not able to. Can You help me here.

    ReplyDelete
  4. Seethalakshmi M20 May 2021 at 03:17

    Hi , I tried to run this procedure according to my business requirement. Its not working. Can you help me here

    ReplyDelete