Solved

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

Posted on 2002-03-20
6
143 Views
Last Modified: 2013-12-24
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
Comment
Question by:g118481
6 Comments
 
LVL 19

Accepted Solution

by:
cheekycj earned 50 total points
ID: 6883063
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
 
LVL 3

Expert Comment

by:TSellers
ID: 6883353
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
 
LVL 19

Expert Comment

by:cheekycj
ID: 6883570
Thanx for those very kind words :-)

CJ
0
New! My Passport Wireless Pro Wi-Fi Mobile Storage

Portable wireless storage to offload, edit, and stream anywhere.

High-capacity, wireless mobile storage designed to accompany professional photographers and videographers in the field to easily offload, edit and stream captured photos and high-definition videos.

 
LVL 6

Expert Comment

by:dash420
ID: 6884785
better u can go for a sehduler of cold fusion.
0
 

Expert Comment

by:webdiva69
ID: 6886969
uh create a scheduled job...
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6888190
Guys, I stated that a job needs to be scheduled.. but the what job needed to be outlined too.
0

Featured Post

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now