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
147 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 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