VB, Crystal Reports, Oracle - Need Solution to Business Problem 
Author Message
 VB, Crystal Reports, Oracle - Need Solution to Business Problem

Hello, can someone tell me the best way i can accomplish my goal.  And
tell me where i can find an example program or code that I can use.

Goal:  I want to automate an e-mail that is sent out each week to
three different groups of people (supervisors, PI's, and authors)
based upon when a protocol expires.  Every expiring protocol has ONE
corresponding author and ONE corresponding PI.  Most of the time, the
PI of the protocol is the author of the protocol.  However, the author
may or may not be the same person as the PI for a respective expired

Upon my inspection of the Oracle database, authors and PI's ARE
physically the same table.  However, in the current system, a "view"
is used to replicate the table, so the one physical table can be used
for two different logical tables.

Current System
1.  Currently, a Crystal Reports file (.rpt) is run every Tuesday to
determine which protocols are expiring within the next 2 weeks and the
corresponding investigator and title to the expiring protocol.
2.  An .xls file is created using the export feature from the .rpt
file after the .rpt file is run.
3.  This .xls file is then manually attached and e-mailed to the 5
supervisors.  This is performed every week and the e-mail addresses do
not change.  The text within the e-mail sent to the supervisors is the
same each week.  However, the information within .xls is always
different, but the file has the same name every week (i.e.

E-mailing to everyone else
4.  The .xls file has 8 columns (Protocol, Expires, PILast, PIFirst,
Alast, AFirst, PIMail, Title)
5.  After the supervisors e-mails are sent, in the current system, a
Word mail merge is run, using the same .xls file as the data source
for the mail merge, to send out the rest of the e-mails.
6.  The PI's and authors e-mail addresses (where the e-mail is to be
sent) are contained within the "PIMail" column in the .xls file.  (See
attached file from in the other e-mail)
7.  **The author of the expiring protocol's e-mail address will only
appear in the "PIMail" column if the author is a different person than
the PI.  The expired protocls with a different author and PI appear
are listed twice in the .xls file. You can see the protocol number
listed in bold at the bottom.  (remember, that the .xls was exported
from the crystal report, so it was the crystal report that had code to
determines this).
8.  The text within the Word template is merged with the information
in some of the columns in the .xls file.
9.  Then the Word mail merge sends out the merged text as the body of
an e-mail to each of the PI's who have expired protocols.
10.  In the e-mail the PI and/or author only see the expired protocol
that pertains to him/her.  He/she does not see the whole list, like
the supervisors do.
11.  The e-mails sent using the Word mail merge appear in the "Sent
Items" folder of the guy who currently runs the mail merge.

P.S. The e-mail addresses are stored in the Oracle database like this

run, one of the report fields automatically removes the "smpt:" from
the e-mail address because otherwise, the mail would be undeliverable.

thanks everyone.

Fri, 22 Jul 2005 23:07:44 GMT  
 [ 1 post ] 

 Relevant Pages 

1. VB, Crystal Reports, Oracle - Need Solution to Business Problem

2. VB, Crystal Reports & Oracle Problem

3. A Problem when connecting to a Crystal report through Oracle via VB

4. Problems connecting to a Crystal report through Oracle via VB

5. Runtime Problems with VB, Crystal Reports & Oracle

6. Problems connecting to a Crystal report through Oracle via VB

7. A Problem when connecting to a Crystal report through Oracle via VB

8. VB5 Crystal Report to Oracle connection problem

9. Newbie problems using Crystal reports with Oracle - please help

10. Crystal Reports 7.0 Distribution with the Oracle direct driver problem

11. Problems with Oracle ODBC-driver and Crystal reports

12. Problems with SQL SERVER vs Oracle Crystal Reports!!!


Powered by phpBB® Forum Software