[AccessD] Automating Outlook

[John Colby]

A client comes to a custom written web site.  It has a pretty simple
purchase form.  They choose a "tier", 1,2 or 3.  The tiers turn on
functionality in the Access database they are buying.  They enter their
company name, address and email as well as the person names and emails of
the individuals that will be licensed to run the program.

The license is a monthly or annual fee, based on the seats, which the web
site infers based on how many licensee names / addresses are entered.  The
cost is computed based on the Tier and the seats, a credit card is entered
and a specific (to canada I think) billing company processes the credit
card.
As soon as the info is entered and "buy" is clicked, an email is composed
and sent to me (or more correctly an email account set up by us for this
purpose).  As soon as the processing company processes and accepts the
payment, another email is sent to me (this email account).

I am informed by my partner that the credit card company waits 7 days for a
"charge back" kind of thing, think buyer's remorse, whereupon the money is
released to us and yet another email is sent.

Exactly who sends these emails I am not sure, though I believe that the
first is sent by our order entry server, and the last two by the credit
card processor.
One way or another, I receive the first email.  It contains all of the
fields that the buyer filled in, plus other purchase info such as tier,
costs, discounts, totals pmt etc.  All generated directly by our little web
site.

That is all I need to proceed.  The body of the email has exactly and only
the data described, in a "fieldName:FieldValue" format, where each line
contains the actual field name I will use in my order table - before the
colon - and then the value to place into that field  - after the colon.

I have set up Outlook to "poll" the email server which is sending out that
email.  All the poll happy folks gotta love that I am in fact "polling" a
server somewhere!    Well Outlook is anyway.

I have written an Access database which, when it opens, opens Outlook and
grabs a pointer to the instance.  If Outlook is already open, then it gets
a pointer to that already open instance.

When I dim my Outlook variable, I do so "Withevents", so various and
assorted Outloop objects can raise an event, and My Outlook database will
sink that event.
There is nothing extraordinary or difficult about that.  Anyone who deals
with events in access raising events (think forms, combo boxes, text boxes,
list boxes, check boxes, radio buttons etc etc ad nauseum) knows all about
sinking events.  This is no different, just another object raising events.
This is known as "Office automation", the only thing different is that we
open a pointer to some external Office App and manipulate that object
programmatically from within our Access code.

So... Every X time period - 10 minutes by default - Outlook "polls" the
email server, asking for email.  Any user of Outlook knows all about
setting up Outlook to talk to an email server out on the internet (or
inside of a company).  Nothing magical there.

As Outlook receives each email it raises an event.  Because I dimensioned
my Outlook Object "WithEvent", I create an event hook inside of a class,
and yes it MUST BE A CLASS since only classes can sink events.  When
Outlook raises its event, program control is passed to my event hook.
Program execution out in Outlook literally stops until my code in my event
hook returns.
So I hook the event and do something.  What is irrelevant to this
discussion, all that matters is that:
1) I create a class clsOutlook (or whatever such name I desire)
2) I dimension an Outlook object variable 'Withevents' in the header of
this class
3) I create an event hook in my class to hook whatever events I want to hook
4) I create code to do something when this event hook is entered (the hook
sinks the Outlook event)
5) In the class initialization code I set my 'Withevents' variable to an
Outlook Object.  This is where Outlook opens.
6) I instantiate this class and set a global variable to this instance so
that this class remains open until I close Access
7) I create code in the Terminate event for the class to release the
pointer to Outlook.  Thus when I close my clsOutlook (or whatever I call
it) I don't hang onto the pointer to Outlook.  Automatic cleanup assuming I
do this in Terminate.
This stuff is dead simple.  Many of us automate Office Apps.  Not everyone
sinks events inside of their Access application.
In this case, the event is an email arriving.  The actual email object is
passed to my event sink.  Thus I have access to the fields containing
sender, receiver, time, date, attachments, subject, body etc.

And so I capture all of that stuff into a clsEmail and from there
tblEmail.  Again dead simple.

Once I have the subject and body in a class, I create an order class and
parse the order fields out of the email body variable into an order object,
and store it into a tblOrder.

The only even slightly cool thing I do here is to store each FieldName /
FieldValue into clsKeyVal instances as I parse the email body.  I save
these clsKeyVal instances into a collection, keyed on the key part (field
name).  Thus when it is time to save the data into tblOrder, I have all of
the fields and data stored as clsKeyVal instances and so I can directly use
those to fill in an order record.

>From the order I create a clsClient, pass in the KeyVal (field names)
collection, and pull out the company name / address / contact person etc
and fill in variables in clsClient and from there into tblClient.  Again
dead simple.

The only slightly cool thing I do is use the clsKeyVal instances stored in
the collection I mentioned to go looking for specific fields that "belong
to" the Client.  Remember I stored the clsKeyVal pairs into the collection
using the Key part (the field name) as the key of the collection item.

Need I repeat ... dead simple?

And finally, I create a set of  clsClientAdvisor instances, passing in the
collection of clsKeyVal instances.   I have a table called
tblClientAdvisor, child to tblClient which will contain just the name  /
email address entered way back in the order entry form for each person to
be licensed to use the app.  Each advisor goes into a record in the table
along with their email address.

The only slightly cool thing I do is use the collection of clsKeyVal
instances to directly discover how many advisors I have sold licenses to
and thus need a record in this table.

Once all of that is completed... I have captured all of the order parts,
i.e. normalized an email which was decidedly denormalized and stuffed the
pieces and parts into Parent / child / grandchild etc tables.  Capturing
the PKID of each record created in the class instance for Email, Order,
Client, ClientAdvisor records.  Now I go back and stitch them all together
using the PKIDs I captured along the way, ending up with FKs in each child
part.

The use of classes for each part of the chain is how I organize the whole.
That collection of clsKeyVal instances (order field data parsed out of the
email body) is just passed along to the next class.  Email does the
parsing, passing the collection to clsOrder, which grabs the fields it
needs, then passes that collection to clsClient, which grabs just the
fields it needs, passing the collection to each clsAdvisor, which is the
end of the line, grabbing the fields it needs to build its record (s).

While it took a bit to describe the process, it is all just a chain of
events, triggered by receiving an email.  When I am done I have a record in
tblEmail, TblOrder, tblClient, and several records in tblClientAdvisors
(from one to 5).

And one final time, dead simple.  Give me an email and I'll take it from
there.
-- 
John W. Colby
Colby Consulting