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
145 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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