Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

cfmail with cfloop based on a cfquery result set

Posted on 2004-09-20
7
Medium Priority
?
524 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 1000 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 1000 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

718 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