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

Question

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    |  john@foo.com |     12
Bill    |  bill@bar.com |     42
Ann     |  ann@bat.com  |     100

CFScript:

var mailerService = new mail();

mailerService.setQuery(nameEmailNumberQuery);
mailerService.setTo('eterps@sendtomefortesting.com');
mailerService.setFrom( 'noReply@example.com' );
mailerService.setSubject('Hi');
mailerService.setFailto('fail@foo.com');
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.

in qeury driven cfmail (tag based) you would add the column name in setTo or set from... try setTo("Name") .... using the column name from the query. — Mark A Kruger, Aug 07 '12 at 13:19
Thanks Mark, but I want every email to send to me for testing purposes. In the real use-case, the contents of the email column in the query will populate the 'To' field. — eterps, Aug 07 '12 at 14:48

score 1 · Answer 1 · answered Aug 06 '12 at 23:55

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.

answered Aug 06 '12 at 23:55

Dale Fraser

4,623
7
39
76

Not sure about with cfscript but with the CFMail tag you can send customized emails with a query: http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec14b97-7ffb.html#WSc3ff6d0ea77859461172e0811cbec14b97-7ff9 You have to scroll down a little from there to the heading: "Customizing e-mail for multiple recipients" – invertedSpear Aug 07 '12 at 01:22
Thats not the same thing, the – Dale Fraser Aug 07 '12 at 02:43
Doesn't need to be documented, you are passing a fixed literal of a single name into an instance of the mail object and expecting it to magically change to the other values you have in your query. – Dale Fraser Aug 07 '12 at 20:11
The problem though is Adobe documents say there isn't a difference between the cfscript and cfml versions. http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSe9cbe5cf462523a0693d5dae123bcd28f6d-7ff9.html I read that document, specifically the parts that say `Used to sends an e-mail message, that optionally contains query output, using an SMTP server.` and `This function corresponds to the tag cfmail. For usage details, see the Usage section for cfmail.` and I would expect the script method to perform the same as the tag when you set the query attribute. – invertedSpear Aug 07 '12 at 21:09
But your still passing a literal, I just dont see how it can work, if internally the send method is looping over the query, it still has no idea what to substitute as you haven't sent it the variables you sent a literal text of the mail. – Dale Fraser Aug 08 '12 at 00:08
mailerService.send(body="Hello #name#. Your number is: #Number#!") and see if it works – Dale Fraser Aug 08 '12 at 00:11
Error. name and Number are both undefined. I tried `query` scope, and got the same result. Tried a bunch of other things, too, and still no luck. It appears you are correct in that the message body is a fixed literal, and there is no way to get coldfusion to loop over the query for you without using the `` tag. Ah well. – eterps Aug 10 '12 at 14:01

score 1 · Accepted Answer · answered Aug 07 '12 at 13:15

1

In this case, you'll need to do the looping yourself, and call the .send() method for each item in the loop. The MailService won't do the looping for you. So you need to do this:

var mailerService = new mail();

mailerService.setTo('eterps@sendtomefortesting.com');
mailerService.setFrom( 'noReply@example.com' );
mailerService.setSubject('Hi');
mailerService.setFailto('fail@foo.com');
mailerService.setType('html');
mailerService.setSpoolenable(true);

for (x = 1; x <= nameEmailNumberQuery.RecordCount; x=x+1) { 
    savecontent variable="mailBody"{ 
        WriteOutput(
            "Hello " & nameEmailNumberQuery.Name[x] & ". Your number is: " & nameEmailNumberQuery.Number[x] & "!"
        );          
    } 
    mailerService.send(body=mailBody & tmpVariable);
}

That will allow you to configure the base email properties once, and then send a separate email for each item in the row. If each email should go to a unique email address, just move the setTo() method into the loop and reference the proper column for the email address.

answered Aug 07 '12 at 13:15

Dan Short

9,598
2
28
53

1

Dan... hmmm.... what's the point of adding the queryname then? In tagged based cfmail when you add a query you end up with a loop - one mail for each query row. Why would you even need the "setQuery" function? – Mark A Kruger Aug 07 '12 at 13:17
You shouldn't use the `setQuery` there in that case (I've removed it from my answer). The issue that you're seeing is that your savecontent is running once, and when it runs, it's picking up the first item in the recordset. Then, the *already processed* text is being passed to the body of the send method. So you're sending already processed text. – Dan Short Aug 07 '12 at 13:19
Dang... I just reread his explanation and you are absolutely right. How would you mimic a query driven cfmail in this case (without implementing your own loop). – Mark A Kruger Aug 07 '12 at 13:22
From everything I can find (and from my initial tests), it just doesn't work that way. The text added to the body of the email is processed before the `send` method actually executes, so it will always have the first row of the query sent to the `send` method. Nothing I can find in the livedocs or the component class documentation implies that there is any way to set the body of the email so that it will uniquely process each row of the query. – Dan Short Aug 07 '12 at 13:30
After banging my head on this some more, I think you're right. No way to use the query attribute in CFScript as it's used in the CFMail tag. It appears that functional parity between CFML and CFScript is still something to look forward to. – eterps Aug 07 '12 at 14:51
I think that's a bug - or at least unexpected. Implamentation in cfscript should provide the same functionality as the tag (though frankly doing cfmail or cfquery in cfscript is convoluted and throws away the power and ease of the language). – Mark A Kruger Aug 07 '12 at 16:22
**The problem get even worse.** Here is my scenario. The email body is static - sending the same thing to all persons. With the query attrib set and To set to the variable of the email address column, **n emails are sent to the first person in the resultset, n being the number of records in the query**. The is definitely a bug. – PeterKA Jan 11 '17 at 15:56

score 0 · Answer 3 · answered Aug 07 '12 at 14:37

0

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="noReply@example.com" 
  failto="fail@foo.com" subject="Hi" type="html">

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

answered Aug 07 '12 at 14:37

Matt Busche

14,216
5
36
61

Thanks, but I'm trying to put this functionality in a script based CFC. Otherwise, yes, that is how to do it. – eterps Aug 07 '12 at 15:05

score -2 · Answer 4 · answered Aug 06 '12 at 22:29

-2

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 & "!"
);

answered Aug 06 '12 at 22:29

invertedSpear

10,864
5
39
77

Funny you should mention that. I tried it about 5 mins ago. Same result - 3 emails, all with the same data. – eterps Aug 06 '12 at 22:32
may just be buggy support for cfMail in cfscript. Maybe time to go back to CFML? – invertedSpear Aug 06 '12 at 22:45

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

4 Answers4

Linked