Automate Access to Send Emails and Receive Responses with Outlook

[padlocked17]

Does anyone know how or where I could start to perform the following action:

1. When a particular field has content entered in it and the record is updated, have an email automatically sent through Outlook with the Voting "Yes" or "No" buttons in the email.

2. Then when that email is received by the specified recipient, they select either Yes or No and then the date is entered into another field in the Database.

Overall picture is that we track students who come through our training facility. We'd like to be able to enter the date that a form is mailed out to their office, and have an email generated and sent to the office that we have the student associated with.

Once the unit receives the email, they reply to that email that was sent and once the email is received back to us, it's automatically processed and the date is automatically entered into the Access program acknowledging receipt of the form.

Anyone know where to start?

 

[Moniker]

You can automatically send email like this (put it in a "Send Email" button or the AfterUpdate event of a field):

DoCmd.SendObject acSendNoObject, , , MailDistribList, , , MailSubject, MailMessage, False

MailDistribList can be one or more email addresses. Separate multiples with a semi-colon.

However, the DoCmd method of sending email won't allow you to set it up as a poll (a yes/no auto-response email).

You're looking at something even more complex if you want to read Outlook's inbox and have it detect responses. Here's your starting point though:

1) Get familiar with the Outlook Object. (Add it as a reference to expose it.) You'll have to define it like you define any object. Generically, it'd be something like this:

Dim OLApp As Outlook.Application
Dim OLMsg As Outlook.MailItem

Set OLApp = New Outlook.Application
Set OLMsg = New OLApp.MailItem

2) You'll have to look at all the available methods and properties (just scroll through IntelliSense on each object) and see if one of them stands out as to how to make a polling email.

3) Once you've determined how to do that, either figure out or search this forum for examples on how to send emails through the object. It's not very difficult.

4) It doesn't remain easy. Polling Outlook to look for certain emails will require:

4a) A timer event to look at the Outlook Inbox every few minutes (or any timing of your choice) scanning for a specific email, and

4b) A way to make sure that the latest forwarded stupid joke isn't an answer to your poll.

In order to do this, you'll need to give your original outbound email a very unique subject line that will not likely be replicated by other emails. Therefore, in the subject line of your email, you'll need something like "Response Request: Student #XXXXX" or whatever you deem to be unique enough as to not cause false positives.

5) Set up a new rule in your Outlook that takes emails with your unique subject line and places them in a special folder, perhaps one called "Poll Responses". This helps in avoiding false positives.

6) Scan that folder at the timer interval of your choice looking for new emails. The trick here is going to be to store internally in the DB the time of the last successful poll result import. This way, you don't retrieve the same answer twice. Pseudo-coded:

If NewEmail.DateReceived > StoredDateReceived Then process it.

7) Using the same method as above, cycle through the IntelliSense methods and properties and find the one that allows you to determine the result of a yes/no poll.

8) Get that info and pat yourself on the back.

Really, you're asking to do something that is fairly complex, but I'm fairly positive it can be done. I tried to outline how I would about doing it. If there's a better way, I'm willing to hear it out, but I don't know why what I'm suggesting wouldn't work.

 

[GolferGuy]

AirForceRuss, What Moniker has outlined sounds right to me. I had a database 10 years ago that did sort of what you are asking. The database sent out e-mails to get managment approval for capital improvement projects, then received the approval/disapproval back from each managment person and recorded the results in the database. I did the Access work and a co-worker did the Outlook portion, so I never really did the e-mail handling myself, but I do know that this does work, and that Moniker has done a great job of outlining what needs to be done. Good luck on this project, and please keep us updating on your progress.

 

[padlocked17]

Thank you both for your input.

I'm going to start attempting to wrap my head around all of this and see what I can't start working out.

If only this was the sole feature on my plate I was trying to knock out (Haha, but isn't that always the complaint ... always more to do).

I'll see about getting started and post my woes up here.

Thanks,

 

[padlocked17]

Well at first glance it appears that I've found the Property "VotingOptions" and "VotingResponse" for use with the Outlook.MailItem

There's a good start.

I'll just edit this thread as I go with more info for others/help for me.