Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

cfmail with cfloop based on a cfquery result set

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
pipcan
Asked:
pipcan
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
pinaldaveCommented:
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
 
Tacobell777Commented:
you need to use the group attribute on the cfmail tag, see http://docs.rinet.ru:8080/Cold/ch21/ch21.htm#Heading2
0
 
pipcanAuthor Commented:
- 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Tacobell777Commented:
no this last comment is not it, its a bad way of doing it... you should not loop over the cfmail tag
0
 
anandkpCommented:
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
 
pipcanAuthor Commented:
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
 
pinaldaveCommented:
glad to help you,
Regards,
---Pinal
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now