'Why is CFMAIL ignoring all but the first row of a query when using CFScript?

I'm trying to use CFmail in a CFScript CFC. I want CFMail to iterate through a query, and change values based on the query contents. It does this just fine, as long as the query object only has 1 row. If it has multiple rows (e.g. 5 rows), it will send 5 emails, but each email will contain the values from the first query row. I've tried a few different things. See below:

Query Obj:

Name    |     Email     |   Number
----------------------------------
John    |  [email protected] |     12
Bill    |  [email protected] |     42
Ann     |  [email protected]  |     100

CFScript:

var mailerService = new mail();

mailerService.setQuery(nameEmailNumberQuery);
mailerService.setTo('[email protected]');
mailerService.setFrom( '[email protected]' );
mailerService.setSubject('Hi');
mailerService.setFailto('[email protected]');
mailerService.setType('html');
mailerService.setSpoolenable(true);

    savecontent variable="mailBody"{ 
    WriteOutput(
        "Hello " & Name & ". Your number is: " & Number & "!"
    );          
}   

mailerService.send(body=mailBody & tmpVariable);

Using the above code, I get three emails. Each email says, "Hello John. Your number is: 12!"

I've also tried:

WriteOutput(
    "Hello " 
     & mailerService.getQuery().Name 
     & ". Your number is: " 
     & mailerService.getQuery().Number 
     & "!"
);

and:

WriteOutput(
    "Hello " 
    & mailerService.getQuery().Name[mailerService.getQuery.CurrentRow] 
    & ". Your number is: " 
    & mailerService.getQuery().Number[mailerService.getQuery.CurrentRow] 
    & "!"
);

EDIT: A couple more things I've tried (One as suggested by @invertedSpear)

Using the Query literal in the email body:

WriteOutput(
    "Hello " 
    & nameEmailNumberQuery.Name 
    & ". Your number is: " 
    & nameEmailNumberQuery.Number
    & "!"
);

Trying to use an incremented counter:

var counter = 1;
...
WriteOutput(
    "Hello " 
    & mailerService.getQuery().Name[counter] 
    & ". Your number is: " 
    & mailerService.getQuery().Number[counter]
    & "!" & evaluate('counter = counter++')
);

With the same result each time - 3 emails, all with 'John' and '12'. Dumping mailerService.getQuery().CurrentRow results in '1' for each email. I am using Coldfusion 9.0.1 on Windows Server 2008 R3.

coldfusioncoldfusion-9cfmail


Solution 1:[1]

Im sure that when you send a mail to a query list, its just sending one mail to a big list of people.

Thus you can't change the name in the one email.

If you want to do what your trying to do, you send multiple mails.

loop over the query and do a cfmail for every record, this will generate a seperate mail.

Solution 2:[2]

If you're trying to send out one email to each record in the query, I would just write this in tags. Pretty simple if you do it that way

<cfmail query="nameEmailNumberQuery" 
  to="#nameEmailNumberQuery.Email#" from="[email protected]" 
  failto="[email protected]" subject="Hi" type="html">

  Hello #nameEmailNumberQuery.Name#. Your number is: #nameEmailNumberQuery.Number#!
</cfmail>

Solution 3:[3]

In the examples I see for the tag based version of this they reference the query by name. Have you tried:

WriteOutput(
 "Hello " & arguments.engagementQuery.Name & ". Your number is: " & arguments.engagementQuery.Number & "!"
);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dale Fraser
Solution 2 Matt Busche
Solution 3 invertedSpear

Related Questions