Collecting info by email

[Colman]

I have an ambitious idea which I just need to know if it is possible, to save me wasting time trying and failing.

My client uses a database of mine to control product changes. For each proposed product change, they create a change record and a number of related approval records (one for each person that needs to approve the change). The database sends an email to each of the approvers and they respond by going into the database to record either an accept or a reject in the respective approval records.

Is it possible for this response to be performed by the approvers replying to the emails instead of having to go into the database?

I know that data can be collected by Access from an email message but as I understand it, it can either add records to a table, or update a record that has the email address in one of its fields.

In my case, the email address would be in a field of the record that needs updating but it would also appear in many other records in that table as well. These mustn't be updated.

Anyone have any experience with this. I'd be grateful for any advice.

 

[spikepl]

An alternative approach is to use the built-in facilities in Access. Set up some temp table to be updated/appended to, collect the data using this fancy Access feature and so get the responses into the table, and then extract data from that table and process as required.

 

[Colman]

Thanks Uncle Gizmo for taking the time to look at this. I'm presently leaning towards the Access built in feature as it looks like it might be simple enough, even for me.

Spikepl, using the Access feature, I wonder if it is possible for the database to generate the outgoing email in response to a single event in the database, or does it always involve manually working through a wizard. I'm sure I could do code that provides all the information needed, but does the feature allow that?

My clients are used to creating a change record and pressing a button that fires off a notification email. I don't think they would want to make this any more elaborate.

 

[spikepl]

I don't know - I'd be surprised if the feature couldn't be triggered in code or replicated somehow. I have not used it, so what I would do is some googling, make a small test, and then see what I could come up with. I cannot imagine that you are the only one who needs to run this from code.

 

[jamesmor]

I may have missed something but:

When a change record is created, how does the approvers know which record they need to look at?

I would assume there's some type of reference number or similar which is stored in the database.

Then it would be a matter of parsing the emails looking for that reference number.