cfmail group attribute throws error

Question

I have a query which returns multiple results with just data in one column as different for a particular report number. I want to group the results for one report number and send an email to one person for all the action items. Below is my query

<cfquery name="qryCorrectiveDueDate" datasource="#application.config.DSN#">
    SELECT  FR.report_id,
            FR.report_number,
            IR.investigation_id,
            CA.action_who,
            CA.action_who_email,
            CA.action_what,
            CA.action_when,
            CA.action_type,
            CA.action_complete_date
    FROM    xyz_gir.dbo.xyz_flash_report AS FR,
            xyz_gir.dbo.xyz_investigation_report AS IR,
            xyz_gir.dbo.xyz_corrective_actions AS CA
    WHERE   FR.report_id = IR.report_id
    AND IR.investigation_id = CA.investigation_id
    AND DATEDIFF(day,CA.action_when,GETDATE()) > 1
    AND CA.action_complete_date IS NULL
    ORDER BY IR.investigation_id
</cfquery>

The Result Set looks something like this:

report_id   report_number   investigation_id    action_who  action_who_email     action_what    action_when action_type action_complete_date
2   13-0002 2   Hans-Joerg Wolf [email protected] Action 1    00:00.0 Repair  NULL
4   13-0004 3   Robert Michael  [email protected]  fghfgh  00:00.0 Repair  NULL
5   13-0005 4   Masoud Aghel    [email protected]    sdfsdfsdf   00:00.0 Repair  NULL
5   13-0005 4   Masoud Aghel    [email protected]    hhjghj  00:00.0 Repair  NULL

What i want to do is send one email to the action_who_email value for multiple lines with the same investigation id. If i use cfoutput group attribute this is the output:

[email protected]
Report ID: 2
Report Number:  13-0002
investigation id:   2
Action Who :Hans-Joerg Wolf
Action What: Action 1
Action When: 2013-04-26 00:00:00.0
Action type:Repair
Action Complete Date: 


[email protected]
Report ID: 4
Report Number:  13-0004
 investigation id:  3
Action Who :Robert Michael
Action What: fghfgh
Action When: 2013-05-05 00:00:00.0
Action type:Repair
Action Complete Date: 


[email protected]
Report ID: 5
Report Number:  13-0005
investigation id:   4
Action Who :Masoud Aghel
Action What: sdfsdfsdf
Action When: 2013-04-29 00:00:00.0
Action type:Repair
Action Complete Date: 

Report ID: 5
Report Number:  13-0005
investigation id:   4
Action Who :Masoud Aghel
Action What: hhjghj
Action When: 2013-04-29 00:00:00.0
Action type:Repair
Action Complete Date:

And This is my cfoutput:

<cfoutput query="qryCorrectiveDueDate" group="investigation_id">

<b>#action_who_email#</b><br>
    <cfoutput>
        Report ID: #report_id#</br>
        Report Number:  #report_number#</br>
        investigation id:   #investigation_id#</br>
            Action Who :#action_who#</br>
            Action What: #action_what#</br>
            Action When: #action_when#</br>
            Action type:#action_type#</br>
            Action Complete Date: #action_complete_date#</br></br>
    </cfoutput></br>

</cfoutput>

But if i do the same thing with cfmail on the query and group it, that throws a javax.mail.MessagingException: Could not connect to SMTP host: 127.0.0.1, port: 25; error.

My CFMAIL code is as below

<cfmail query="qryCorrectiveDueDate" from="[email protected]" to="#action_who_email#" group="#investigation_id#" subject="xyz">

<p>#action_who#,</p> 
<p>Due Dates for Corrective Actions for the Incident #report_number# have lapsed </p> 
<p>You are receiving this notification, as the Manager/Supervisor to implement the Corrective Actions </p> 
<p>Incident No: #report_number# </p> 
<p>Corrective Action: #action_what#</p> 
<p>Due Date: #action_when#</p> 

</cfmail>

Please note that i am running Coldfusion locally with xampp, and although the mails dont get delivered, i can see them in the undelivered folder in coldfusion. The same file has other cfmail tags which do not throw the connection exception if this cfmail tag is commented out. I apologize if my question is very generic, but i either have to do this with coldfusion or i might have to write a function to treat my result set as a temp table. Its easier with coldfusion i believe, if i know how to make this work.

Can you please update your question and quantify what you mean by "does not work"? — Adam Cameron
This error would indicate that there isn't a mail server running on that host or port? — Joshua M
yes there is no mail server but i have other cfmail tags with similar to and from addresses and they simply go to the undelivered forlder in coldfusion. I dont get this particular error. Its just when i added this particular cfmail on the query i am encountering this error. — amsko
Is there any difference between this cfmail and your other cfmail tags? — Joshua M
The other cfmail tags are inside cfloop . This one is as below: <cfmail query="qryCorrectiveDueDate" from="[email protected]" to="#action_who_email#" group="#investigation_id#" subject="xyz"> #action_who#, Due Dates for Corrective Actions for the Incident #report_number# have lapsed You are receiving this notification, as the Manager/Supervisor to implement the Corrective Actions Incident No: #report_number# Corrective Action: #action_what# Due Date: #action_when# </cfmail> — amsko

Winston Anchant Winston Anchant · Accepted Answer · 2013-05-11T03:06:12

Now that you've removed the hash tags, your issue is likely a missing cfoutput tag in the email. Similar to the group attribute of the cfoutput tag, you need another cfoutput tag to loop through the grouped records.

Tested on CF8 locally.

<cfmail query="q" group="field">
    <cfoutput>
        #field# - #field2#
    </cfoutput>
</cfmail>

cfmail group attribute throws error

2 Answers