Solved

cfmail with cfloop based on a cfquery result set

Posted on 2004-09-20
7
513 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Service "nameserver" appears to be down 4 543
Reverse Proxy Server 6 78
How can I install php on a ngxin server on droplet 5 122
Configure IIS to process JSON 10 75
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now