Solved

Schedule email

Posted on 2004-10-26
256 Views
Last Modified: 2013-12-24
I have read other posts about cfscheduler, but I can't get my arms around it in terms of what I am trying to do.

We have a newsletter that we want to send out on a dynamic schedule.  The flow of the tasks looks like this:

1.  Page 1 shows a list of available groups that people are part of in a db table.  (ie FirstName Bob, Group Plumbers, EAdd bob@plumbers4you.com)  The user clicks the checkboxes in front of each group that they want to send the newsletter to and clicks next.

2.  Page 2 shows the user a bunch of textboxes and textareas in which they type their new information for this newsletter.  These fields are stored in a db also.

3.  Page 3 allows the user to preview the newsletter and click on send to do a cfloop through the email addresses that belong to the groups selected and send out the email.

I can do this much.  My problem is, how do I schedule the actual send to not happen until a specified time/date?  Would the best way be to store the time and date to send the newsletter in the db and then have a cfscheduler run a cfm file hourly which contains a sql statement checking to see if there are any newsletters that are available since the last time the scheduler ran?

I am basically thinking out loud, so feel free to tell me that the whole thing is wrong and that there is a better way to do it if that is the case.

Thanks,
Jerod
0
Question by:prairieits
    10 Comments
     
    LVL 22

    Accepted Solution

    by:
    Hi prairieits,

     I can do this much.
        well then tough part is done.
      My problem is, how do I schedule the actual send
     to not happen until a specified time/date?  Would the best way be to
     store the time and date to send the newsletter in the db and then have
     a cfscheduler run a cfm file hourly which contains a sql statement
     checking to see if there are any newsletters that are available since
     the last time the scheduler ran?

        okey buddy... i think you gave answer yourself.
        I have extensive experience in this field as my previous job was sending newslaters and we used to do the same.

    The small snippet of our database was like this...

    1) name_of_newlater
    2) late_ran
    3) Frequency
    4) Owner
    5) Template
    6) Gallery
    7) Firsttime
    8) Lasttime
    9) Next_run

    I guess this will help you... the fields are pretty much explanatory.

    The query was ran every 5 min to check if there is any next run if there is next run we run cfschedule action="run"

    whenever we ran the query we updated the next_run field and also the last_ran field for future use.

    I think this is the way to go. Let me know if you want to know anything more...


    Regards,
    ---Pinal
    0
     
    LVL 35

    Assisted Solution

    by:mrichmon
    I think that if you want cold fusion to do the sending then yes your idea is probably the best way.

    However, if you want to have something else do the send like have SQL actually send the email using an exchange account, then there would be other possibilities in how to schedule it - and the scheduled task in SQL server would be less vulnerable than a scheduled task in Cold Fusion.

    The reason is the in cold fusion it runs a webpage at a scheduled time.  Therefore anyone who knew about the page could actually go to the page if there were no permission settings on it limiting who could access the page.

    This could be a good or bad thing depending on the situation.

    For example I used this in a case where we wanted a task to be scheduled, but also give a few administrative people the ability to manually visit the page off the normal schedule and run the task.

    I think in your case a CF scheduled page would work great just in the way you thought.
    0
     
    LVL 4

    Author Comment

    by:prairieits
    Thank you both for your feedback.

    If I was to do it in SQL, would I be doing it in a stored proc that was scheduled to run?  And how would you suggest I store the groups or users selected?  Should that be stored in a field in the db which is just an array of the groups that I then loop through when it is time to run it?

    I have more experience in cf than sql, thus the newbie question about it, but I agree that that would be a better way to go.  I just hadn't considered it.  Would books online have the best resource for how to send email from sql/exchange?

    Thanks,
    Jerod
    0
     
    LVL 35

    Expert Comment

    by:mrichmon
    It is acually much more complicated than sending from straight CF, but depending on the situation sometimes it is more useful - othertimes not.

    It would probably be best in a stored procedure and then scheduled as a job to run if you did it that way.

    I do not see taht you would be storing more or less data either way since you will need to store the list of groups or users selected in either approach...
    0
     
    LVL 22

    Expert Comment

    by:pinaldave
    hello,
    If I was to do it in SQL, would I be doing it in a stored proc that was scheduled to run?
    YES. And that would be faster but due to our requirement we did it in CF as we keep on changing things and it was easy to code (for us).

    And how would you suggest I store the groups or users selected?  Should that be stored in a field in the db which is just an array of the groups that I then loop through when it is time to run it?
    DATABASE FIELD. If you store them in Array that is really not good suggestion IMHO. It will slow down the process of sending email and we were facing strange timeout from our exchange server. (we were using list instead of array for small group which is same.) so I will not suggest it do it. You should have them in the database and run query over it.

    I have more experience in cf than sql, thus the newbie question about it, but I agree that that would be a better way to go.  I just hadn't considered it.  Would books online have the best resource for how to send email from sql/exchang.
    I am good at CF so I never much consider sending using SQL but for this answer is google.

    Regards,
    ---Pinal
    0
     
    LVL 4

    Author Comment

    by:prairieits
    I will not be able to respond on this for a day or so as something has come up.  I will respond again as soon as I can work on it.

    Thanks,
    Jerod
    0
     
    LVL 4

    Author Comment

    by:prairieits
    So it has been more than a day or two...sorry. :)

    I won't be working on this again until the first of December, so is it ok to just leave it open for now, or should I close it and ask another question if I have problems then?  

    Sorry for the delay.  I really appreciate your input.

    Thanks,
    Jerod
    0
     
    LVL 22

    Expert Comment

    by:pinaldave
    hello Prairieits,
    If you think you got the satisfactory answer for your first Q then you may close this Q and open new again for new problem.
    Glad to help you and good day!
    ---Pinal
    0
     
    LVL 4

    Author Comment

    by:prairieits
    I am working on this project (finally) again.  I should be able to try the suggestions and close it this week.  Sorry for the long delay.

    Thanks,
    Jerod
    0
     
    LVL 22

    Expert Comment

    by:pinaldave
    Thank you!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Shellfire Box VPN + Lifetime Subscription

    The Shellfire Box easily connects all of your devices, even those that don't offer the possibility to establish a safe vpn connection. Access blocked content and surf safely, no matter where in the world you are located.

    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…
    When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    856 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

    17 Experts available now in Live!

    Get 1:1 Help Now