Hello! I am trying to create a pl/sql procedure that lets me send an email and include an existing file to a email address. So far, I found information on how to send a file and create an attachment with information I put in the procedure. This is NOT what I'm trying to do. I'm trying to send an email and include an attachment for a file that already exists. I need the pre-existing file to be sent to the email recipient.
This is how far I've gotten, but this only allows me to CREATE an attachment with the information I put in it from the procedure. I got it from the following site:
http://www.orafaq.com/wiki/Send_mail_from_PL/SQL
DECLARE
v_From VARCHAR2(80) := 'cchang@mycompany.com';
v_Recipient VARCHAR2(80) := 'cchang@mycompany.com';
v_Subject VARCHAR2(80) := 'Weekly Invoice Report';
v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
'This is a test'|| crlf || -- Message body
'of the email attachment'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="ap_inv_supplier_cc10.txt"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="ap_inv_supplier_cc10.txt"'|| crlf ||
crlf ||
'TXT,file,attachment'|| crlf || -- Content of attachment (THIS IS MY PROBLEM! I NEED TO BE ABLE TO ATTACH AN EXISTING FILE, NOT CREATE A NEW ONE)
crlf ||
'-------SECBOUND--' -- End MIME mail
);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
/