Data Collection Through Email
One of the coolest new features in Access 2007 is the ability to gather data through emailed forms. These forms can be used to either update existing data or to gather new data. The forms can either be rich InfoPath forms that show up in-place in Outlook 2007 email messages, or they can be very simple HTML forms that can be completed by users of practically any email client. When the forms are returned to your machine, the data is extracted from the email message (either automatically or manually at your discretion) and uploaded to the database. In fact the data can even be linked to any linked table in Access, so can be stored in SharePoint or even SQL Server if you'd like. Data Collection is a very handy way to do a variety of things, from gathering simple data (e.g. what are you bringing to the pot luck) to periodically updating more complex data (e.g. annual updates to customer contact information).
HTML Data Collection
We'll walk through 2 scenarios in this post - one for collecting new data with an HTML form, the other for updating existing data with an InfoPath form. Data collection works on tables and queries, so the first step is to select a table or query from the Nav Pane. You can either right-click on the object and select "Collect and Update Data via E-mail"
or you can click on "Create Email" under the "Collect Data" chunk of the "External Data" tab in the ribbon UI.
(Click image to enlarge)
That launches a wizard to create and send the email. The first step of the wizard describes the process:
(Click image to enlarge)
Next, you choose whether to send InfoPath or HTML mail. InfoPath provides a rich experience with dropdown controls, in-form validation, and so on. Although HTML pages can contain these things, HTML mail cannot as they're blocked by most firewalls and email gateways. As a result, HTML forms are simple edit controls. In this case, we'll choose HTML:
(Click image to enlarge)
The next step is to choose which fields to include in the emailed form:
(Click image to enlarge)
Then you choose what happens to the mails when they come back. You can either have the replies processed automatically or manually do it yourself:
(Click image to enlarge)
If you choose to process replies automatically, you can set the following options to govern how that automatic processing works by clicking on the "Set properties..." link on the wizard dialog.
Next, you have to tell Access who to send the forms to. You can either use a link to a table of email addresses or you can simply type a list of names into the email header:
(Click image to enlarge)
The next to the last step is to create a subject line for the email and (optionally) type an introduction that goes in the top of the email, above the form:
(Click image to enlarge)
Finally, you can hit the "Create" button and we'll create the email message, and it is ready to send - all you need to do is hit "Send" in the Outlook send note:
(Click image to enlarge)
The final email message will look something like this:
(Click image to enlarge)
InfoPath Data Update Mail
The process for creating a mail to update a table with a form sent through InfoPath is quite similar. If the table or query you select in the first step has any data in it, you'll be asked if you'd like to simply collect new information or update the existing information (this pane of the wizard didn't show in the example above because the table was empty). Users who are asked to update information can also add new records at the same time. In this case, we'll update the existing information:
(Click image to enlarge)
When automatically processing replies for data updates, you can control what happens to additional records (either automatically adding or discarding them).
(Click image to enlarge)
In this example, we'll use a list of email names in the database rather than manually typing them into the Outlook mail note:
(Click image to enlarge)
Just as before, you can set the email header and introduction. In addition, you can choose where the addresses are attached to the mail (e.g. so you can set them to the BCC line and prevent everyone from seeing them).
(Click image to enlarge)
Since the email that is sent will contain data (rather than simply a form), there's a warning that you're sending out potentially confidential or sensitive data, and to verify what you're doing.
(Click image to enlarge)
The last page of the wizard shows each of the email recipients, and allows you to verify that you're mailing the right list. This allows you to make sure that the mails go to the right people, since you're pulling the addresses from a table rather than manually typing them in as you did in the previous example.
(Click image to enlarge)
The final InfoPath email is a rich form that users can update directly from the Outlook email reply note.
(Click image to enlarge)
Next Time
In the next post, I'll show what happens when the replies come back.
