Section 19.5. Collecting Info by Email | Access 2007[c] The Missing Manual

19.5. Collecting Info by Email

There are many more dedicated email users than database mavens in the world. So it would be pretty nifty to have a way to get data from other people by just having them send you an email. The designers who work on Access think so too. They've added one other way to pull data into Access from other sources: You can collect it by email.

Here's how it works:

You pick a table that needs information.
You pick a bunch of people who can provide that information. (You need a list of email addresses, which you can either type in by hand or pull out of a table in your database.)
Access sends an email message to all of them with a form they can fill out. The form lets them fill in the data for one record's worth of information (although a recipient can fill out the same form multiple times if necessary). To see a quick preview of what this email message might look like, jump ahead to Figure 19-19.
The results of that form get emailed back to you.
For each email you receive, Access inserts a record in your table.

Often, this collect-by-email feature is used to get information about people. For example, you may have a table that has a list of contacts. You can send each person an email message and get them to supply their personal information (address, phone number, and so on). Of course, you can also use the feature to collect other types of information, like a list of items people want to bring to the company potluck.

The collect-by-email feature has a few ground rules:

You need to use Microsoft Outlook 2007 (the email software that's included with Office 2007) to send your message and receive the responses . So if you haven't configured Outlook yet with your email details, then you should do that before you go any further. Your recipients can use whatever kind of email program they want.
You can only insert information, not update it . (There's one exception. You can update a table if each record has the recipient's email address in a field, because Access can figure out which record to update by matching the email address of the responder to the email address in the table.)
If people fill in the wrong data (for example, putting text in a numeric field), Access can't create the record . It's then up to you to figure out what went wrong, and correct the problem.
You'll probably need to spend some time reviewing the data other people have submitted . People are notoriously careless when filling out forms on a computer. They may type their names completely in lowercase letters , leave out important information, make spelling mistakes or off- color jokes, and so on.

In the following sections you'll see how to create the email message you need, and then get the data into your table.

19.5.1. Creating an Email Message

The first step ( assuming you've already installed Outlook and configured your email account in it) is to identify the table where you want to insert data. In the following example, you'll see how to add prospective candidates to Dating Service database's Bachelors table. If you want to try this out with your friends , you can find the Dating Service database on the "Missing CD" page at www.missingmanuals.com.

Here's how to do it:

Choose External Data Collect Data Create E-mail .

A wizard appears. The first step lists all the steps you need to go through to get your data.
Click Next to move on .

The second step lets you pick the type of form you want to use.
Choose "HTML form", and then click Next .

This choice tells Access to use HTML tags in its email message. Using these tags, Access can create a form with attractive formatting and text boxes where the recipient can type in information.

The only other option (Microsoft Office InfoPath form) is turned off unless you have the InfoPath application installed on your computer. InfoPath is only included in certain editions of Office, and it's most commonly used by big companies. Although it's a great program for filling out forms, it has one serious drawbackin order to use an InfoPath form, all your recipients need to have the InfoPath software installed on their computers. For that reason, the HTML option's usually better.
The next step asks whether you want to collect new information or update existing information. Choose an option, and then click Next .

Usually, you'll choose "Collect new information only". This option's the one to use in the Dating Service database, because you want to get the information that you need to insert a record in the Bachelors table for each recipient.

If you choose "Update existing information", your table must include the recipient's email addresses. For example, you can use this approach if you have a set of records in the Bachelors table that you want to update. Each bachelor will receive an email that they can use to change their current details.

Tip: You can also use the update option if you have the email addresses in another table that's related to the table you want to fill. For example, you could update a Projects table with the current status of every project if it includes a ProjectManagerID field that points to a record in a ProjectManagers table, which in turn contains the email address.

Pick the fields you want to collect (Figure 19-15) .

To add a field, select it in the "Fields in table" list, and then click the > button. Or, click the >> button to transfer all the fields in one go.

Figure 19-15. You can collect as few or as many fields as you want. But Access forces you to include all the table's required fields, which appear with an asterisk (*) in front of them. (In this example, that's FirstName, LastName, and PhoneNumber.)

Note: You don't see your ID AutoNumber field in the "Fields in table" list. Access knows it needs to generate that number itself, so it doesn't bother asking anyone to supply a value. You also don't see multivalue fields or attachment fields, because Access can't create forms for these types of data.

Optionally, provide clearer names for your fields .

For example, the label "Your favorite food is" might be clearer than the field name FoodPreference. To change a label, select it in the list, and then change the text box that appears underneath.

You can also turn on the Read-only checkbox so that people can't change a field value. This option makes sense only if you're getting people to update records. In this situation, there might be some information you want them to see in the form but not change.
Optionally, rearrange the order of your fields .

To move a field, select it in the list of included fields, and then use the up or down arrow buttons . When Access creates the email form, it puts the fields in the same order.
Click Next .

The final step appears (Figure 19-16).

Figure 19-16. Figure 19-16: In the next step, you pick the location where Access stores the replies, and you choose whether they'll be processed automatically.
Choose the folder where Access stores the reply messages once it's processed them .

Ordinarily, Access stores replies in an Outlook folder named Access Data Collection Replies. However, you can use any folder you want. To change the folder, click the Access Data Collection Replies link. Outlook launches, and shows you a Select Folder dialog box where you can pick any existing folder (or click New to create a new one.) Once you've picked the folder you want, click OK.

Tip: If you plan to perform more than one import operation for different tables, it makes sense to use different folders.
If you want to use automatic processing, then turn on the "Automatically process replies" checkbox. If you want to use manual processing, then skip to step 12 .

If you use automatic processing, then Outlook communicates with Access whenever it receives a reply. Access then adds or updates the corresponding record right away. This system works as long as your database file remains in the same location, has the same name, and isn't password-protected.

Manual processing's more work, but it's actually a safer choice. That way, you can review every reply before you add the record. You also know exactly how many replies you've received, and you can check for errors before the data gets into your table. For these reasons, manual processing's the best bet.
If you're using automatic processing, then click the "Set properties to control the automatic processing of replies" link to show the Collecting Data Using E-mail Options dialog box (Figure 19-17). Choose the settings you want, and then click OK .

Figure 19-17. If you choose to process replies automatically, then you can control a number of settings in this dialog box.

You can control the following settings:
- Discard replies for those to whom you did not send the message lets you ignore messages if they're sent from people that you didn't email.
- Accept multiple replies from each recipient lets recipients respond as many times as they want. Each time Access receives a message, it adds a record to the table. This makes sense if, say, you're collecting a list of items your friends want to sell at a group garage sale. It doesn't make sense if you're compiling the personal information of a bunch of bachelors, because each person gets just one record.
- Allow multiple rows per reply works only if you're using InfoPath. With InfoPath, you can fill in the information for more than one record in the same form (if this setting's switched on).
- Only allow updates to existing data works only if you're performing an update (see step 4). If you are, then you can use this setting to prevent people from adding new records. Again, this setting's an InfoPath-only option.
- Number of replies to be processed lets you stop processing after you reach a certain number of replies. From that point on, Access ignores all replies (unless you choose to process them manually, as described in Section 19.5.2).
- Date and time to stop lets you halt processing on a certain date and time. Replies that come in late are ignored, although you can process them manually.
Click Next .

The next screen asks how you want to supply email addresses.
Choose an email option, and then click Next .

Choose "Enter the email addresses in Microsoft Office Outlook" if you want to type in the email addresses for your recipients (or pick them from your Outlook address book). Then skip to step 15.

Choose "Use the email addresses stored in a field in the database" if you want to pull the email addresses out of a table.

If you're performing a table update, then you don't see this step. You always need to get email addresses out of a table.
If you're supplying email addresses from a table, then you need to tell Access what table and field to use. Then click Next to continue .

You can pull email addresses out of a current table (if you're performing record updates) or another linked table (which works if you're performing record inserts or updates). If you're updating the information in the Bachelors table, you could use the Email field in that table. Or, if you're creating a list of projects, each of which is linked to a project manager record, you can pull email addresses out of the ProjectManagers table, and let people create related records in the Projects table.
Fine-tune the email message that you're sending, and then click Next .

You can modify the text in the subject line and introduction (Figure 19-18).
You've reached the final step. Click Create to fire up Outlook, and get ready to send your message .

When you click "Create", Access creates the form and loads it up in a new message that's just itching to be sent (Figure 19-19).

If you chose to pull email addresses out of a table, then you see those addresses appear in the To, Cc, or Bcc lines. Otherwise, the To line's empty and it's up to you to fill in the right addresses. (Add as many as you want, separated by semicolons.) If you're an Outlook whiz, feel free to throw a mailing list into the mix. You can also perform any last minute edits to your message.
Once you have the correct recipient email addresses, click Send to send the message on its way .

That's it. Your work's done (until someone gets the message and fires back a response).

Figure 19-18. This example shows the standard subject line and text that Access fills in. You can add something that's more meaningful to your recipients.

To fill out a reply, the recipient simply needs to click Reply, type the values in all the text boxes, and then click Send to send the completed form back to you.

19.5.2. Processing Replies Manually

If you opted for manual processing, then you need to check your Outlook inbox periodically to look for replies. When you find a reply, right-click it, and then choose Export Data to Microsoft Access. This option appears only if you right-click a message that Outlook recognizes as a completed Access form (see Figure 19-20).

If Access successfully imports the message, then the email moves to the Access Data Collection Replies folder (or whatever folder you set up in step 9). Outlook shows a confirmation message telling you that all's well.

If you find a message that Access can't process, then you get to decide how you want to handle it. Possible problems include values that break field validation rules (Section 4.3), values that duplicate a value that's already in the table when duplicates aren't allowed (Section 4.1.3), and values that break data type or field length restrictions.

Here are some possible strategies for dealing with messages that Access can't process:

Delete the message and forget about it . You could use this approach if you spot a message that's obviously wrong, or one that duplicates a record that's already in the table.

Figure 19-19. Here's part of the form for the Bachelors table. You'll notice that Access automatically identifies the required fields, and fills in some details about the acceptable data types for each form.

Ask for a correction . Send the form to the recipient, and ask them to try again.
Enter the correct information by hand . If you can figure out where the data went wrong, then you may be able to correct the problem yourself. In that case, use the datasheet in Access to add the record that it should've created.

19.5.3. Processing Replies Automatically

If you chose to use automatic processing, then you don't need to take any more steps. As replies arrive in your Inbox, Access adds the data to the table, without you even knowing that it's happening. It's a good idea to check your table frequently to make sure the data that's being added doesn't contain obvious errors. Also, you should review your Outlook inbox for messages that weren't successfully processedlike those that contain bad data.

You'll know a message couldn't be processed if you see it in your inbox, and there's a red square or a blank value next to it in the Categories column. In this situation, you can try all the solutions described in the previous section to fix the problem.

Figure 19-20. Choose this shortcut menu option, and the current message goes to Access, which places it in a new record in the Bachelors table.

Note: You can also try one other technique to remedy problematic emails. If the record wasn't processed because of a temporary problem (for example, the database was open in exclusive mode at the time, or the drive where the database is stored wasn't available), you can ask Access to try to process it again. To do so, right-click it, and then choose Export Data to Microsoft Access.

19.5.4. Managing Your Email Collection Settings

After you've sent your message, all the informationwhom it's been sent to, what it's asking for, and so onis stored in your database. Access needs this info so it knows how to process replies.

You can do a few things to make sure everything runs smoothly. For example, you can send the email to more people, switch automatic processing on or off, and remove the email information altogether. To perform any of these tasks , choose External Data Collect Data Manage Replies. This action shows the Manage Data Collection Messages shown in Figure 19-21. (The name of this commandManage Repliesis a bit misleading. Youre not actually doing anything with the replies here. Instead, you're tuning up your original email settings.)

Figure 19-21. Click Message Options to pop up the dialog box where you can switch automatic processing on or off, and adjust how it works (Figure 19-17). Click "Resend this E-mail Message" to send your form out to another batch of people. Finally, click "Delete this E-mail Message" when you're done receiving data, and you don't want to use this feature anymore.

Tip: Even if you're finished collecting data, there's no reason to delete your email settings. Instead, why not keep them around in case you decide to collect data again, sometime in the future?