[AccessD] Automating Outlook - actually using the email

John Colby jwcolby at gmail.com
Sat Aug 21 09:17:57 CDT 2021

So as Jim pointed out, he thinks this is the long way around the barn.

I half expect to have several to dozens of these orders a month.  I don't
want to have to process the orders manually so the concept is to have
access / outlook running in a vm out on a server on the web.  Outlook will
look for emails periodically, every 10 minutes by default.

This app is an admin kinda thing.  It processes the email from parsing the
order to attaching the program being purchased to an email and mailing it
out to the purchaser.  Automagically.

I am not afraid of classes and I use them to encapsulate functionality.
The next class is clsEmailANPurchase.  The previous two classes handled
obtaining the email from Outlook and storing the parts I am interested in
into tblEmail.

clsEmailANPurchase begins the process of parsing the email body.  The body
will have the fields matching a database on the order server.  It is in the
format FieldName:Value.  Actual test data from such an email looks like

CompanyName:Colby Consulting
ContactPerson:John Colby
ContactEmailAddress:jwcolby at colbyconsulting.com
AddressLine1:1448 Hillside Boularderie
PostalCode:B1B 1B1
PhoneNumber:(999) 578-8149
OrderDate:8/11/2021 7:56:10 AM

I was faced with developing my part while the order entry web page was also
being developed.  Thus I did not know exactly what fields would be in an
order.  That was being figured out by my partner.

In order to allow him to add fields as desired while not being forced to
manually add fields to my order table I built what I will call a "self
building table" class.  So clsEmailANPurchase parses the field name and
value and places it into a key/val pair class, and then store these class
instances into a collection.  The collection can then be passed around for
other classes to use.

We'll get to the specifics but in general...

Dim db as dao.database
dim rst as dao.recordset

set db = currentdb
set rst = currentdb.openrecordset("tblOrder")

I had created a table with a handful of standard fields.  PK and the like.

It turns out you can ask a tble if it has a field by something like

Dim strFldName as string

John W. Colby
Colby Consulting

More information about the AccessD mailing list