Thanks!
Anjanava Biswas, December 29, 2011 - 10:34 am UTC
So this means something like below -
declare
v_From VARCHAR2(80) := 'yourhelper@fun.com';
v_cc VARCHAR2(80) := 'gethelp@fun.com';
v_Recipient VARCHAR2(80) := 'yourhelper@fun.com';
v_Subject VARCHAR2(80) := 'test Subject';
v_Mail_Host VARCHAR2(50) := 'hub.fun.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.Rcpt(v_Mail_Conn, v_cc); -- To CC recepient
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 ||
'Cc: ' || v_cc || crlf ||
'Content-Type: text/html;' ||crlf ||
'Hello this is a test email');
utl_smtp.Quit(v_mail_conn);
end;
December 29, 2011 - 11:53 am UTC
yes.
Emaling via oracle
A reader, December 29, 2011 - 1:26 pm UTC
Is it possible to do any email formatting using SMTP package/protocol (i.e font size, color, type) or we have to send it in HTML format. We assume diff email clients will open it as HTML or plain text depending on how it is configured.
December 29, 2011 - 5:03 pm UTC
Html is the open way to do it. I am sure that various clients like ms exchange have proprietary ways as well. But html would be the way to go
Smtp is just a transport... it cannot do formatting
Anjanava Biswas, December 29, 2011 - 5:12 pm UTC
I did some HTML formatting using the below code-
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 ||
'Cc: ' || v_cc || crlf ||
'Content-Type: text/html;' ||crlf ||
'Hello this is a test email');
The line 'Content-Type: text/html;' makes the email as HTML I presume. So, if you write something like below it should appear in Red fonts in the email
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 ||
'Cc: ' || v_cc || crlf ||
'Content-Type: text/html;' ||crlf ||
'<html><body><font face="arial" color="red">Hello this is a test email</font></body></html>');
December 30, 2011 - 10:23 am UTC
HTML Formatted Emails
Barry Chase, December 29, 2011 - 11:11 pm UTC
If you decide to add binary attachments you will want to consider adding the appropriate mime headers too. Below is a snippet of the code that we use in my job that permits us to have html formatted emails, email priority, cc/bcc, etc. Take notice of the boundary header entry which is important for the attachments to work correctly. The original code uses utl_smtp, but I switch the code to utl_tcp and added new features to the original code that I found online..
Note: Full code (open-source) can be found at my website myoracleportal.net
-- build the start of the mail message ...
-- -----------------------------------
rc := utl_tcp.write_line ( conn, p_datestring );
rc := utl_tcp.write_line ( conn, 'From: ' || from_name );
rc := utl_tcp.write_line ( conn, 'Subject: ' || subject );
rc := utl_tcp.write_line ( conn, 'To: ' || to_name );
IF cc_name IS NOT NULL
THEN
rc := utl_tcp.write_line ( conn, 'Cc: ' || cc_name );
END IF;
IF bcc_name IS NOT NULL
THEN
rc := utl_tcp.write_line ( conn, 'Bcc: ' || bcc_name );
END IF;
rc := utl_tcp.write_line ( conn, 'Mime-Version: 1.0' );
-- Set priority:
-- High Normal Low
-- 1 2 3 4 5
IF ( priority IS NOT NULL )
THEN
rc := utl_tcp.write_line ( conn, 'X-Priority: ' || priority );
END IF;
rc := utl_tcp.write_line ( conn, 'X-Mailer: ' || mailer_id );
rc :=
utl_tcp.write_line
( conn
, 'Content-Type: multipart/mixed; boundary="=_mixed 0052287A85256E75_="' );
rc := utl_tcp.write_line ( conn, '' );
rc :=
utl_tcp.write_line
( conn
, 'This is a Mime message, which your current mail reader may not' );
rc :=
utl_tcp.write_line
( conn
, 'understand. Parts of the message will appear as text. If the remainder' );
rc :=
utl_tcp.write_line
( conn
, 'appears as random characters in the message body, instead of as' );
rc :=
utl_tcp.write_line
( conn
, 'attachments, then you''ll have to extract these parts and decode them' );
rc := utl_tcp.write_line ( conn, 'manually.' );
rc := utl_tcp.write_line ( conn, '' );
rc := utl_tcp.write_line ( conn, '--=_mixed 0052287A85256E75_=' );
rc :=
utl_tcp.write_line ( conn
, 'Content-Type: text/html; charset=8859-1' );
rc := utl_tcp.write_line ( conn, '' );
rc := utl_tcp.write_line ( conn, '<html>' );
rc := utl_tcp.write_line ( conn, '<head>' );
rc :=
utl_tcp.write_line
( conn
, '<meta http-equiv="Content-Type" content="text/html;charset=8859-1">' );
rc := utl_tcp.write_line ( conn, '<title>' );
rc := utl_tcp.write_line ( conn, subject );
rc := utl_tcp.write_line ( conn, '</title>' );
rc := utl_tcp.write_line ( conn, '</head>' );
rc := utl_tcp.write_line ( conn, '<body>' );
WHILE pos < msg_length
LOOP
rc :=
utl_tcp.write_line ( conn
, dbms_lob.SUBSTR ( MESSAGE
, offset
, pos ));
pos := pos + offset;
offset := LEAST ( bytes_o_data, msg_length - offset );
END LOOP;
rc := utl_tcp.write_line ( conn, '<BR><BR>' );
rc := utl_tcp.write_line ( conn, '</body></html>' );
rc := utl_tcp.write_line ( conn, '' );
rc := utl_tcp.write_line ( conn, crlf );
-- Append the file BLOB ...
-- ----------------