Beginning with version 2007, Microsoft Access included a feature that seamlessly links your database table to Outlook and makes it easier to collect user data via email messages. This quick tutorial will show you the steps for setting up the email template for collecting the data as well as the steps for processing the collected data.

Creating the email message

Follow these steps to create the email message:

1. Select the table that you need to collect the data for from the Navigation Pane.
2. From the External Data tab on the Ribbon, in the Collect Data group, click on the Create E-mail button.
3. The Collect data through e-mail messages Wizard should open as show in Figure 1.
   

   Figure 1

4. Click Next. The next screen will present you with the option to choose the type of email form you want to use. You can only use either HTML or InfoPath forms (see Figure 2).
   

   Figure 2

   Select HTML if your users do not have InfoPath installed on their machines.

5. Click Next after you have made your choice.
6. If your table contains existing data, the next screen that shows up is where you’ll choose whether the data you’re collecting are new information or for updating the existing data in your table (see Figure 3).
   

   Figure 3

7. Click Next after you have made your selection.
8. The next screen (see Figure 4) allows you to select which fields from your table you would like to include in the email message for collecting the data. You can set the order of the fields or change the label captions for each field.
   

   Figure 4

9. After you have selected all the fields you want to collect, click Next to see the next screen (see Figure 5).
   

   Figure 5

10. This screen allows you to specify which Outlook folder will be used to store the email replies for the collected data. You can change the default location by clicking on the folder’s name and then switch to the Outlook window and select or create a new folder (see Figure 6).
    

    Figure 6

11. From the same screen (see Figure 5), you can also specify if you want all email replies to be processed automatically, which means that the data collected will be automatically added to your Access table. To control how the data is processed, you can click on the link labeled “Set properties to control the automatic processing of replies.” This would open up the Options screen (see Figure 7).
    

    Figure 7

12. From the screen shown in Figure 5, click Next to move on the next step.
13. The next screen specifies how you would like to provide the email addresses for the recipients of your message (see Figure 8).
    

    Figure 8

14. If you select to provide the email addresses from an Access table, clicking Next will present the screen shown in Figure 9.
    

    Figure 9

15. Select the email address field from the current or related table and then click Next.
16. The next screen allows you to specify the subject line and body of the message (see Figure 10).
    

    Figure 10

17. Click Next to see the next screen (see Figure 11). This screen is informational only.
    

    Figure 11

18. Click Next again. The next screen allows you to select specific recipients for the email message, send the message to all recipients (see Figure 12).
    

    Figure 12

19. When you’re ready to send the email message, click Send.
20. When your users get the email message, all they have to do is reply back by filling out the form in the message with the information being requested.

Managing Automatic Reply Processing

After you have set up your data collection email, you can go to the Message Options screen to change its settings. To do so, follow these steps:

1. From the External Data tab on the Ribbon, in the Collect Data group, click on the Manage Replies button. The Manage Data Collection Messages dialog window should come up (see Figure 13).
   

   Figure 13

2. With the data collection message template you want to modify highlighted, click on the Message Options button.
3. The Collecting Data Using E-Mail Options dialog window should open up (see Figure 14).
   

   Figure 14

4. From this screen, you can check or uncheck the options you prefer.
5. Click OK to close the Options window.
6. Click Close on the Manage Data Collection Messages dialog window.

Processing Replies

If you did not set up your email data collection to be processed automatically by Outlook, or if a reply failed to be processed, you can manually process each reply by following these steps:

1. Locate the folder you designated for storing the collected data in Outlook.
2. Right-click on the user’s reply you want to process and select Export data to Microsoft Access.
3. On the Export data to Microsoft Access dialog window, verify the data to be added to the table and click OK.
4. If the export is successful, you should get the confirmation window shown in Figure 15.
   

   Figure 15

5. You must repeat these steps for each reply you want to process.

Conclusion

Since Access 2007, database developers can now automate external data collection via email messages using Outlook. If you have users who need to send out surveys or questionnaires and want to avoid the manual process of collecting feedback, you might consider giving this new feature a try.  More importantly this type of integration is actually possible via a web browser and in the cloud with Access hosting’s remote desktop hosting. All you need to do is sign up for an Office Pro plan with MS Access and Outlook to try it free for 30 days.