Running mailshots from spreadsheet lists

Introduction

Inforama 1.2 introduces the ability to create datasources based on spreadsheet files. Combine this with Inforama's mailshot capability and you have a very powerful, easy to use document delivery mechanism. If you want to try the examples outlined here for yourself, download the sample spreadsheet project from SourceForge at https://sourceforge.net/project/showfiles.php?group_id=229527. When you have downloaded the sample project extract it to C:\SpreadsheetSample1.2. If you want to store it elsewhere extract it to the desired location, open the datasourceset.cfg.xml file in a text editor and change the path to the users.xls spreadsheet file.

Getting started

Begin by opening the users.xsl file in the resources directory and you will see Sheet1 as shown below.

Sample spreadsheet to be processed

Each of the rows shown contains data which will be used to populate the generated PDF documents. The email field will be used as the email address to which the generated documents will be sent. Now run the Inforama Studio application and open the extracted sample project. Click the Project - Properties menu item and the dialog shown below will be displayed. Enter SMTP server through which emails will be sent along with a valid username and password.

Inforama Studio project properties dialog

Setting up the spreadsheet datasource and dataset

Setting up a datasource based on a spreadsheet is very straightforward. Click the Data - Connections/Datasources menu item and the dialog to configure new and existing datasources is displayed. Click the existing contactsheet datasource and then click the Modify button to display the details of the datasource including the path to the users.xls file. This is shown on the left of the screenshot below.

Configuring the spreadsheet datasource and dataset

The dataset shown in the dialog on the right contains an SQL-like query which extracts data from the spreadsheet. This syntax allows queries to be defined in much the same way that database queries are written. There are limitations on joins and the number of clauses after the WHERE keyword when working with spreadsheets in this way.

Creating a letter background

The sample project contains a letter background document which is used as a document letterhead. To open this document expand the AcroForm from Document node and double-click the Letterhead document. The document will be opened in the embedded OpenOffice editor as shown below. There are two fields contained within this document at the top right which will display the addressee and the date the document was generated.

Creating a PDF background from an OpenOffice document

To create the data mappings for this background click the PDF icon to export the document to PDF and display the Acrobat fields. Click the data fields and you will see the data mappings displayed in the Components frame.

Creating a PDF background from an OpenOffice document

Preview the document

To see how the document will appear when generated, click the Project - Manage documents menu item and the Letter manager dialog will appear as shown below.

Letter manager dialog

Select the newsletter document from the master documents tree and then click the right arrow to add it to the selected fragments list. Next select the Letterhead document as the background document. Now click the Preview button in order to generate the document and it will appear as shown below.

Letter preview

Create an email template

Now that the document templates have been defined, it's now possible to create an email template which will be sent to users with the generated documents attached. To view the email template file which comes with the sample project, expand the Email node and double-click the MailshotEmail document. Again the document will open in the OpenOffice editor as shown below.

Creating and email template

Again data placeholders can be inserted where required. Next open the Letter manager dialog, select this email document and enter the subject line which will be associated with the email when sent.

Letter manager dialog with email

Running the mailshot

Now all of the elements are in place to run the mailshot so to get started click the Project - Generate documents and packs menu item. The document generation wizard will appear as shown below. On the first page, select the Mailshot radio button, the user dataset, the MailshotNewsletter document and then click teh next button.

The document generation wizard dialog

On the second page, select the MailshotNewsletter document and click the Email parameters checkbox. In the To email address text box enter the text $F{user.Email} and click the next button.

The third page of the wizard shown on the left below allows parameters to be entered. In this case there are no parameters so go ahead and click the next button to run the mailshot. You will need to wait a few seconds while the documents are generated and emailed.