cfmail querying database not working

[shearak]

The code below is not working and I am not getting any errors. Any suggestions.


<cfparam name="notifyDate" default="#DateFormat(Now(), "short")#">

<CFQUERY name="getNotifications" datasource="AEDWEB">
SELECT u.strEmail,
u.strFirstName,
u.strLastName,
cc.strCCNoteEmailBody
FROM umsUsers u,
ctsConferenceCalls cc
WHERE cc.dtsCCNotificationDate = '#notifyDate#'
AND u.intCCNotify = 1
AND u.intMemberstatus = 1
</CFQUERY>

<CFOUTPUT query="getNotifications">
<CFMAIL TO="#strEmail#" FROM="notification@ccicom.com" SUBJECT="Conference Call Reminder" SERVER="mail.ccicom.com" port="25">
#strCCNoteEmailBody#
</CFMAIL>
</CFOUTPUT>

[rmorgan]

Is it possible that your
WHERE cc.dtsCCNotificationDate = '#notifyDate#'
AND u.intCCNotify = 1
AND u.intMemberstatus = 1
statements do not exist? What if you take your 'AND' statements out?

Also, you do not need cfoutput statements with cfmail.

[shearak]

I just can a query with the WHERE, AND and I get data return. I also modified the code by taking the cfoutput statement out. I still get no mail. I also check in CF admin and the mail server is running. Any other sugguestions please.


<cfparam name="notifyDate" default="#DateFormat(Now(), "short")#">

<CFQUERY name="getNotifications" datasource="AEDWEB">
SELECT u.strEmail,
u.strFirstName,
u.strLastName,
cc.strCCNoteEmailBody
FROM umsUsers u,
ctsConferenceCalls cc
WHERE cc.dtsCCNotificationDate = '#notifyDate#'
AND u.intCCNotify = 1
AND u.intMemberstatus = 1
</CFQUERY>

<CFMAIL TO="#strEmail#" FROM="notification@ccicom.com" SUBJECT="Conference Call Reminder" SERVER="mail.ccicom.com" port="25">
#strCCNoteEmailBody#
</CFMAIL>

[rmorgan]

Have you checked your logs in cfadmin?

Try hardcoding the TO.

Some email servers are set that email can only be sent from a valid email account. Is notification@ccicom.com a real email account on that server.

[shearak]

OK , I tried everything still no luck. How would you rewrite this to make it work with any database (query)?

<cfparam name="notifyDate" default="#DateFormat(Now(), "short")#">

<CFQUERY name="getNotifications" datasource="AEDWEB">
SELECT u.strEmail,
u.strFirstName,
u.strLastName,
cc.strCCNoteEmailBody
FROM umsUsers u,
ctsConferenceCalls cc
WHERE cc.dtsCCNotificationDate = '#notifyDate#'
AND u.intCCNotify = 1
AND u.intMemberstatus = 1
</CFQUERY>


<CFMAIL query="getNotifications" TO="#strEmail#" FROM="notification@icom.com" SUBJECT="Conference Call Reminder" SERVER="mail.icom.com" port="25">
#strCCNoteEmailBody#
</CFMAIL>


Thanks again

[Dan_Bracuk]

try taking the table aliases (aliii?) out of the select clause of your query. It might not make a difference, but it's worth a shot.
Also consider taking away all the attibutes of your cfmail tag except query, to, from and subject.


By the way, how many records does your query return?

[shearak]

The query returns no records. How do I output dates in SQL? I even typed in a real date and no return. The cfparm is not working here. Please advise.

<cfparam name="notifyDate" default="#DateFormat(Now(), "MM/DD/YYYY")#">
<cfquery name="Recordset1" datasource="AEDWEB">
SELECT u.strEmail,
u.strFirstName,
u.strLastName,
cc.strCCNoteEmailBody
FROM umsUsers u,
ctsConferenceCalls cc
WHERE cc.dtsCCNotificationDate = #notifyDate#
AND u.intCCNotify = 1
AND u.intMemberstatus = 1
</cfquery>

[shearak · Accepted solution]

Thanks everyone for your feed. I finally figured it out. Below is the query that made the cfmail work.

<cfquery name="getNotifications" datasource="AEDWEB">
SELECT u.strEmail,
u.strFirstName,
u.strLastName,
cc.strCCNoteEmailBody
FROM umsUsers u,
ctsConferenceCalls cc
WHERE cc.dtsCCNotificationDate = #CreateODBCDate(Now())#
AND u.intCCNotify = 1
AND u.intMemberstatus = 1
</cfquery>