We have a large corporate database with the email addresses of sales employees. My task this week was to create a Form which filters the massive list and send an email to the filtered results.
I started by creating a simple form that allowed Filtering by Job Title, Business Segment, and Region. I gave the user dropdowns or links to reports that provided lists of these values. I then added an text input for Subject line, and a Text Area for the email message.
Step2 – Build the queries using the form filter.
Step3 – Send out the email or show user which email address will be sent. HOLD on a minute. We have thousands of emails, yet our email servers only allow a certain number of addresses to be sent on any one email message. Our average query would yield from 300 to 3000 addresses so I need to loop through the results to send out the email message multiple times to a different group of recipients.
Query: name=q_emails
select email_ids from email_list_table
where email_id is not null
<cfif job_title NEQ 'ALL'>and job_title = '#form.job_title#'</cfif>
etc...
Some variables
<cfset ids_per_email = 200>
<cfparam name="istartRow" default="1" type="numeric">
<cfparam name="iendRow" default="201" type="numeric">
<cfset totalRows = "#q_emails.recordcount#">
<cfset emaillist = "">
Nested Loop Output
<cfoutput>
<cfloop from="1" to="#totalRows#" index="i" step="#ids_per_email#">
<cfset iendrow = Min(#i#+#ids_per_email#-1, #totalRows#)>
-- build the list
<cfloop query="q_emails" startrow="#i#" endrow="#iendrow#">
<cfset emaillist="#emaillist##email_id#;">
</cfloop>
-- send the email
<cfmail to="#emaillist#"
from = "donotreply@domain.com"
subject = "#FORM.subject#"
server = "smtp.domain.com"
type = "HTML">
#FORM.message#
</cfmail>
<cfset emaillist = ""> -- reset the group for the next loop [i].
Message Sent.
</cfloop>
</cfoutput>
Note: you can use BCC in the CFMAIL tag and list the emaillist recipients there. You should provide a default “TO” value and if needed add a REPLY TO value.
cfset iendrow = Min(#i#+#ids_per_email#-1, #totalRows#)This bit of code sets the last group to the correct number of output emails. Imagine if your query results had 236 recipients and your max ids allowed is 200 recipients. You would generate 1 email with 200 recipients and 1 email with 36 recipients.
Enhancements:
if there are no records allow an abort
use cftry/cfcatch for errors
use an HTML template for the email
be code-reuse mindful and put CFMAil in its own CFM for use on other pages.
