Sending email to mulitple adddress if checkbox is ticked

Hi
at the minute I have Customer Email address in a Customer Table in MYSQL. If the Customer has an email address and has an invoice in the Invoice table the checkbox will be defaulted to checked.
Im now trying to send all checked invoices to Customers which have email addresses but im struggling, I have the option of emailing them seperate which I have working but, im getting mixed up with sending multiple emails

any tips would be great

Heres my code


<cfquery name="qgetemailaddress" datasource="#request.dsn#">
					select *
					from customer C,invoice I
					where C.Customer_AccNum = I.CUSTOMER_AccNum
					AND I.COMPANY_Number = C.COMPANY_Number
					AND I.CUstomer_DelvNum = C.CUstomer_DelvNum
					AND C.Customer_Email <> ''
					AND I.CUstomer_DelvNum = C.CUstomer_DelvNum
					AND I.INVOICE_Key = #INVOICE_key#
					</cfquery>		


<!--- <cfdump var="#qgetemailaddress#"> --->

<cfif isdefined("url.invoice")>
		<cfquery name="qgetcompanyno" datasource="#request.dsn#">
		select COMPANY_Number, INVOICE_Number, INVOICE_checktosendemail
		from INVOICE
		where INVOICE_Key = #url.invoice#
		</cfquery>
		<cfset form.INVOICE_Number = qgetcompanyno.INVOICE_Number>
		<cfset form.COMPANY_Number = qgetcompanyno.COMPANY_Number>
</cfif>
<cfparam name="url.action" default="">
<cfquery name="qgetInvoicedetail" datasource="#request.dsn#">
select INVOICE_Key, COMPANY_Number, INVOICE_Number, INVOICE_EMAIL, INVOICE_checktosendemail
from INVOICE
where INVOICE_Key = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.INVOICE#">
</cfquery>

<cfquery name="qgetname" datasource="#request.dsn#">
select *
from COMPANY C, INVOICE I 
where INVOICE_Key = #url.INVOICE#
and I.COMPANY_Number = C.COMPANY_Number
</cfquery>

<cfquery name="getemail" datasource="#request.dsn#">
select *
from USERS

</cfquery>

<cfset page = "users">
<cfset request.currentmenu = 4>
<cfinclude template="inc_header.cfm">
	<tr>
		<td id="midcontenttop">	
		<h1>Email Invoice</h1>
		</td>
	</tr>
	
	<tr>
		<td id="midcontent">
		<table cellpadding="0" cellspacing="0">
			<tr>
				<td id="menu" valign="top">
				<cfinclude template="inc_leftmenu.cfm">
				</td>
				<td valign="top">
				<cfif listfind(valuelist(qgetglobalmenuoptions.MENUOPTIONS_KEY),request.currentmenu) eq 0>
				ACCESS DENIED
				<cfelse>
				<table cellpadding="3" width="750">
					<tr class="row4">
						<th align="left">Invoice No.</th>
						<th align="left">Company Name</th>
						<th align="left">Customer Acc Number.</th>
					</tr>
				<cfloop query="qgetINVOICEdetail">
					<tr class="#iif(currentrow mod 2, de("row1"), de("row2"))#">
						<td>#INVOICE_Number#</td>
						<td>#qgetname.COMPANY_Name#</td>
						<td>#CUSTOMER_Acc_Num#</td>
					</tr>
				</cfloop>
				</table><br>
				<table cellpadding="3">	
					<cfquery name="qfindlog" datasource="#request.dsn#">
					select INVOICEEMAILLOG_To,INVOICEEMAILLOG_Date
					from INVOICEEMAILLOG
					where INVOICE_key = #qgetInvoicedetail.INVOICE_Key#
					</cfquery>
					<cfif qfindlog.recordcount>

					
					<tr>
						<td colspan="2"><strong>Email History</strong></td>
					</tr>
					
					<tr class="row4">
						<th align="left">Date</th>
						<th align="left">To</th>
					</tr>
					</cfif>
					<cfloop query="qfindlog">
					<tr class="#iif(currentrow mod 2, de("row1"), de("row2"))#">
						<td>
						<cfoutput>#dateformat(INVOICEEMAILLOG_Date,"dd mmm yyyy")# #timeformat(INVOICEEMAILLOG_Date,"HH:mm")#</cfoutput>
						</td>
						<td>#INVOICEEMAILLOG_To#</td>
					</tr>
					</cfloop>
				</table>
				<br/>	
				
						
<cfquery name="qgetcustomeremail" datasource="#request.dsn#">
					Select *
					From CUSTOMER
</cfquery>

				<form name="sendInvoice" action="act_sendCheckedInvoices.cfm?Invoice=#url.INVOICE#" method="post">
				<input type="hidden" name="type"  value="1"/>
				
				
					<cfmail query="qgetcustomeremail">
					from="#ListLast(cgi.REMOTE_User,"\\")#@Test.com"
					to="#qgetemailaddress#"
					Subject="#qgetname.Company_Name# ,  Invoice: #qgetInvoicedetail.INVOICE_Number#"
					
					
					Please see attached
				
					</cfmail>

forgot to send the action page


<cfquery name="qgetinvoice" datasource="#request.dsn#">
	select INVOICE_PDFFile
	from INVOICE
	where INVOICE_Key = #url.invoice#
</cfquery>

<cfset timestamp = timeformat(now(),"HHmmss")>
<cfset pdfname = url.invoice&"_"&timestamp&".pdf">

<cfpdf action="getinfo"  name="pdfinfo" source="#request.mainfilePath#PDFs\\#qgetinvoice.INVOICE_PDFFile#">

<cfif pdfinfo.TotalPages gt 1>
	<cfset totalpages = "1-"&pdfinfo.TotalPages>
<cfelse>
	<cfset totalpages = 1>
</cfif>
<cfquery name="qgetemailaddress" datasource="#request.dsn#">
					select *
					from customer C,invoice I
					where C.Customer_AccNum = I.CUSTOMER_AccNum
					AND I.COMPANY_Number = C.COMPANY_Number
					AND I.CUstomer_DelvNum = C.CUstomer_DelvNum
					AND C.Customer_Email <> ''
					AND I.CUstomer_DelvNum = C.CUstomer_DelvNum
					AND I.INVOICE_Key = #INVOICE_key#
</cfquery>	

<cfquery name="qgetcreditnotetemplate" datasource="#request.dsn#">
	select *
	from InvoiceTemplate IT, INVOICE I, CUSTOMER CU
	where INVOICE_Key = #url.invoice#
	AND IT.InvoiceTemplateType_Key = I.InvoiceTemplateType_Key
	AND IT.Company_Number = I.Company_Number
	AND I.CUSTOMER_DelvNum = CU.CUSTOMER_DelvNum
</cfquery>

<cfpdf
    action = "addwatermark"
    source = "#request.mainfilePath#PDFs\\#qgetinvoice.INVOICE_PDFFile#"
    image = "#request.mainfilePath#templates\\#qgetcreditnotetemplate.InvoiceTemplate_FileName#"
    foreground = "No"
    overwrite = "yes"
    pages = "#totalpages#"
	opacity = "10"
	Position = "10, -50"
     showonprint = "YES"
    destination = "#request.maindrivePath#temppdf\\#pdfname#">


<cfif form.type eq 2>
	<cfset tempto = form.to&"@fax2">
<cfelse>
	<cfset tempto = form.to>
</cfif>
<cfmail to="#tempto#" from="#ListLast(cgi.REMOTE_User,"\\")#@TEST.com" subject="#form.subject#"><cfmailparam name="reply-to" value="#form.replyto#">#form.Message#
<cfmailparam file="#request.maindrivePath#temppdf\\#url.invoice&"_"&timestamp&".pdf"#" type="application/JPG">
</cfmail>
<cfquery name="qaddintolog" datasource="#request.dsn#">
insert into INVOICEEMAILLOG
(INVOICEEMAILLOG_To,INVOICEEMAILLOG_Replyto,INVOICEEMAILLOG_Text,INVOICE_Key,INVOICEEMAILLOG_Type)
values
('#form.to#','#form.replyto#','#form.Message#','#url.INVOICE#',#form.type#)
</cfquery>


<cflocation url="dsp_searchdocs.cfm?invoice=#url.INVOICE#">

Seems like you should start your base query by doing a WHERE client_email NOT ISNULL and INNER (or outer, i forget) JOIN the table with invoices so you’ll have multiple rows of the same client if they have more than one invoice. Now you can loop over that query with the cfmail tag and send the invoices out to the client.

I think you’re wanting to combine multiple invoices into one email, right? I would actually lean towards one invoice to an email. That way, when they open the email for one invoice the other emails remain unread and are still demanding attention.

I think you’re wanting to combine multiple invoices into one email, right?

No I don’t want to combine multiple Invoices, just sending one Invoice at a time

Then just do something like so…


<cfquery name="client_invoice_info">
     SQL STUFF IN HERE
</cfquery>

<cfloop query="client_invoice_info">
     <cfmail to="#client_invoice_info.email#"
             from="invoices@your_url.com"
             subject="#client_invoice_info.client_name# invoice for #MonthAsString(Month(Now()-1)) &" "& Year(Now())#"
             replyto="no-reply@your_url.com">
     <!--- attachments and mailparts go here --->

     </cfmail>
</cfloop>


Thanks downtroden
I tried the above but its still not sending for me, its not even giving me an error message
This is what i changed it to


<cfloop>
<cfquery name="qgetemailaddress" datasource="#request.dsn#">
					select C.CUSTOMER_Email
					from customer C,invoice I
					where C.Customer_AccNum = I.CUSTOMER_AccNum
					AND I.COMPANY_Number = C.COMPANY_Number
					AND I.CUstomer_DelvNum = C.CUstomer_DelvNum
					AND C.Customer_Email <> ''
					AND I.CUstomer_DelvNum = C.CUstomer_DelvNum
					AND I.INVOICE_Key = #INVOICE_key#
</cfquery>

 
<cfloop query="qgetemailaddress">
     <cfmail to="#C.CUSTOMER_Email#"
             from="#ListLast(cgi.REMOTE_User,"\\")#@TEST.com"
             subject="TEST"
             replyto="#ListLast(cgi.REMOTE_User,"\\")#@TEST@.com">
     <!--- PDF attachments --->
 <cfpdf
    action = "addwatermark"
    source = "#request.mainfilePath#PDFs\\#qgetinvoice.INVOICE_PDFFile#"
    image = "#request.mainfilePath#templates\\#qgetcreditnotetemplate.InvoiceTemplate_FileName#"
    foreground = "No"
    overwrite = "yes"
    pages = "#totalpages#"
	opacity = "10"
	Position = "10, -50"
     showonprint = "YES"
    destination = "#request.maindrivePath#temppdf\\#pdfname#">
     </cfmail>
</cfloop>

<cflocation url="dsp_searchdocs.cfm">
</cfloop>

Is that the actual code, because there seems to be some extra <cfloop>'s in there. Also, make sure you’re not accidentally “cflocating” to another page before the code can process.

<!— PDF attachments —>
<cfpdf
action = “addwatermark”
…>

That just creates a watermark. Where’s the actual code that generates the mail attachment?

That just creates a watermark. Where’s the actual code that generates the mail attachment?

Well the PDF is already created im just using it and putting a watermark onto it

Yes, but you have the code inside a cfmail tag. That implies you want to attach it to the email. But … the code you posted isn’t doing that. It just watermarks … never attaches anything.

Also - what about the CFLOOP and CFLOCATION?

Oh yeh thats correct
This is attaching the field to the email

<cfmailparam file="#request.maindrivePath#temppdf\\#Invoice_Key&"_"&timestamp&".pdf"#" type="application/JPG">

Also - what about the CFLOOP and CFLOCATION?

and as for this im reloading the same page again so this should’nt really matter

I now have it attaching the file but its attaching the same file to every email, which I defiantly don’t want, it picking the correct email address but not the correct file

I think you missed my point about the extra cfloop. Is that a typo, or what …? Can you post the real code. It’s really hard to make the right suggestions when we can only half of the code :wink:

Just noticed that myself… loading the page over and over again is TOTALLY unnecessary (and probably a bad practice). I believe that was the point cfStarlight was trying to make that might’ve been missed.

This is what I have now:
It’s sending the Invoices ok but its keeping the same watermark for each invoice and its also sending all invoices, while it should only be sending invoices based on the company number which I have selected in the previous form (dsp_searchdocs.cfm)



<!--- Get Invoice --->
<cfquery name="qgetinvoice" datasource="#request.dsn#">
	select INVOICE_PDFFile
	from INVOICE I, COMPANY C, CUSTOMER CU
	where I.Company_Number = C.COMPANY_Number
	AND I.CUSTOMER_AccNum = CU.CUSTOMER_AccNum
	AND I.CUSTOMER_DelvNum = CU.CUSTOMER_DelvNum
	AND CU.CUSTOMER_Email <> ''
</cfquery>

<!--- Get Customer Email address & Company Email Address --->
<cfquery name="Getemails" datasource="#request.dsn#">
  	SELECT * 
	FROM Customer CU, Company C, Invoice I
 	Where I.COMPANY_Number = C.Company_Number
  	AND I.CUSTOMER_AccNum = CU.CUSTOMER_AccNum
  	AND I.CUSTOMER_DelvNum = CU.CUSTOMER_DelvNum
  	AND C.Company_Email <> ''
</cfquery>

<cfquery name="qgetemailcount" datasource="#request.dsn#">
					select INVOICEEMAILLOG_Key
					from INVOICEEMAILLOG IE
					where INVOICE_Key = INVOICE_key
					AND INVOICEEMAILLOG_Type = 1
					</cfquery>	

<cfmail query="Getemails"
   from="#Company_Email#"
   to="#Customer_Email#"
   subject="#COMPANY_Name#     Invoice Number: #INVOICE_Number#"
   replyto="#Company_Email#">
Dear #Customer_DelvName#
Please see attachment below
<cfquery name="qgetinvoice" datasource="#request.dsn#">
	select INVOICE_PDFFile
	from INVOICE I, COMPANY C, CUSTOMER CU
	where I.Company_Number = C.COMPANY_Number
	AND I.CUSTOMER_AccNum = CU.CUSTOMER_AccNum
	AND I.INVOICE_Number = #INVOICE_Number#
	AND CU.Customer_Email <> ''
</cfquery>

<cfset timestamp = timeformat(now(),"HHmmss")>
<cfset pdfname = INVOICE_Key&"_"&timestamp&".pdf">
<cfpdf action="getinfo"  name="pdfinfo" source="#request.mainfilePath#PDFs\\#qgetinvoice.INVOICE_PDFFile#">

<cfif pdfinfo.TotalPages gt 1>
	<cfset totalpages = "1-"&pdfinfo.TotalPages>
<cfelse>
	<cfset totalpages = 1>
</cfif>
<cfquery name="qgetcreditnotetemplate" datasource="#request.dsn#">
	select *
	from InvoiceTemplate IT, INVOICE I
	where INVOICE_Key = INVOICE_Key
	AND IT.InvoiceTemplateType_Key = I.InvoiceTemplateType_Key
	AND IT.Company_Number = I.Company_Number
</cfquery>

 <cfpdf
    action = "addwatermark"
    source = "#request.mainfilePath#PDFs\\#qgetinvoice.INVOICE_PDFFile#"
    image = "#request.mainfilePath#templates\\#qgetcreditnotetemplate.InvoiceTemplate_FileName#"
    foreground = "No"
    overwrite = "yes"
    pages = "#totalpages#"
	opacity = "10"
	Position = "10, -50"
     showonprint = "YES"
    destination = "#request.maindrivePath#temppdf\\#pdfname#">
	<cfmailparam file="#request.maindrivePath#temppdf\\#Invoice_Key&"_"&timestamp&".pdf"#" type="application/JPG">
Regards,
#Company_Name#
#Company_Address1#, #Company_Address2#
#Company_Address3#, #Company_Address4#
#Company_Phone#, #Company_Fax#
</cfmail>

<cflocation url="dsp_searchdocs.cfm">


Seems like you should be looping over some checkboxes from your form to get the companies that you’ve picked. I would base your checkboxes on the company id.