Solved

cfmail with cfloop based on a cfquery result set

Posted on 2004-09-20
7
520 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
wordpress limitations 4 147
CFFILE upload help 98 209
True Type Font Files? 4 122
what is the easiest way and cheapest way to build a simple database that can be accessed online 10 134
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…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
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…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Suggested Courses

752 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