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
146 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

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…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …

752 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