[AccessD] Automating Outlook

[Rocky Smolin]

seems 1) simple, and 2) elegant, and 3) effective. Nice design. So is
there a question or problem?

r


On Tue, Aug 24, 2021 at 10:37 AM John Colby <jwcolby at gmail.com> wrote:

> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>