Solved

cfmail with cfloop based on a cfquery result set

Posted on 2004-09-20
7
517 Views
Last Modified: 2013-12-24
i have the following query that returns individuals by manager that have not completed their timesheets for a given period.

<CFQUERY NAME="gettimsheetpastdue" DATASOURCE="#DSN#">

SELECT
      Employee.EID,
      Employee.EFirstName,
      Employee.ELastName,
      Employee.ManagerID As EManagerID,
      Employee.EArchived,
      Employee.EEMailAlias,
      Employee_1.EID AS MEID,
      Employee_1.ManagerID As MManagerID,
      Employee_1.EFirstName AS MFirstName,
      Employee_1.ELastName AS MLastName,
      Employee_1.EEMailAlias AS MEMailAlias
FROM
      Employee AS Employee_1 INNER JOIN Employee ON Employee_1.EID = Employee.ManagerID
WHERE
      (((Employee.EID) Not In (SELECT NoTimesheet.EID FROM NoTimesheet) And (Employee.EID) Not In
      (SELECT UserID FROM EventListCompletion  
      WHERE
      EventListCompletion.Year = '#Year#' AND
      EventListCompletion.Month = '#Month#' AND
      EventListCompletion.Period = '#Period#')) AND
      ((Employee.EArchived)=0))
ORDER BY
      Employee_1.ELastName,
      Employee.ELastName
</CFQUERY>

Returns result set:

Donald Duck (dduck)
            Orange Orange (oorange)
            Purple Purple (ppurple)                  
            Red Red (rred)
            Sam Sam (ssam)
            
Green Spinach (gspinach)
            Broccoli Green (bgreen)
            Tomato red  (bgreen)             
            Cauliflower white (cwhite)
etc.....

i need to send out an email to their managers (i.e. Donald Duck) listing the individuals (Orange Orange, Purple purple,, red red, sam sam) in the body of email that have yet to complete their timesheet, as well as a separate email to each  individual reminding them to complete their timesheet.

what is the best way to complete the cfmail with a cfloop or other method?

NOTE: using msaccess




0
Comment
Question by:pipcan
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 21

Accepted Solution

by:
pinaldave earned 250 total points
ID: 12105738
Hi pipcan,
================================
Solution 1

<cfloop list="#EmailAddress#" index="currentAddress">

<cfmail  to="#currentAddress#" bcc="jim@jimboggia.com" from="jim@jimboggia.com" subject="#EmailSubject#" type="html" server="128.121.222.100">
<font face="Verdana, Arial, Helvetica, sans-serif" size="3">
<p>#EmailText#</p></font>
</cfmail>

</cfloop>

================================
Solution 2
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20553984.html

=================================
Solution 3
This example uses CFMAILPARAM to attach two files and add a header to a
message..

<CFMAIL FROM="peter@domain.com" To="paul@domain.com" Subject="See
Important Attachments and Reply">
<CFMAILPARAM NAME="Reply-To" VALUE="mary@domain.com">
Please read the text file and view the new logo, and let us know what
you think.
<CFMAILPARAM FILE="c:\work\readme.txt">
<CFMAILPARAM FILE="c:\work\logo.gif">
</CFMAIL>

Regards,
---Pinal
0
 
LVL 17

Assisted Solution

by:Tacobell777
Tacobell777 earned 250 total points
ID: 12107560
you need to use the group attribute on the cfmail tag, see http://docs.rinet.ru:8080/Cold/ch21/ch21.htm#Heading2
0
 

Author Comment

by:pipcan
ID: 12107961
- i currently have cfmail working for individuals who are late, but i need it to work for a group (i.e. where email is sent to each Manager listing only their employees that are late in the body of the text).... essentially i need it to loop thru the managers and send out an email to each listing their employees that are late.  --- let me know if you need more info.


<cfoutput query="gettimesheetpastdue" group="EManagerID">
<cfmail to="#MEMailAlias#" from="spinach@company.com" subject="Timesheet Reminder" type="html" server=127.0.0.1>

<font face="Veranda" size=3
<p>Body of email needs to list individuals that are late for the given manager that is being sent the email</p>
</font>

</cfmail>
</cfoutput>

<cfdump var=#gettimesheetpastdue#>

-------

<cfloop list="gettimesheetpastdue" index="currentaddress">
<cfmail to="#currentaddress#" from="spinach@company.com" subject="Timesheet Reminder" type="html" server=127.0.0.1>

<font face="Veranda" size=3
<p>Body of email needs to list individuals that are late for the given manager</p>
</font>

</cfmail>
</cfloop>

<cfdump var=#gettimesheetpastdue#>
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 17

Expert Comment

by:Tacobell777
ID: 12108223
no this last comment is not it, its a bad way of doing it... you should not loop over the cfmail tag
0
 
LVL 17

Expert Comment

by:anandkp
ID: 12109450
try this :

<CFOUTPUT GROUP="EManagerID">

      <CFSET Defaulters = "">
      <CFOUTPUT><!--- Send users under the manager - individual email --->
            <CFMAIL To="#EFirstName#<#EEMailAlias#>" .....>
                  Kindly fill in ur time sheets !!!
            </CFMAIL>            
            <CFSET Defaulters = ListAppend(Defaulters,EFirstName)>
      </CFOUTPUT>
      
      <CFMAIL To="#MFirstName# <#MEMailAlias#>" ......><!--- Mail to the manager abt the defaulters --->            
            The following users are in the defaulter list - for not filling their time sheets !
            #Defaulters#            
      </CFMAIL>

</CFOUTPUT>

lemme know
0
 

Author Comment

by:pipcan
ID: 12122893
thanks for assistance with this questions - both links that were provided have aided in my solutions. - i need to re-define a subquery and then group on the result set. thanks again.
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 12124090
glad to help you,
Regards,
---Pinal
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
json_decode return null? 8 93
Configure a Local PHP Interpreter for Phpstorm 2 346
SSL sertificate 5 71
Create sub domain on windows dedicated server. 13 74
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 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