2 Replies Latest reply: Dec 10, 2008 4:32 PM by NelsonNelson RSS

    Send mail with attachment

    653881
      Hi,

      I want to send a mail with an attachment. My mail is created and send but without my attachment file.
      Here is my code.

      objOutlook := CLIENT_OLE2.CREATE_OBJ('Outlook.Application');
      objarg := CLIENT_OLE2.CREATE_ARGLIST;
      CLIENT_OLE2.ADD_ARG(objarg,0);
      objMail := CLIENT_OLE2.INVOKE_OBJ(objOutlook,'CreateItem', objarg);
      CLIENT_OLE2.DESTROY_ARGLIST(objarg);
      objAttach := CLIENT_OLE2.GET_OBJ_PROPERTY(objmail, 'Attachments');
      objarg := CLIENT_OLE2.CREATE_ARGLIST;
      CLIENT_OLE2.ADD_ARG(objarg,myFileName); -- filename               
      CLIENT_OLE2.SET_PROPERTY(objmail,'FROM','xxx@xxx.ca');
      CLIENT_OLE2.SET_PROPERTY(objmail,'To', 'xxx@xxx.ca');
      CLIENT_OLE2.SET_PROPERTY(objmail,'Subject', 'Rapport alerte forecast qty vs sales qty');
      CLIENT_OLE2.INVOKE(objmail,'Send');
      CLIENT_OLE2.RELEASE_OBJ(objmail);
      CLIENT_OLE2.RELEASE_OBJ(objOutlook);
      CLIENT_OLE2.DESTROY_ARGLIST(objarg);

      Thanks for yours helps.

      MK.
        • 1. Re: Send mail with attachment
          Andreas Weiden
          i guess you need to do something after
          CLIENT_OLE2.ADD_ARG(objarg,myFileName); -- filename
          with your arglist, have a look at

          regarding sending mail to outlook from forms
          • 2. Re: Send mail with attachment
            NelsonNelson
            If you don´t mind changing your code... here´s a procedure that worked for me. It accepts up to three attachments:

            procedure mail_files( pSmtp_server VARCHAR2,
            pfrom_name VARCHAR2,
            pto_name VARCHAR2,
            pSubject VARCHAR2,
            pMessage VARCHAR2,
            pMax_size NUMBER DEFAULT 9999999999,
            pFilename1 VARCHAR2 DEFAULT NULL,
            pFilename2 VARCHAR2 DEFAULT NULL,
            pFilename3 VARCHAR2 DEFAULT NULL,
            DEBUG NUMBER DEFAULT 0 ) is
            /*
            This procedure uses the UTL_SMTP package to send an email message.
            Up to three file names may be specified as attachments.

            Parameters are:

            1) from_name (varchar2)
            2) to_name (varchar2)
            3) subject (varchar2)
            4) message (varchar2)
            5) max_size (number)
            5) filename1 (varchar2)
            6) filename2 (varchar2)
            7) filename3 (varchar2)

            eg.

            mail_files( from_name => 'oracle' ,
            to_name => 'someone@somewhere.com' ,
            subject => 'A test',
            message => 'A test message',
            filename1 => '/data/oracle/dave_test1.txt',
            filename2 => '/data/oracle/dave_test2.txt');

            Most of the parameters are self-explanatory. "message" is a varchar2
            parameter, up to 32767 bytes long which contains the text of the message
            to be placed in the main body of the email.

            filename{1,2,3} are the names of the files to be attached to the email.
            The full pathname of each file must be specified. The files must exist
            in one of the directories specified in the init.ora parameter
            UTL_FILE_DIR. All filename parameters are optional: It is not necessary
            to specify unused file parameters (eg. filename3 is missing in the above
            example).

            The max_size parameter enables you to place a constraint on the maximum
            size of message, including all attachments, that the procedure will send.
            If this limit is exceeded, the procedure will truncate the message at
            that point with a '*** truncated ***' message. The default is effectively
            unlimited. However, the text of message body is still limited to 32Kb, as
            it is passed in as a varchar2.

            */


            -- v_smtp_server VARCHAR2(20) := 'localhost';
            v_smtp_server VARCHAR2(32);
            v_smtp_server_port NUMBER := cPort;
            v_SentBy VARCHAR2(100);
            v_directory_name VARCHAR2(100);
            v_file_name VARCHAR2(100);
            v_line VARCHAR2(1000);
            crlf VARCHAR2(2):= CHR(13) || CHR(10);
            mesg VARCHAR2(32767);
            conn UTL_SMTP.CONNECTION;

            TYPE varchar2_table IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;

            file_array varchar2_table;
            i BINARY_INTEGER;
            v_file_handle utl_file.file_type;
            v_slash_pos NUMBER;
            mesg_len NUMBER;
            mesg_too_long EXCEPTION;
            invalid_path EXCEPTION;
            mesg_length_exceeded BOOLEAN := FALSE;
            vcMensajeError VARCHAR2(1000);

            BEGIN

            --Sino se tienen los valores del parametro usa los default del paquete
            IF pSmtp_server IS NULL THEN
            v_smtp_server := cEmailServer;
            ELSE
            v_smtp_server := pSmtp_server;
            END IF;

            IF pfrom_name IS NULL THEN
            v_SentBy := cSentBy;
            ELSE
            v_SentBy := pfrom_name;
            END IF;

            -- first load the three filenames into an array for easier handling later ...

            file_array(1) := pfilename1;
            file_array(2) := pfilename2;
            file_array(3) := pfilename3;

            -- Open the SMTP connection ...
            -- ------------------------
            conn:= utl_smtp.open_connection( v_smtp_server, v_smtp_server_port );

            -- Initial handshaking ...
            -- -------------------
            utl_smtp.helo( conn, v_smtp_server );
            utl_smtp.mail( conn, v_SentBy );
            utl_smtp.rcpt( conn, pto_name );
            utl_smtp.open_data ( conn );

            -- build the start of the mail message ...
            -- -----------------------------------
            mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
            'From: ' || pfrom_name || crlf ||
            'Subject: ' || psubject || crlf ||
            'To: ' || pto_name || crlf ||
            'Mime-Version: 1.0' || crlf ||
            'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || crlf ||
            '' || crlf ||
            'This is a Mime message, which your current mail reader may not' || crlf ||
            'understand. Parts of the message will appear as text. If the remainder' || crlf ||
            'appears as random characters in the message body, instead of as' || crlf ||
            'attachments, then you''ll have to extract these parts and decode them' || crlf ||
            'manually.' || crlf ||
            '' || crlf ||
            '--DMW.Boundary.605592468' || crlf ||
            'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' || crlf ||
            'Content-Disposition: inline; filename="message.txt"' || crlf ||
            'Content-Transfer-Encoding: 7bit' || crlf ||
            '' || crlf ||
            pmessage || crlf ;

            mesg_len := LENGTH(mesg);
            IF mesg_len > pmax_size THEN
            mesg_length_exceeded := TRUE;
            END IF;

            utl_smtp.write_data ( conn, mesg );

            -- Append the files ...
            -- ----------------

            FOR i IN 1..3 LOOP
            -- Exit if message length already exceeded ...
            EXIT WHEN mesg_length_exceeded;
            -- If the filename has been supplied ...
            IF file_array(i) IS NOT NULL THEN
            BEGIN
            -- locate the final '/' or '\' in the pathname ...
            v_slash_pos := INSTR(file_array(i), '/', -1 );

            IF v_slash_pos = 0 THEN
            v_slash_pos := INSTR(file_array(i), '\', -1 );
            END IF;

            -- separate the filename from the directory name ...
            v_directory_name := SUBSTR(file_array(i), 1, v_slash_pos - 1 );
            v_file_name := SUBSTR(file_array(i), v_slash_pos + 1 );

            -- open the file ...
            v_file_handle := utl_file.fopen(v_directory_name, v_file_name, 'r' );
            -- generate the MIME boundary line ...
            mesg := crlf || '--DMW.Boundary.605592468' || crlf ||
            'Content-Type: application/octet-stream; name="' || v_file_name || '"' || crlf ||
            'Content-Disposition: attachment; filename="' || v_file_name || '"' || crlf ||
            'Content-Transfer-Encoding: 7bit' || crlf || crlf ;

            mesg_len := mesg_len + LENGTH(mesg);
            utl_smtp.write_data ( conn, mesg );

            -- and append the file contents to the end of the message ...
            LOOP
            utl_file.get_line(v_file_handle, v_line);
            IF mesg_len + LENGTH(v_line) > pmax_size THEN
            mesg := '*** truncated ***' || crlf;
            utl_smtp.write_data ( conn, mesg );
            mesg_length_exceeded := TRUE;
            RAISE mesg_too_long;
            END IF;

            mesg := v_line || crlf;
            utl_smtp.write_data ( conn, mesg );
            mesg_len := mesg_len + LENGTH(mesg);

            END LOOP;

            EXCEPTION
            WHEN utl_file.invalid_path THEN
            vcMensajeError := 'SFT_K_BLOB.MAIL_FILES-> INVALID_PATH->' || SqlErrm;
            raise_application_error(-20000, vcMensajeError);
            WHEN NO_DATA_FOUND THEN
            NULL; --LLEGA AL FINAL DEL ARCHIVO
            -- All other exceptions are ignored ....
            --WHEN OTHERS THEN
            -- dbms_output.put_line('WHEN OTHERS->' || SqlErrm );
            -- NULL;
            END;

            mesg := crlf;
            utl_smtp.write_data ( conn, mesg );
            -- close the file ...
            utl_file.fclose(v_file_handle);

            END IF;

            END LOOP;

            -- append the final boundary line ...

            mesg := crlf || '--DMW.Boundary.605592468--' || crlf;
            utl_smtp.write_data ( conn, mesg );

            -- and close the SMTP connection ...

            utl_smtp.close_data( conn );
            utl_smtp.quit( conn );

            EXCEPTION

            WHEN OTHERS THEN
            vcMensajeError := 'SFT_K_BLOB.MAIL_FILES-> Error Desconocido->' || SqlErrm;
            utl_smtp.RSET(conn);
            utl_smtp.quit(conn);

            raise_application_error(-20000, vcMensajeError);
            End mail_files;