CFMAIL 1

[mikeadmin]

Having trouble executing a auto email to owner of voided lot. Lot's get voided but when running email function I get the following error.

SET NOCOUNT ON SELECT DISTINCT EMailAddress FROM Lot, Item, Person WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID AND ItemID IN (#ValueList(EMailAddresses.EMailAddress)#) UNION
Error Occurred While Processing Request
Error Diagnostic Information
Invalid CFQUERY syntax

The error is most likely caused by one of two things:

In your template you have mismatched <CFQUERY> and </CFQUERY> tags, or,
You have omitted the SQL= attribute of a simple <CFQUERY> tag, i.e., one that has no </CFQUERY> tag associated with it.


HERE IS THE CODE FOR THE PAGE:

<!--- get the unvoided items in the lot --->
<CFQUERY NAME=&quot;Items&quot; DATASOURCE=&quot;PDC&quot;>
SELECT ItemID FROM Item WHERE LotID = #LotID#
AND NOT EXISTS (SELECT ItemID FROM ItemVoid WHERE ItemID = Item.ItemID)
</CFQUERY>

<CFTRANSACTION>

<!--- void the items in the lot --->
<CFLOOP QUERY=&quot;Items&quot;>
<CFQUERY DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
INSERT INTO ItemVoid (ItemID, Reason, PersonID, Date) VALUES (#ItemID#, '#Reason#', #Cookie.CurrentPersonID#, GetDate())
</CFQUERY>
</CFLOOP>

<!--- void the lot --->
<CFQUERY DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
INSERT INTO Void (LotID, Reason, PersonID, Date) VALUES (#LotID#, '#Reason#', #Cookie.CurrentPersonID#, GetDate())
</CFQUERY>

</CFTRANSACTION>

<!--- determine interested people --->
<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
<!--- created the lot --->
SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID
AND ItemID IN (#ValueList(EMailAddresses.EMailAddress)#)
UNION
<!--- send the email --->
<CFMAIL SUBJECT=&quot;Voided Lot #LotID#&quot;
TO=&quot;#ValueList(EMailAddresses.EMailAddress)#&quot;
FROM=&quot;#PrimaryAdminEMailAddress#&quot;>
Lot #LotID# was voided on #DateFormat(LotID.Date, &quot;ddd, mmmm dd, yyyy&quot#

</CFMAIL>

 

[mikeadmin]

There is no email address field in the lot table. I inherieted this site alittle while ago. What I need to do is once a lot is voided, send a email to the person who created the lot. Telling them the lot was voided.

 

[tleish]

It seems you were pretty close on the first script. What about changing:

[COLOR=666666]<!--- determine interested people --->[/color]
<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID
AND ItemID IN (#ValueList(EMailAddresses.EMailAddress)#)
UNION
(SELECT EMailAddress FROM Person
WHERE EMailAddress LIKE '#PrimaryAdminEMailAddress#')
</CFQUERY>

to

[COLOR=666666]<!--- determine interested people --->[/color]
<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
SELECT DISTINCT Person.EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = #LotID#
</CFQUERY> - tleish

 

[mikeadmin]

I think that might have worked. I havent received a email yet from a test void I set up. It should be sent to my yahoo address. I checked the undeliverable folder and error log and it appears to be ok. Does this cfmail look ok?

<!--- send the email --->
<CFMAIL SUBJECT=&quot;Voided Lot #LotID#&quot;
TO=&quot;#QuotedValueList(EMailAddresses.EMailAddress)#&quot;
FROM=&quot;#ProgrammerEMailAddress#&quot;>
Lot #LotID# was voided
-Connecticut Property Distribution Center Administration
</CFMAIL>

 

[tleish]

Another way to do it is to use the &quot;Query&quot; attribute in CFMAIL. This will loop through each one of the records the way CFOUTPUT does and send an email to each email address. I added the GROUP attribute as well so that duplicate emails won't be sent.

[COLOR=666666]<!--- send the email --->[/color]
<CFMAIL QUERY=&quot;EMailAddresses&quot;
GROUP=&quot;EMailAddress&quot;
SUBJECT=&quot;Voided Lot #LotID#&quot;
TO=&quot;#EMailAddresses.EMailAddress#&quot;
FROM=&quot;#ProgrammerEMailAddress#&quot;>
Lot #LotID# was voided
-Connecticut Property Distribution Center Administration
</CFMAIL> - tleish

 

[mikeadmin]

Thanks! I wont be in the office until monday but I'll let you know how it works out.
Happy Thanksgiving

Mike

 

[mikeadmin]

Tleish,
I tried the query method but it wouldnt work for me. Also, it appears that this method works:

<!--- send the email --->
<CFMAIL SUBJECT=&quot;Voided Lot #LotID#&quot;
TO=&quot;#QuotedValueList(EMailAddresses.EMailAddress)#&quot;
FROM=&quot;#ProgrammerEMailAddress#&quot;>
Lot #LotID# was voided
-Connecticut Property Distribution Center Administration
</CFMAIL>

However, I still dont receive the email. There is no error message in the logs and the smtp is sending email, I checked. Any ideas? Thanks

 

[tleish]

What do you mean by &quot;it wouldnt work for me.&quot; Also, have you looked at your CF Email spool to see if the emails aren't being sent? (Assuming CF Server is installed on the C Drive you would look in C:\CFUSION\MAIL\UNDELIVR) - tleish

 

[mikeadmin]

OK, the CFMAIL QUERY you gave me executed fine but I still am not receiving email. I checked all folders in C:\CFUSION\MAIL including undeliv. No sign of it. I checked the logs too looking for a error message and there aren't any.
Mike

 

[mikeadmin]

Disregard, I finally received email.
Thank You Very Much!

Mike