CFMAIL: Multiple Queries?

[eugeniana]

'm trying to email an invoice to the customer after they purchase, as well as a copy to our order desk in office...

Unfortunately the person whos coding I inherited had all the information that's needed in three different queries. What makes it even more frustrating is that it's intermitently worked on and off and now doesn't work at all.

Is there any way to pull the information from all three of the queries into one email form? Rewriting the original queries is not an option for me right now. Any help would be greatly appreciated, I'm feeling rather desperate.

Code is as follows currently:

<cfmail SPOOLENABLE="Yes" from="orderdesk@millerdavis.com" to="#Email#" type="HTML" subject="Your order from MillerDavis.com" cc="orderdesk@millerdavis.com" bcc="annam@millerdavis.com" server="mail.millerdavis.com">
                Your order from MillerDavis.com: <br /> &nbsp; <br />
                
<cfquery name="getShip" datasource="MillerDavis">
Select Shipping
from MDCatCust
Where CustomerID = #form.CustomerID#
</cfquery>

<cfset theShipping = getShip.shipping>

<table border="1" bordercolor="Black" cellspacing="0" cellpadding="4">
<cfoutput query="MakeReceipt1" maxrows="1">
        <tr>
                <td colspan="4" align="left">
                #FirstName# #LastName# #company#
                </td>
                <td align="right">
                #DateFormat(NOW(), "mm-dd-yyyy")#
                </td>
        </tr>
                <tr>
                <td align="left">
                        Email
                </td>
                <td colspan="4" align="right">
                        #email#
                </td>
        </tr>
        <tr>
                <td colspan="5">
                #Ship_Address1# 
                </td>
        </tr>
        <cfif #Ship_Address2# neq "">
        <tr>
                <td colspan="5">
                #Ship_Address2# 
                </td>
        </tr>
        </cfif>
                <tr>
                <td colspan="5">
                #Ship_City#, #Ship_State# #Ship_Zip#
                </td>
        </tr>
</cfoutput>



        <tr>
                <td>
                <b>Product No.</b>
                </td>
                <td>
                <b>Item Description</b>
                </td>
                <td>
                <b>Price</b>
                </td>
                <td>
                <b>Quantity</b>
                </td>
                <td>
                <b>Total</b>
                </td>
        </tr>
<cfoutput query="Makereceipt2">
        <tr>
                <td>
                #formno#
                </td>
                <td>
                #Description#
                </td>
                <td>
                #DollarFormat(ItemPrice)#
                </td>
                <td>
                #Quantity#
                </td>
                <td>
                #DollarFormat(itemPriceTotal)#
                </td>
        </tr>

        </cfoutput>
        <tr>
        <cfoutput>
                <td>&nbsp;   </td>
                <td>Shipping</td>
                <td>#Dollarformat(theShipping)#</td>
                <td>1</td>
                <td>#Dollarformat(theShipping)#</td>
        </cfoutput>
        </tr>

        <tr>
                <td colspan="4" align="left">
                <b>Grand Total</b>
                </td>
                <td>
                <cfoutput query="MakeReceipt1" maxrows="1">
                #DollarFormat(totalprice)#
                </cfoutput>
                </td>
        </tr>
</table>

 

[philhege]

<SNIP>
What makes it even more frustrating is that it's intermitently worked on and off and now doesn't work at all.

Is there any way to pull the information from all three of the queries into one email form?
</SNIP>

The first set of questions is: Is there an error? What is the error (EXACT text of the error that's returned? What's throwing the error? What changed before and after this template "worked" that may have caused this error? If there's no error, what entry from the logs can shed light on this problem?

In answer to your second question, I would pull the invoice construction code out of the CFMAIL tag. Write the information out to a variable. Then, the CFMAIL body would be nothing but

#theinvoicevariable#

and perhaps would lessen "confusion" in the parser.

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[eugeniana]

Nope returns no error... everything functions, but no email gets sent.

I just tried your suggestion, but the cfset tag seems to get confused with all the output information. I'm getting the feeling that wasn't what you meant... do you have an example of what you mean?

 

[philhege]

Yeah, it can be a bit tricky writing the code to do this.

As an example, using what I believe are your line items:
<cfset mailbody = "">
<cfoutput query="Makereceipt2">
<cfset mailbody=mailbody & "
<tr>
<td>"&
#formno#&"
</td>
<td>"&
#Description#&"
</td>
<td>"&
#DollarFormat(ItemPrice)#&"
</td>
<td>"&
#Quantity#&"
</td>
<td>"&
#DollarFormat(itemPriceTotal)#&"
</td>
</tr>"

</cfoutput>

HTH,


Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.