ColdFusion makes it very easy to send emails to mulitple recipients from a database using very little code. Part 1 of this series went over some basics, and part 2 showed a very basic newsletter delivery system, similar to the system we use at Community MX to send our weekly newsletters. Part 3 will show two different scenarios to include line items within the email body -- generic line items and recipient-specific line items.

Generic Line Items

The Community MX newsletter uses what I call "generic line items" -- a series of items within the newsletter that are pulled from a database but remain constant for each and every email. Community MX publishes 2 new pieces of content every weekday -- 10 per week. These 10 items are prominently displayed in our weekly newsletter, and are the same for each recipient. Using the sendemail.cfm file from Part 2, we'll add a new section to pull some generic information from the database, loop over it, and put it into our email.

There are different ways to do this, but because we are using an external file for our message body, the technique will follow these steps:

  1. Query the database for the information
  2. Create a design for the items, for both the HTML and text versions of the newsletter
  3. Loop over the query, substituting the dynamic data in the design
  4. Wrap the whole thing in a <cfsavecontent> tag pair in order to save the results into a variable
  5. Put a pre-defined variable surrounded by <cfoutput> tags into the newsletter, to be replaced at runtime

For this sample, I'll create the simple database table RecentNews using the following data:

RecentNewsID RecentNewsTitle RecentNewsItem RecentNewsDate
1 Email Blast Part 3 Using a ColdFusion server to send an email blast article posted at Community MX 7/14/2006
2 Handling File Uploads New article on handling file uploads for PHP posted at Jack's web site 7/13/2006
3 CMX turns 100 Community MX turned 100 years old today 7/12/2006
4 Adobe Buys Microsoft In a stunning move, Adobe bought Microsoft from a group of high school kids. Microsoft was too busy laughing and has yet to issue a statement. 7/11/2006
5 Google search fails Slowpoke Rodriguez failed to find his car keys using a Google search 7/9/2006

The database structure is as follows:

CREATE TABLE RecentNews (
  RecentNewsID int IDENTITY (1, 1),
  RecentNewsTitle varchar (255) NULL ,
  RecentNewsItem varchar (1024) NULL ,
  RecentNewsDate datetime NULL
)

The code for this section is listed below. It is commented inline and can be included in the sendemail.cfm file right after the rsGetAllEmails query.

<!--- query the database for the latest 3 items
      in the RecentNews table --->
<cfquery name="rsNewsItems" datasource="test">
SELECT TOP 3 RecentNewsId, RecentNewsTitle, RecentNewsItem
FROM RecentNews
ORDER BY RecentNewsDate DESC
</cfquery>

<!--- create the design for the HTML version,
      wrap it in a loop, and save the content in a variable --->
<cfsavecontent variable="newsitemshtml">
<!--- content for html goes here --->
<cfoutput query="rsNewsItems">
<h2>#rsNewsItems.RecentNewsTitle#</h2>
<p>#rsNewsItems.RecentNewsItem#</p>
</cfoutput>
</cfsavecontent>

<!--- create the design for the text version,
      wrap it in a loop, and save the content in a variable --->
<cfsavecontent variable="newsitemstext">
<!--- content for text goes here --->
<cfoutput query="rsNewsItems">
#rsNewsItems.RecentNewsTitle##chr(13)##chr(10)#
#rsNewsItems.RecentNewsItem##chr(13)##chr(10)#
</cfoutput>
</cfsavecontent>

The <cfsavecontent> tag suppresses the output to the page and instead puts it into a variable (named "newsitemshtml" and "newsitemstext" in this example). This is very useful in this situation, because we can simply use the variable in the newsletter -- the newsletter will change each week, but the design for the items will not.

In the newsletter body, add the replacement variable surrounded by <cfoutput> tags:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<body>
<img src="http://www.communitymx.com/images/logo_blue.jpg" alt="Community MX">
<h1>July Newsletter</h1>
<p>Hello <cfoutput>#rsGetAllEmails.name#</cfoutput></p>
<p>This is the newsletter for July. Yada yada yada yada yada.</p>
<p>These are the most recent new items:</p>
<cfoutput>#newsitemshtml#</cfoutput>
</body>
</html>

We'll do the same for the text version:

July Newsletter

Hello <cfoutput>#rsGetAllEmails.name#</cfoutput>

This is the newsletter for July. Yada yada yada yada yada.

These are the most recent news items:
<cfoutput>#newsitemstext#</cfoutput>

Now, when we send the newsletter, the news items will show up in the body. Figure 1 shows the newsletter as sent:


Figure 1: The completed newsletter

Recipient-Specific Line Items

Generic line items are built once and used for everyone in the email blast. Now we'll talk about building another loop inside each email that is specific to each recipient. This is useful for e-commerce sites where you might want to include a list of orders for each user, or a list of products that a user might be interested in (ala Amazon), or locations within a region close to a user. The technique will use the following steps:

  1. Within the <cfloop> tag for sending the emails, query the database for the information
  2. Create a design for the items for both the HTML and text versions of the newsletter
  3. Loop over the query, substituting the dynamic data in the design
  4. Wrap the whole thing in a <cfsavecontent> in order to save the results into a variable
  5. Put a pre-defined variable surrounded by <cfoutput> tags into the newsletter to be replaced at runtime

The steps and code are almost exactly the same. However this time we are doing it within the loop sending the emails rather than outside the loop.

Note: Another way to create line items within an email is using the GROUP attribute of the <cfmail> tag, however that technique is a little too involved for this basic article.

For this example, we'll assume an OrderDetails table exists that shows orders and items for each customer:

OrderID CustomerID OrderDetailsItem OrderDetailsPrice OrderDetailsQuantity
1 1 Red Scarf 3.99 1
2 1 Red Shirt 5.99 3
3 1 Red Hat 7.99 1
4 1 Red Pen 1.99 1
5 2 Pink underwear 5.99 20
6 3 Paper ream 15.99 1
7 3 Glue sticks 0.99 20

The table structure is as follows:

CREATE TABLE OrderDetails (
  OrderID int IDENTITY (1, 1),
  CustomerID int NULL,
  OrderDetailsItem varchar (50) NULL,
  OrderDetailsPrice money NULL,
  OrderDetailsQuantity int NULL
)

The code for this section is listed below. It would be used in place of the code in the previous section -- in other words, if you need a recipient-specific loop, you would use this code, whereas if you need a generic loop, use the previous example. It is commented inline and can be included in the sendemail.cfm file right after the rsGetAllEmails <cfloop> tag.

<!--- query the database for the customer orders
in the RecentNews table --->
<cfquery name="rsOrderDetails" datasource="test">
SELECT * FROM OrderDetails
WHERE CustomerID = #rsGetAllEmails.CustomerID#</cfquery>

<!--- create the design for the HTML version,
      wrap it in a loop, and save the content in a variable --->
<cfsavecontent variable="orderdetailshtml">
<!--- content for html goes here --->
<table>
 <tr>
  <td>Item</td>
  <td>Price</td>
  <td>Quantity</td>
  <td>Total</td>
 </tr>
<cfset totalPrice = 0>
<cfoutput query="rsOrderDetails">
 <tr>
  <td>#rsOrderDetails.OrderDetailsItem#</td>
  <td>#DollarFormat(rsOrderDetails.OrderDetailsPrice)#</td>
  <td>#rsOrderDetails.OrderDetailsQuantity#</td>
  <td>#DollarFormat(rsOrderDetails.OrderDetailsQuantity * rsOrderDetails.OrderDetailsPrice)#</td>
 </tr>
<cfset totalPrice = totalPrice + (rsOrderDetails.OrderDetailsQuantity * rsOrderDetails.OrderDetailsPrice)>
</cfoutput>
 <tr>
  <td>&nbsp;</td>
  <td>&nbsp;</td>
  <td>Total:</td>
  <td><cfoutput>#DollarFormat(totalPrice)#</cfoutput></td>
 </tr>
</table>
</cfsavecontent>

<!--- create the design for the text version,
wrap it in a loop, and save the content in a variable --->
<cfsavecontent variable="orderdetailstext">
<!--- content for text goes here --->
Item Price Quantity Total
<cfset totalPrice = 0>
<cfoutput query="rsOrderDetails">
#rsOrderDetails.OrderDetailsItem# #DollarFormat(rsOrderDetails.OrderDetailsPrice)# #rsOrderDetails.OrderDetailsQuantity# #DollarFormat(rsOrderDetails.OrderDetailsQuantity * rsOrderDetails.OrderDetailsPrice)##chr(13)##chr(10)#
<cfset totalPrice = totalPrice + (rsOrderDetails.OrderDetailsQuantity * rsOrderDetails.OrderDetailsPrice)>
</cfoutput>
Total: <cfoutput>#DollarFormat(totalPrice)#</cfoutput>
</cfsavecontent>

The code in the email bodies will be the same, with some changes in wording for the current example:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<body>
<img src="http://www.communitymx.com/images/logo_blue.jpg" alt="Community MX">
<h1>Your Orders For July </h1>
<p>Hello <cfoutput>#rsGetAllEmails.name#</cfoutput></p>
<p>This is a list of orders placed in July. Yada yada yada yada yada.</p>
<cfoutput>#orderdetailshtml#</cfoutput>
</body>
</html>

We'll do the same for the text version:

Your Orders For July

Hello <cfoutput>#rsGetAllEmails.name#</cfoutput>

This is a list of orders placed in July. Yada yada yada yada yada.

<cfoutput>#orderdetailstext#</cfoutput>

Figure 2 shows the completed orders email with line items:


Figure 2: The completed orders email

Example Files

All code samples in this tutorial are included, and are as follows:

emailform.cfm -- email form to send the generic line item email

sendemail.cfm -- action page to send generic line item email

bodyhtml.cfm -- html body for generic line item email

bodytext.cfm -- text body for generic line item email

emailformOrders.cfm -- email form to send the recipient-specific line item email

sendOrdersEmail.cfm -- action page to send recipient-specific line item email

orderdetailsbodyhtml.cfm -- html body for recipient-specific line item email

orderdetailsbodytext.cfm -- text body for recipient-specific line item email

Conclusion

Sending emails with ColdFusion is a trivial matter, and adding complexity with generic line items or recipient-specific line items is a simple matter of formatting the line items and inserting them as a variable within the email body. All working files for this tutorial are included in the download package.