Well, I was up pretty late yesterday sending out emails to the folks who attended my session and were interested in our software. Since the number of emails was more than I wanted to do by hand (and I was afraid of doing typographical errors with the hand-coded emails), I elected to do a mail merge.

Mail Merge Options
When looking at mail merge, there are a few options available to you, and they depend on the following:

• The tool you want to use to do the Mail Merge
• How you want to source the data

Picking the Tool

In this circumstance, I elected to use Microsoft Word (although PeopleTools 8.48 customers will want to look at XML Publisher). The reason I chose Word is that my volumes weren't extravagant (~100 emails), and word has the linkage to Outlook baked in, so I didn't have to worry about writing code to send them out.

For XML Publisher, unless you've purchased CRM's module that includes the email functionality with it (which in this circumstance, if GS was big enough to use PeopleSoft CRM, that module would have worked for these purposes... We'd love to be big enough to fall under Oracle's target market for using PeopleSoft Enterprise for internal purposes ...).

In the absence of PeopleSoft-delivered functionality, you will probably need to write a simple app engine program that generates the output, then uses the SendMail function in PeopleCode. The documentation for PeopleTool 8.48 has sample code for selecting data and appying it to a template in XML publisher. Put a loop around the rowset and do a SendMail at the end, and you're in business.

Sourcing the Data

When sourcing the data, there are a few choices to make when using Word at the merging tool (it's more straightforward with XML publisher, because the integration to the data's baked in). Here are some options available to you:

• Connecting natively to the database through your database driver and selecting the data directly from the database
• Connecting natively to the database through your database driver and extracting data to excel.
• Running a PS/Query to Excel.
• Running a tabular nVision report (this is good if you want to script the mailmerge in VBA as an instancehook).

I chose to connect natively to our CRM database through the driver and extract the data to excel. This allowed me to use excel functions to massage the data (one of the things we did was to use the company name in a hyperlink, but with the white spaces, commas, and periods removed). I, then used word's wizard to format the email, pick the excel file as the source, and generate the email.

Issue with Word and Hyperlinks

For those who received my emails yesterday, you may have noticed that I had to send two of them. I was inadvertently caught by a word limitation in mail merge.

So, the first question that may come to mind is "why would you need hyperlinks?" In this circumstance, I was sending the emails to allow people to download something from our website, and the URL was specific to that person (company). When a person types a URL into word, it is smart enough to identify it as a hyperlink and to enable it. However, when it is not keyed by a person, it does not do this. In addition, the dialog for setting the hyperlink manually in word does not accept the field references from the mail merge.

I was able to find a workaround to this issue, which involves putting in your own code in word. Here's the support page that describes it. One thing I found is that that workaround can be pretty fragile. If you make any change that invokes the auto-format processing in word, your settings will be overwritten (which means that I got pretty good at going through the steps).

Labels: Email, Microsoft, Word