Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

How can I send an automatic e-mail when a date field in a record is older than now?

I have records stored by a CF app, and in this app I have a "due date" field.

I want to send an e-mail to the person listed in the record as the owner, when the "due date" is older than now.

Any suggestions on how I can achieve this?
0
g118481
Asked:
g118481
1 Solution
 
cheekycjCommented:
add an email_sent flag that defaults to 0.

create a cfm page that has a query that gets the records where duedate < sysdate and email_sent = 0

now this page will loop through this query sending each email to the person and also running another query that updates the email_sent flag to 1 upon each successful mail sent.

have this page run as a scheduled job in CF Admin or use the CFSchedule job to schedule it.

CJ
0
 
TSellersCommented:
The method described above for the database query is great, it has inspired me to give it a try. As I have some code to actually send a decent looking email out I thought I would pass it along in case it can save you some time:

********************************************

<cfmail
            type = "html"
       to = #Form.email#
       from = "you"
       subject ="the due date is older
than now."><body bgcolor="C0C0C0" text="000000" link="0000FF"></body><img src="http://www.yourdomain.com/images/backgrounds/yourlogo_logo2.gif" width="260" height="52"><br>
#Form.fname#, the due date is older... <br>
<table width="75%" border="1" cellspacing="1" cellpadding="3">
<TR bgcolor="ffffcc">
      
     <TD><strong>SKU</strong></TD>
    <TD><strong>ProductName</strong></TD>
    <TD><strong>Brand</strong></TD>
    <TD><strong>Unit Price</strong></TD>
    <TD><strong>Qty</strong></TD>
      <TD><strong>Total</strong></TD><TD></TD>
</tr>
<br>
 <cfloop index="x" from="1" to="#SearchResult.recordCount#">
      <cfif SearchResult.ResultField5[x] is not 0>
            <tr><td>#SearchResult.ResultField1[x]#</td>
            <td>#SearchResult.ResultField2[x]#</td>
            <td>#SearchResult.ResultField3[x]#</td>
            <td>#DollarFormat(SearchResult.ResultField4[x])#</td>
            <td>#SearchResult.ResultField5[x]#</td>
            <td>#DollarFormat(SearchResult.ResultField5[x]*SearchResult.ResultField4[x])#</td><TD></TD>
            </TR><br>
      </cfif>
</cfloop>
</TABLE>
<p></p>
<table width="45%" border="1" cellspacing="1" cellpadding="3">
<TR bgcolor="ffffcc">
    <TD valign="top"> First name: </TD>
    <TD>
            #Form.Fname#
      </TD>
      </TR>
      <!--- Field: orders.Lname --->
    <TR bgcolor="ffffcc">
    <TD valign="top"> Last name: </TD>
    <TD>
      
            #Form.Lname#
      
      </TD>
      </TR>


   


    <!--- Field: orders.email --->
    <TR><TR bgcolor="ccffcc">
    <TD valign="top"> email address: </TD>
    <TD>
      
            #Form.email#
      
      </TD>
      </TR>


    <!--- Field: orders.Address1 --->
    <TR bgcolor="ccffcc">
    <TD valign="top"> Ship-to Address: </TD>
    <TD>
      
            #Form.Address1#
      
      </TD>
      </TR>


    <!--- Field: orders.Address2 --->
    <TR bgcolor="ccffcc">
    <TD valign="top"> Billing Address: </TD>
    <TD>
      
            #Form.Address2#
      
      </TD>
      </TR>


    <!--- Field: orders.Phone --->
    <TR><TR bgcolor="ccff66">
    <TD valign="top"> Phone: </TD>
    <TD>
      
            #Form.Phone#
      </TD>
      </TR>


    <!--- Field: orders.Fax --->
    <TR bgcolor="ccff66">
    <TD valign="top"> Fax: </TD>
    <TD>
      
            #Form.Fax#
      
      </TD>
      </TR>


    <!--- Field: orders.Town --->
    <TR><TR bgcolor="ccff00">
    <TD valign="top"> Town: </TD>
    <TD>
      
            #Form.Town#
      
      </TD>
      </TR>


    <!--- Field: orders.Prov --->
    <TR bgcolor="ccff00">
    <TD valign="top"> Prov/State: </TD>
    <TD>
      
            #Form.Prov#
      
      </TD>
      </TR>


    <!--- Field: orders.Pcode --->
    <TR bgcolor="ccff00">
    <TD valign="top"> Postal code/Zip: </TD>
    <TD>
      
            #Form.Pcode#
      
      </TD>
      </TR>


    <!--- Field: orders.Comments --->
    <TR><TR bgcolor="99ffcc">
    <TD valign="top"> Comments: </TD>
    <TD>
      
            #Form.Comments#
      
      </TD>
      </TR>
      </table>
      
<table width="75%" border="0" cellspacing="0" cellpadding="3">

  <tr>
    <td class="tblwthbwite">
          <p class="header">&nbsp;</p>
      <font size="2" face="Arial, Helvetica, sans-serif" >The attached
      form is in Adobe Acrobat format,
      if you do not have the Acrobat reader, Adobe Acrobat Reader
      is free, and freely distributable, software that lets you view and print
      Adobe Portable Document Format (PDF) files. In case you don't have it already,
      here is the link address to download it: <br>
    http://www.adobe.com/products/acrobat/readstep.html</font>
      <p><font size="2" face="Arial, Helvetica, sans-serif">Please
        look at the invoice and confirm it is correct and acceptable. If so,
        then please complete the Credit Card authorization form and fax it or
        send it back via email. </font></p>
      <p>Please
        feel free to contact us at any time if you require further assistance.</font><br>
      </p>
        Warmest Regards,<br>
        
                       Name <br>
              (403) 522-5252<br>
             HTTP://www.yourdomain.com/
      <p>&nbsp;</p>
      </td>
  </tr>
</table>
</body>
</html>


      
</cfmail>  
0
 
cheekycjCommented:
Thanx for those very kind words :-)

CJ
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
dash420Commented:
better u can go for a sehduler of cold fusion.
0
 
webdiva69Commented:
uh create a scheduled job...
0
 
cheekycjCommented:
Guys, I stated that a job needs to be scheduled.. but the what job needed to be outlined too.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now