Downloaded a sample Form to send email using Outlook, default account installed on Client's PC, added Email Body and a Loop in Procedure to add multiple recipients but it sends to only one recipient. Is it right approach or my added code is wrong ?
Text Items
Recipients ( multiple comma separated email addresses )
Subject
Attachment
Email Body
Buttons
Send Email
Begin
Email_File(:RECEPIENT_EMAIL,
:EMAIL_SUBJECT,
:FILE_ATTACHED,
:EMAIL_BODY
);
Exception
When others then
Message (SQLERRM);
Message (SQLERRM);
End;
Close
exit_form (no_validate);
Procedure:
/*
** email_file - E-mail with optional attachment using Microsoft Outlook via client side OLE2 calls.
**
** Usage: email_file (<recipient>, <subject>, <path to file attachment>);
**
** if recipient is null, the new e-mail window will be displayed. Else, the mail will
** be sent silently. If there is no attachment, pass NULL.
**
** Notes: - Original source code from Oracle Document ID 188545.1, modified for 10g. See comments
** below for changes required to make it work.
** - Requires a working webutil installation.
*/
PROCEDURE email_file (rcplist varchar2,
subject_in varchar2,
filename_in varchar2,
vEmailBody long) IS
/*declaration of the Outlook Object Variables*/
application client_ole2.OBJ_TYPE;
hMailItem client_ole2.OBJ_TYPE;
hRecipients client_ole2.OBJ_TYPE;
recipient client_ole2.OBJ_TYPE;
/*declaration of the argument list*/
args client_OLE2.LIST_TYPE;
msg_attch client_OLE2.OBJ_TYPE;
attachment client_OLE2.OBJ_TYPE;
des VARCHAR2(80) := 'FILE_NAME';
attch VARCHAR2(80) := filename_in;
xx NUMBER:=0;
begin
/*create the Application Instance*/
application:=client_ole2.create_obj('Outlook.Application');
args:=client_ole2.create_arglist;
client_ole2.add_arg(args,0);
hMailItem:=client_ole2.invoke_obj(application,'CreateItem',args);
client_ole2.destroy_arglist(args);
/* If there is no recipient passed in, display the new e-mail on screen. */
if rcplist is null then
CLIENT_OLE2.INVOKE(hMailItem,'Display');
end if;
/* set the Subject and Body properties*/
/* Subject must be set first. If not and rcp_list is null, the subject will not get filled in. */
client_ole2.set_property(hMailItem,'Subject',subject_in);
/* Setting the body text stops the signature from being displayed. Anybody know how to do both? */
-- client_ole2.set_property(hMailItem,'Body','this is body text');
msg_attch := client_OLE2.GET_OBJ_PROPERTY(hMailItem,'Attachments');
/* Attach the 1st file. Commented-out lines caused errors. Works fine without them. */
args := client_OLE2.CREATE_ARGLIST;
client_OLE2.ADD_ARG(args,attch);
attachment := client_OLE2.INVOKE_OBJ(msg_attch,'Add',args);
client_ole2.destroy_arglist(args);
-- client_OLE2.SET_PROPERTY(attachment,'name',des);
client_OLE2.SET_PROPERTY(attachment,'position',1);
-- client_OLE2.SET_PROPERTY(attachment,'type',1);
-- client_OLE2.SET_PROPERTY(attachment,'source',Attch);
args := client_OLE2.CREATE_ARGLIST;
client_OLE2.ADD_ARG(args,Attch);
-- client_OLE2.INVOKE(attachment,'ReadFromFile',args);
client_OLE2.DESTROY_ARGLIST(args);
/*Get the Recipients property of the MailItem object:
Returns a Recipients collection that represents all the Recipients for the Outlook item*/
args:=client_ole2.create_arglist;
hRecipients:=client_ole2.get_obj_property(hMailItem,'Recipients',args);
client_ole2.destroy_arglist(args);
-- added for multiple recipients
for rec in (
select substr(rcplist,
instr(','||rcplist||',', ',', 1, rn),
instr(','||rcplist||',', ',', 1, rn+1)
- instr(','||rcplist||',', ',', 1, rn) - 1) email_add
from ( select level rn from dual
connect by level <= length(rcplist)-length(replace(rcplist,',',''))+1)
)
loop
xx:=xx+1;
:no_recipients := xx;
message (rec.email_add, Acknowledge);
/*Use the Add method to create a recipients Instance and add it to the Recipients collection*/
args := client_ole2.create_arglist;
client_ole2.add_arg (args, rec.email_add);
recipient := client_ole2.invoke_obj(hRecipients,'Add',args);
/* put the property Type of the recipient Instance to value needed (0=Originator,1=To,2=CC,3=BCC)*/
client_ole2.set_property(recipient,'Type',1);
/*Resolve the Recipients collection*/
args:=client_ole2.create_arglist;
client_ole2.invoke(hRecipients,'ResolveAll',args);
end loop;
client_ole2.destroy_arglist(args);
if rcplist is not null then
client_ole2.invoke(hMailItem,'Save',args);
end if;
client_ole2.destroy_arglist(args);
client_ole2.set_property(hMailItem,'Body',vEmailBody);
/*Save the mail. Not needed if the note is displayed on screen. */
/*Send the mail if a recipient was passed in. */
if rcplist is not null then
args:=client_ole2.create_arglist;
client_ole2.invoke(hMailItem,'Send',args);
client_ole2.destroy_arglist(args);
end if;
/*Release all your Instances*/
client_ole2.release_obj(application);
client_ole2.release_obj(hRecipients);
client_ole2.release_obj(recipient);
client_ole2.release_obj(hMailItem);
end;
Regards.