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
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.
<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>
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?
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.
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
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&"_"×tamp&".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&"_"×tamp&".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.
