Solved

Loop through UserEmail addresses to send a mass email -- 10 records at a time -- using CFMAIL / maxrows attribute?

Posted on 2010-09-15
34
508 Views
Last Modified: 2012-08-14
My task: I need to send an email message to 914 people. These people's information is in a data table, NBPTS_Principals_Unapproved.

My strategy is, I will query the table NBPTS_Principals_Unapproved, loop through the 914 table rows (one row per person), and use CFMAIL to deliver an HTML formatted email message.

Each person will receive the email only one time. And, only ten email messages will go out in a single SMTP request.

Is it possible to do this using the MAXROWS attribute of the CFMAIL tag?

I did some reading on CFMAIL:
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f8e.html

If I understand this documentation correctly, then MAXROWS will do what I want.

My questions:

1. Does anyone have any advice about sending bulk email messages using CFMAIL? I do not want my SMTP server to get flagged as a spam source.

2. Am I using CFLOOP and CFMAIL correctly, below?

Thank you as always for any advice. =)

Eric
<!-----

Name:        email_principals_template.cfm

Author:      Eric B



Description: This template does these actions:

1) query table NBPTS_Principals_Unapproved to get Names and Email addresses of 914 Principals who were not approved for the NBPTS Field Test

2) create an HTML formatted email message to send to each principal

3) 



Created:     September 2010

ColdFusion Version 8

MS SQL Server 2005

----->



 <!--- Set the datasource --->

 <cfset ds="ebwebwork">



 

  <!--- select columns from table tbl_NBPTS_Principals, for use later in this application --->

  <cfquery datasource="#ds#" name="GetPrincipalstoApprove">

  SELECT UserID

     , Salutation

     , FirstName

	 , LastName

     , Address

     , City

     , State

     , ZIP

     , UserEmail

  FROM NBPTS_Principals_Unapproved

  </cfquery>

  



<cfloop query="GetPrincipalstoApprove">



<!--- generate email --->

<!--- email message goes inside CFLOOP --->



<cfmail

      SERVER="mail.nbptsprincipals.org"

      FROM="NBPTS Principals Field Test"

      TO="#UserEmail#"

      SUBJECT="NBPTS Principals Field Test"

      username = "xxxxxxxx"

	  password = "xxxxxxxx"

      type="html"

      SpoolEnable="Yes"

      query="GetPrincipalstoApprove"      

      maxrows="10">

      



<!--- begin cfoutput --->

<cfoutput>



 <!--- begin formatted email message --->

<html>

<head>

<title>NBPTS Principal Field Test</title>

</head>



<body lang="EN-US">

      

      <div style="width:800px;padding:20px;text-align:left;">

      

  <img src="http://www.nbptsprincipals.org/img/nbpts_email_letterhead.gif" width="710" height="158" alt="NBPTS Letterhead" />



  <p>September 17, 2010</p>





<p>#GetPrincipalstoApprove.Salutation# #GetPrincipalstoApprove.FirstName# #GetPrincipalstoApprove.LastName#<br />

#GetPrincipalstoApprove.Address#<br />

#GetPrincipalstoApprove.City#, #GetPrincipalstoApprove.State# #GetPrincipalstoApprove.ZIP#</p>



<p>Dear #GetPrincipalstoApprove.Salutation# #GetPrincipalstoApprove.LastName#,</p>





<p>Thank you for expressing your interest in participating in the National Board for Professional Teaching Standards Principal Certification field test. We had an overwhelming response from principals across the country, hearing from nearly 2,000 principals who indicated a desire to take on this professional challenge.</p>



<p>We only selected 600 principals based on field test criteria. According to these criteria, you were not selected to participate in the field test. Selection to participate in the field test is not a designation of merit, but rather designed to assure validity and reliability of the assessment for principal in all school types.</p> 



</div>





</body>



</html>



</cfoutput>

<!--- close cfoutput --->

      

</cfmail>

<!--- close cfmail --->



</cfloop>

<!--- close cfloop --->

Open in new window

0
Comment
Question by:Eric Bourland
  • 13
  • 7
  • 7
  • +2
34 Comments
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Hmmm. Should I put the CFMAIL inside the CFLOOP? Or the CFLOOP inside the CFMAIL? I am trying to think how that will work.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
Hi Eric,

How are you ?

Good to see you again :-)

cfmail is like cfoutput.

check out this link.

you can use query with cfmail tag directly

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_m-o_01.html

0
 
LVL 15

Expert Comment

by:myselfrandhawa
Comment Utility
The best approach is you create the isSendMail field in your table, for every 10 emails, make their value to 1, then requery that query again after a schedule of every 3 hours [I assume you know cfschedule], then it will query again and fetch another batch of records and send again. after agaion 3 hrs of Gap, it will do it again..

This way you can make the work easy..

or check this

http://tutorial256.easycfm.com/
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility

Sory Mr.Eric,

I didnt read your Q properly.

Very Sory
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Brichsoft and myselfrandhawa, thank you for these comments. myselfrandhawa, I will try the isSendMail column and the cfschedule idea -- that is a good idea.

Do you think that I need the CFMAIL tag inside the CFLOOP tag? Or should I put the CFLOOP inside the CFMAIL?

Thank you again. Hope you are both doing very well.

Eric
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 150 total points
Comment Utility
A scheduled task with a database flag is good idea.  Though you should limit the results on the database side ie SELECT TOP x.  Using "maxrows" you still retrieve everything .. CF ignores anything greater than the maxrows value.

<!--- get the next 10 records ...--->
SELECT  TOP 10 RecordID, Columns
FROM     TableName
WHERE   WasMailSent = 0  --- ie Email was NOT sent

<!--- if there's at least 1 mail to send --->
<cfif yourQueryName.recordCount>
      <!--- send it ... --->
      <cfmail ....>

      <!--- flag the 10 records as sent ... --->
      UPDATE TableName
      SET       WasMailSent = 1
      WHERE RecordID IN ( ... list of 10 RecordID's .... )
</cfif>



>> <cfmail
>>      query="GetPrincipalstoApprove"      
>> Do you think that I need the CFMAIL tag inside the CFLOOP tag? Or should I
>> put the CFLOOP inside the CFMAIL?

I'm not sure you want to use either one ... At least not if you're using cfmail's query attribute.  Typically people use the "query" attribute to do the looping for them

http://livedocs.adobe.com/coldfusion/8/Tags_m-o_01.html
Query:
Name of cfquery from which to draw data for messages. Use this attribute to send more than one message, or to send query results within a message.

(Disclaimer: I haven't had sufficient caffeine yet ...)





0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 250 total points
Comment Utility

Agreed that the database flag is the way to go, however, I would think about it a bit deeper.   Are you sending out more than one email over time?   Are there different emails that you may be sending to these email addresses?

If so, perhaps not just a Yes/No flag but either a date stamp that indicates the date of last email.  That way you can send out a new email every month without getting confused (or course you could clear the flag before the batch send starts as well).

But if you're sending different emails, perhaps a newsletter, monthly invoices, account alerts, then consider creating a child table where you have the email Type along with a sent flag...

userID   EmailType  EmailDate   Sent?
   1         Alert          1/1/2010     Yes
   1         Alert          2/1/2010     Yes
   1         News        2/10/2010    Yes
   1         News        3/10/2010    No    --- this one is still pending the send, after send update the flag to Yes



 
0
 
LVL 15

Assisted Solution

by:myselfrandhawa
myselfrandhawa earned 100 total points
Comment Utility
uage of cfschedule will handle this all. you can use cfschedule and as experts suggested database way is tego way!

have u checked the tutorial, it is also a nice way of sending the mails

0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Very good points by gdemaria.  Before you decide on a table structure, it's good to think ahead ... consider what types of emails you might be sending in the future.  Then design accordingly (ie for flexibility).
0
 
LVL 15

Expert Comment

by:myselfrandhawa
Comment Utility
make sure you also consider bounced back emails in that
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
myselfrandhawa, this tutorial:

http://tutorial256.easycfm.com/

is very helpful and I have read it. The notes at http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_m-o_01.html about CFMAIL, I have reviewed carefully.

gdemaria, I agree that it is better to set up this application with the future in mind. And this client will need to send this email several times to the group of people in this database table. The client is in a terrible hurry, and rushing me, so for now I will set up just this simple page and send the email. Later I will set up the child table as you suggest -- that is a good idea.

I am reviewing the notes on CFSCHEDULE here: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7c6e.html

@ _agx_ your idea looks very good and I am going to try that. You and myselfrandhawa say to use CFSCHEDULE ... does that mean, include the CFSCHEDULE tag inside my template email_principals_template.cfm ? Or does CFSCHEDULE go inside its own template? I am just not sure how this will exactly work.

Thank you as always for helping me figure this out. Hope you all are doing well.

Eric
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 250 total points
Comment Utility



Regarding the scheduler..

To make it easy, just go into CFIDE/administrator and setup a scheduled task to run every 15 minutes (or whatever you like)

The scheduled task will check the table to send emails if any user needs an email sent, then it will send it to them automatically.


If you're in a hurry, that's easier than coding a new scheduled task and coding one to stop itself.  Older versions of CF have bugs in CFSCHEDULE, I had hudreds of scheduled tasks getting created and never being deleted.  I assume the bugs been fixed, but don't know.    So, I suggest just doing it in admin to get it done quickly and easily.



0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 250 total points
Comment Utility


Here's some code based on the suggestions by experts above..

It uses a new column

 emailReminderDate  data type is dateTime

You can add that to your table..

<!-----

Name:        email_principals_template.cfm

Author:      Eric B



Description: This template does these actions:

1) query table NBPTS_Principals_Unapproved to get Names and Email addresses of 914 Principals who were not approved for the NBPTS Field Test

2) create an HTML formatted email message to send to each principal

3) 



Created:     September 2010

ColdFusion Version 8

MS SQL Server 2005

----->



 <!--- Set the datasource --->

 <cfset ds="ebwebwork">



 

  <!--- select columns from table tbl_NBPTS_Principals, for use later in this application --->

  <cfquery datasource="#ds#" name="GetPrincipalstoApprove">

  SELECT top 10 

       UserID

     , Salutation

     , FirstName

	 , LastName

     , Address

     , City

     , State

     , ZIP

     , UserEmail

  FROM NBPTS_Principals_Unapproved

  where emailReminderDate is null

 </cfquery>

  



<cfloop query="GetPrincipalstoApprove">



   <cfmail

      SERVER="mail.nbptsprincipals.org"

      FROM="NBPTS Principals Field Test"       <==== need a valid email address 

      TO="#GetPrincipalstoApprove.UserEmail#"

      SUBJECT="NBPTS Principals Field Test"

      username = "xxxxxxxx"

	  password = "xxxxxxxx"

      type="html"

      SpoolEnable="Yes">

      

         <!--- begin formatted email message --->

        <html>

        <head>

        <title>NBPTS Principal Field Test</title>

        </head>

        

        <body lang="EN-US">

              

          <div style="width:800px;padding:20px;text-align:left;">

              

          <img src="http://www.nbptsprincipals.org/img/nbpts_email_letterhead.gif" width="710" height="158" alt="NBPTS Letterhead" />

        

          <p>September 17, 2010</p>

        

        

        <p>#GetPrincipalstoApprove.Salutation# #GetPrincipalstoApprove.FirstName# #GetPrincipalstoApprove.LastName#<br />

        #GetPrincipalstoApprove.Address#<br />

        #GetPrincipalstoApprove.City#, #GetPrincipalstoApprove.State# #GetPrincipalstoApprove.ZIP#</p>

        

        <p>Dear #GetPrincipalstoApprove.Salutation# #GetPrincipalstoApprove.LastName#,</p>

        

        

        <p>Thank you for expressing your interest in participating in the National Board for Professional Teaching Standards Principal Certification field test. We had an overwhelming response from principals across the country, hearing from nearly 2,000 principals who indicated a desire to take on this professional challenge.</p>

        

        <p>We only selected 600 principals based on field test criteria. According to these criteria, you were not selected to participate in the field test. Selection to participate in the field test is not a designation of merit, but rather designed to assure validity and reliability of the assessment for principal in all school types.</p> 

        

        </div>

        

        

        </body>

        

        </html>

   </cfmail>

   <!--- close cfmail --->



   <cfquery datasource="#ds#" name="update_sent_flag">

     update NBPTS_Principals_Unapproved

       set emailReminderDate = getDate()

     where userID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#val(GetPrincipalstoApprove.userID)#">

   </cfquery>

  

</cfloop>

<!--- close cfloop --->

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
@gdemaria

>>So, I suggest just doing it in admin to get it done quickly and easily.

Good idea.
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 250 total points
Comment Utility
Eric,  note that the code I've show above will send an email to any user who does not have a date in the new  field "emailReminderDate"

This includes any new user added to the table NBPTS_Principals_Unapproved

Not sure if that is a benefit or a problem... if a new user is added a week from the mailing, he/she will also receive the email.   To avoid this, when adding a new record to  NBPTS_Principals_Unapproved  give it a default value.

Or you can define the column with a default value

 alter table NBPTS_Principals_Unapproved  add emailReminderDate default '1/1/1900'


If this is confusing, just let us know if it's desireable for a new user to get this email 15 minutes after the record is created or not..

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
gdemaria,

Thanks for all of this. I am working on this code. I need to run out to a meeting and will be back at my desk later this evening to resume work.

To answer your question, a new user record added to the table should not receive an email 15 minutes after the user record is created.

I am going to add the emailReminderDate column. I will be able to focus on this task later on.

Thank you again.

Angsty client = angsty Bourland.

Eric
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 150 total points
Comment Utility
gdemaria
>> So, I suggest just doing it in admin to get it done quickly and easily.

Just checking back after meetings.  Agreed.  When I mentioned cfschedule - I hadn't had my coffee yet ;-)  What I meant was using a scheduled task (in general) was a good idea (not cfschedule specifically).
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I think this is working. I will test more tomorrow.

=)

Good evening to all.

Eric
0
 
LVL 15

Assisted Solution

by:myselfrandhawa
myselfrandhawa earned 100 total points
Comment Utility
Yes! If you have access to the Cfadminstrator then you can create it from there and its quite easy!, if you are on Shared host, you have to create a template and go to your domain control panel and you can see a web schedular tag or link as i see in my host!

There you can add the Path of your emailtemplate and set its timings to 15 or 30 minutes depending on your choice, after evry 30 minutes or 15 minutes, if u check for schedular it will show u a message that schedular was a success or failure,

the code provided by gdmeria is correct and can handle your query properly

Cheers

Happy Coding

Take care

regards
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Good morning friends. This application is working like a charm and I am following up with the client to straighten a few more details. The use of the emailReminderDate column is a great idea. I see how that works -- it is elegant. It was easy to set up a schedule for this task in Cfadminstrator. I will come back later today to close this question but in the meantime I wanted to say, Thank you as always. I hope your Friday is going well.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
>> The use of the emailReminderDate column is a great idea...-- it is elegant

Yep.  gdemaria's has a talent for the deceptively simple approaches :)  Powerful and elegant is a great combination.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

Thanks agx, a nice complement, particularly coming from an expert whose comments are so well respected by myself and the ee community
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Aw, thanks .. and you're welcome. But after seeing much of the good advice you've provided over the years at EE, it's just a statement of fact :)
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
One note that I saw in the documentation. The CFMAIL FROM attribute can be a plain text string:

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_m-o_01.html

"E-mail message sender:
* A static string; for example, "support@mex.com"
* A variable; for example, "#GetUser.EMailAddress#".

This attribute does not have to be a valid Internet address; it can be any text string."

So I think the FROM attribute, below, is valid:

and the tests I did last night seem to be OK with a text string as a value for the FROM attribute. It was interesting to learn this b/c I always thought the FROM attribute needed a valid email address, as gdemaria noted above.
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 150 total points
Comment Utility
Yes, I only read about that feature this past year. But I'm not sure I would use it.  At least not until I understood the ramifications.  Mainly because I don't know how it would effect the "reply-to" feature in various email clients.  Instead I'd probably use a title + a real email address. I think the syntax is something like :

ie
<CFMAIL
FROM="NBPTS Principal Certification Field Test <youremail@address.com>"
...>
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
>>>="NBPTS Principal Certification Field Test <admin@nbptsprincipals.org>"

I think you are right.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

 Also, I believe your emails would be caught up in many spam filters without a valid From email address.

 By valid email, I mean an actual mailbox.  You can create an unmonitored mailbox such as  noReply@...

 You don't need to use anyone's "real" mailbox..
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
>>Also, I believe your emails would be caught up in many spam filters without a valid From email address.

That is true!

I used a valid email address. Per the client's request.

I am also using DKIM / Domain Keys signing, and valid SPF and RDNS values, to maximize delivery success.

I uploaded the template and set the schedule in CF Admin. Emails are scheduled to start sending at 3 p.m. US CST. I have reviewed the code until I am blue in the face. Yes, I am on tenterhooks.

=)
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
>> Yes, I am on tenterhooks.

You could always email yourself too. ie Add your address as a BCC.  Just so you can know what's happening.  I do that sometimes when I'm really nervous.  Just temporarily of course ...
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 250 total points
Comment Utility

Just a reminder Eric...  any new record added to that user table will have an EMPTY value in the column: emailReminderDate.


That makes the new user eligible to receive email if the scheduled task is still running.

I suggest that you set the database column to have a default value on insert of something like 1/1/1900

Also, for testing, see this block of code where I've added the variable testEmail.   When this variable is empty, the email will send to the user, when you put your own email in there, it will send to you for testing.  Then you can run the program with your email in there and see what happens...




 <cfset attributes.testEmail = "EricBorland@myDomain.com">  <=== remove email address to go live

 

  <cfquery datasource="#ds#" name="GetPrincipalstoApprove">

  SELECT top 10 

       UserID

     , Salutation

     , FirstName

	 , LastName

     , Address

     , City

     , State

     , ZIP

     , UserEmail

  FROM NBPTS_Principals_Unapproved

  where emailReminderDate is null

 </cfquery>

  



<cfloop query="GetPrincipalstoApprove">



   <cfset toEmail = GetPrincipalstoApprove.UserEmail>

   <cfif len(attributes.testEmail)>  <!-----swap out test email for real one when testing ----->

      <cfset toEmail = attributes.testEmail>

   </cfif>

   

   <cfmail

      SERVER="mail.nbptsprincipals.org"

      FROM="NBPTS Principals Field Test"       <==== need a valid email address 

      TO="#toEmail#"   <========================= Send to email variable ===========

      SUBJECT="NBPTS Principals Field Test"

      username = "xxxxxxxx"

	  password = "xxxxxxxx"

      type="html"

      SpoolEnable="Yes">







.....



 at the bottom of your email, you can add these lines...

 This will show you, on the email itself, who the email will go to once you are live, but this line will not show once you are live...







   <cfif len(attributes.testEmail)>

     <div style="color:red;">Email to: #GetPrincipalstoApprove.UserEmail#</div>

   </cfif>

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
@gdemaria,

<cfif len(attributes.testEmail)>  
     <div style="color:red;">Email to: #GetPrincipalstoApprove.UserEmail#</div>  
   </cfif>


That is a good idea. The application is already working ... the client really wanted me to push this live before COB Friday (today). But I have incorporated this code into my application. Next time the client wants me to send a bulk email message using this application, this testEmail method will make me less of a nervous wreck before I launch the application.


>>>Just a reminder Eric...  any new record added to that user table will have an EMPTY value in the column: emailReminderDate.
>>>That makes the new user eligible to receive email if the scheduled task is still running.

I understand that 100% and I will monitor that. After this application does its work, I will do:

alter table NBPTS_Principals_Unapproved  add emailReminderDate default '1/1/1900'

I am also working on that child table you mentioned. Might as well make this a flexible application that will be useful multiple times.

This has been incredibly instructive and helpul.

Eric B
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Hmmm. I thought I accepted multiple solutions for this question, from gdemaria, _agx_, and myselfrandhawa. I did not want to simply close this question. Moderator, will you please look into this matter? I want to express gratitude to gdemaria, _agx_, myselfrandhawa, and Brichsoft. The application is working perfectly thanks to them.

EricBourland
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility

Thanks eric for objecting :-)

I really dont know,many authors closing the QUESTION with accepting the solutions.They just giving the points.
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I have tried twice to accept solutions, above, and award points for this question. The question is still open and I do not have confirmation that the experts have received their points. I have followed up with Experts Exchange and hope to resolve this matter soon. Thanks for your patience.

Eric B
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now