With 7.3.2 which is the database you have, sending email is "hard" from the
database. It can be done, but it takes an amount of work.
With D2k, we can use ole automation to do this and since you are willing to do
it from there -- here we go.
This is a support note on this very topic:
Exchange using OLE2
Keywords: DEVELOPER/2000;FORMS;REPORTS;FAX;EMAIL;EXCHANGE;OLE;OLE2
_______________________Programmatically sending a Fax/Email
INTRODUCTION:
-------------
This bulletin explains how to programmatically send a fax/email message from a
Forms/Reports application via Microsoft Exchange without any kind of user
interaction. It shows the general usage of the 'Mail' package as well as a fully
coded Forms sample application.
The concept of OLE (Object Linking and Embedding) automation is used to control
the OLE server application (Microsoft Exchange) using the client application.
The client in this case may be a Developer/2000 Forms or Reports application. It
uses the objects and methods exposed by the OLE Messaging Library which are
much more robust than the MSMAPI OCX controls and allow access to many more MAPI
properties.
Oracle provides support for OLE automation in its applications by means of the
OLE2 built-in package. This package contains object types and built-ins for
creating and manipulating the OLE objects. Some of these built-ins for e.g.
OLE2.create_obj, OLE2.invoke, OLE2.set_property have been extensively used in
the code.
GENERAL USAGE:
--------------
The Mail package contains three procedures:
1. Procedure Mail_pkg.logon( profile IN varchar2 default NULL);
----------
Use this procedure to logon to the MS Exchange mail client. The procedure
takes a character argument which specifies the Exchange Profile to use for
logon. Passing a NULL argument to the logon procedure brings up a dialog box
which asks you to choose a profile from a list of valid profiles or create a new
one if it doesn't exist.
2. Procedure Mail_pkg.send(
--------- Recipient IN varchar2,
Subject IN varchar2 default NULL,
Text IN varchar2 default NULL,
Attachment IN varchar2 default NULL
);
This is the procedure that actually sends the message and attachments, if
any, to the recipient. The recipient may be specified directly as a valid email
address or as an alias defined in the address book. If the message is intended
for a fax recipient then a valid alias must be used that is defined as a fax
address in the address book.
3. Procedure Mail_pkg.logoff;
-----------
This procedure closes the Exchange session and deallocates the resources used
by the OLE automation objects.
SAMPLE FORMS APPLICATION:
-------------------------
1. Create the Mail Package using the following two Program Units:
(a) Mail Package Spec
(b) Mail Package Body
Mail Package Spec:
------------------
PACKAGE Mail_pkg IS
session OLE2.OBJ_TYPE; /* OLE object handle */
args OLE2.LIST_TYPE; /* handle to OLE argument list */
procedure logon( Profile IN varchar2 default NULL );
procedure logoff;
procedure send( Recp IN varchar2,
Subject IN varchar2,
Text IN varchar2,
Attch IN varchar2
);
END;
Mail Package Body:
------------------
PACKAGE BODY Mail_pkg IS
session_outbox OLE2.OBJ_TYPE;
session_outbox_messages OLE2.OBJ_TYPE;
message1 OLE2.OBJ_TYPE;
msg_recp OLE2.OBJ_TYPE;
recipient OLE2.OBJ_TYPE;
msg_attch OLE2.OBJ_TYPE;
attachment OLE2.OBJ_TYPE;
procedure logon( Profile IN varchar2 default NULL )is
Begin
session := ole2.create_obj('mapi.session');
/* create the session object */
args := ole2.create_arglist;
ole2.add_arg(args,Profile);/* Specify a valid profile name */
ole2.invoke(session,'Logon',args);
/* to avoid the logon dialog box */
ole2.destroy_arglist(args);
End;
procedure logoff is
Begin
ole2.invoke(session,'Logoff');
/* Logoff the session and deallocate the */
/* resources for all the OLE objects */
ole2.release_obj(session);
ole2.release_obj(session_outbox);
ole2.release_obj(session_outbox_messages);
ole2.release_obj(message1);
ole2.release_obj(msg_recp);
ole2.release_obj(recipient);
ole2.release_obj(msg_attch);
ole2.release_obj(attachment);
End;
procedure send( Recp IN varchar2,
Subject IN varchar2,
Text IN varchar2,
Attch IN varchar2
)is
Begin
/* Add a new object message1 to the outbox */
session_outbox := ole2.get_obj_property(session,'outbox');
session_outbox_messages := ole2.get_obj_property(session_outbox,'messages');
message1 := ole2.invoke_obj(session_outbox_messages,'Add');
ole2.set_property(message1,'subject',Subject);
ole2.set_property(message1,'text',Text);
/* Add a recipient object to the message1.Recipients collection */
msg_recp := ole2.get_obj_property(message1,'Recipients');
recipient := ole2.invoke_obj(msg_recp,'add') ;
ole2.set_property(recipient,'name',Recp);
ole2.set_property(recipient,'type',1);
ole2.invoke(recipient,'resolve');
/* Add an attachment object to the message1.Attachments collection */
msg_attch := ole2.get_obj_property(message1,'Attachments');
attachment := ole2.invoke_obj(msg_attch,'add') ;
ole2.set_property(attachment,'name',Attch);
ole2.set_property(attachment,'position',0);
ole2.set_property(attachment,'type',1); /* 1 => MAPI File Data */
ole2.set_property(attachment,'source',Attch);
/* Read the attachment from the file */
args := ole2.create_arglist;
ole2.add_arg(args,Attch);
ole2.invoke(attachment,'ReadFromFile',args);
ole2.destroy_arglist(args);
args := ole2.create_arglist;
ole2.add_arg(args,1); /* 1 => save copy */
ole2.add_arg(args,0); /* 0 => no dialog */
/* Send the message without any dialog box, saving a copy in the Outbox */
ole2.invoke(message1,'Send',args);
ole2.destroy_arglist(args);
message('Message successfully sent');
End;
END;
2. Create a block called MAPIOLE with the following canvas layout:
|-------------------------------------------------------------|
| |
| Exchange Profile: |====================| |
| |
| To: |============================| |
| |
| Subject: |============================| |
| |
| Message: |============================| |
| | | |
| | | |
| | | |
| | | |
| | | |
| |============================| |
| |-----| |
| Attachment: |============================| |SEND | |
| |-----| |
|-------------------------------------------------------------|
The layout contains 5 text-itmes:
- Profile
- To
- Subject
- Message (multiline functional property set to true)
- Attach
and a 'Send' button with the following WHEN-BUTTON-PRESSED trigger:
---------------------------
mail_pkg.logon(:profile);
mail_pkg.send(:to,:subject,:message,:attch);
mail_pkg.logoff;
CONCLUSION:
-----------
This bulletin explained how to create a sample Forms application which can send
a fax/email using OLE automation. The same concept/code can be extended to
programmatically fax/email an Oracle Report without any user interaction. In
order to fax a report, first generate the report output as a PDF file and then
send this to the fax recipient as an attachment using the same Mail Package.