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