Automating Survey Distribution
My solution uses Outlook Tasks to manage the survey schedule and keep track of when surveys were sent. In Outlook's Tasks folder, select New Task, and label the subject Send Surveys. Click Recurrence, and set the task to run daily. You also need to turn on the reminder and set an appropriate reminder time for your schedule.
Return to the Visual Basic Editor, and in the ThisOutlookSession module, enter the VBA code that Listing 2 shows. This code contains two subroutines: Application_Reminder and SendSurvey. The Application_Reminder subroutine starts when your reminder opens. If the reminder matches your task by the Subject line, the subroutine will prompt you to send the surveys. If you click Yes, the subroutine connects to a database to collect the email address and ticket numbers for the surveys, then sends the surveys. The subroutine marks the task as complete and creates a new task for the next day. If you click No, you'll see a reminder dialog box to either dismiss or snooze the item until a later time. If you snooze the item, the subroutine will prompt you again at the chosen time. If you dismiss the reminder, the subroutine will ignore the task and won't send any surveys that day. In addition, you'll have to reopen the task to reset the reminder or manually mark it as complete.
At callout A in Listing 2, notice that you need to modify the database-connection string and query for your environment. My example connects to a SQL Server database table called TicketTable, which Figure 5 shows. You extract the following key fields from the table and pass them to the SendSurvey subroutine: TicketNumber, Summary, and EmailID. This example uses the text data type for the fields; you might have to modify your query if you use different data types. The ticket number identifies the record to update in the database when collecting the survey responses. Also notice at callout B in Listing 2 that the code adds a set of characters ("-//-") that separate the ticket number from the description in the Subject line. If you change this set of characters, you must also change them in the next section.
Automating Survey Collection
On the same computer you're sending the surveys from, you can also automate the collection of the results. First, you need to make sure the email address you're sending from is the same as the survey-collector address in Listing 1 so that the returned surveys will be sent to the Inbox on this computer.
My solution collects the survey results by using an Outlook rule and a script. You begin by entering the code from Listing 3 into the VBA editor. When the rule calls this script, the code connects to the database and updates the appropriate record with the survey results. The code also references the user-defined fields you created on the response form. So if you used different field names than I did, be sure to adjust them in this code so that they match.
Notice also that the ticket number that identifies the database record to update is in the subject line. As I just noted, the characters "-//-" separate the ticket number from the description, so if you change the format of the subject line, make sure you use the same delimiting characters or use another method for identifying the ticket number from the subject line.
You can now create a new Outlook rule under Tools, Rules and Alerts. Start with a blank rule to check messages after they arrive. Under Select Conditions, choose uses the form name form, where form name is the name of your survey form, and click the link to open the selection box. Browse to the Organizational Forms Library, and select the Survey form. On Select Actions, choose Run a Script, where Script is the name of your survey-collection script. Click the link, and choose CompileSurveys to assign the script to the rule. You can also choose to either delete the item or move it to a specified folder to help keep the Inbox organized, and you can select Stop Processing more Rules to avoid conflicts with other rules. You can skip the last section for exclusions. Finish the rule by providing a descriptive name, and turn it on. Figure 6 shows a completed sample rule. If you've already started collecting surveys, you can run the rule now on items already in the Inbox. From the time the rule begins running, it will process the surveys as they come in, updating the appropriate database records with the user responses.
What Do You Think?
The survey-automation process is now complete, but here's a final, quick note about security. If you're using a username and password to connect to a database, weigh the risk of exposing them. The code to distribute and collect surveys resides on one machine under a single profile. Although this setup is reasonably protected by Windows security, avoid using a system administrator account and password, and where possible, use integrated security. If you use integrated security, you'll have to change the database-connection string in your code. If you're using a SQL Server database, replace the user ID and password section with Integrated Security=SSPI; if you're using another type of database, refer to your database documentation for the correct connection string to use. You're now ready to pick users' minds to improve your service and confirm that you're satisfying their needs.