Auto Email Reminder

[foundsheep]

I have a form that is classic asp that basically collects some info and sends it to an email account. What I really to do is have an email reminder option added. The form is a monthly report and the idea is to have an option for the reminder. The email will be sent monthly. There are other things, but that's all for now. Can this all be done in SQL? And how would that work if so?

[techniner]

I would cron/schedule a job to run a new program.

The new program should read through the member db. Then look at the last email sent date (you will create this field). Now do now() +30 days. If last email > now() - 30 days sent date send an email.
*You could also use BETWEEN and I recommend you do to be 100% sure.

Then flag the DB that an email has been sent and update the last email sent date.

Next time the program runs say midnight the next day ti will not send the ones form the previous day unless it meets 30 day criteria.

This can be done very easily in MSQL.

As a matter of fact if you have MSSQL Enterprise manager you can create this all in a single DTS job and run the email function out of the DTS job as well and then schedule the DTS job.

[foundsheep]

This sounds good. One issue is that there is no member db. At this point there is not a good reason to create a full fledged membership. The reminder is for the customer to come back to the site and report a meter reading off their printer. I'm thinking I'll just do a separate form to sign up for reminders, then I'll take care of updating the db myself. Anyway, is there some documentation on how to set this up in SQL? I'm using Express. I have very little experience in this area. Thanks.

[SheSaidImaPregy]

I would do this:

Create a job that sends a page hit to a certain page, let's say emailall.aspx (where it is unable to be seen from an http_referrer.

On the emailall.aspx page, it should search the database and retrieve all records that have the new field that is NOT null: fieldname: EmailDate.

Then grab all records that have the correct email date (today). Attach it to a datareader, and loop through an email function to email those who wish to be reminded. After that, have it UPDATE all the records with the month format:

UPDATE Reminders SET EmailDate=DATEADD(mm, 1, EmailDate) WHERE EmailDate=getdate()

This way you are not searching every record, reformatting it, then searching it again for proper dates.

The problem with "email > now() - 30", besides not being correct format for MS SQL, is that it first searches for all records that are greater than "now()", then reformats all those records to be now() - 30, then searches the string again for the corresponding dates. This is a very harsh task for the server to do, and can take awhile depending on the number of records you have.

By asking for the date to be equal to today, it does 1 search to grab todays date. Any that equal it, OKAY SEND! if not, oh well, better luck next time.

[foundsheep]

Is that a nix on using DTS then? I just spent the last hour trying to download it. Hope I can use it.

What is firing the emailall.aspx page? I'm guessing some code I'm not aware of.

[SheSaidImaPregy]

Don't forget to do a try catch for every single one, or do "on error resume next", however that is greatly depreciated and obsolete (but still works).

You would hate to throw an error half way through and half of the records don't viewed, and none will be updated.

It will be a hefty process, but that's why you do it during "downtimes" or on a separate server.

Something would be like this:

Dim conn As New SqlConnection( connstring )
Dim cmd As New SqlCommand("SELECT Email, FullName, ReminderID FROM Reminders WHERE Active=1 AND EmailDate<=getdate()", conn)
Dim reader As SqlDataReader
Dim strIDs As String = ""
Dim strIDsFailed As String = ""
Dim mymail As MailMessage

Try
    conn.Open()
    reader = cmd.ExecuteReader()

    If reader.HasRows Then
        While reader.Read()
            Try
                mymail = New MailMessage()
                mymail.To = reader("Email")
                ...
                mymail.Body = bodyyoucreate & "Dear Mr./Ms. " & reader("FullName")        
                SMTPMail.smtpserver = ""
                SMTPMail.Send(mymail)                       
                strIDs &= "," & reader("ReminderID")
            Catch
                strIDsFailed &= "," & reader("ReminderID")
            End Try
        End While
    End If

    reader.Close()

'You should try looping through this again for all those who failed ("ids")

    If Len(strIDs) > 0 Then
        If strIDs.substring(0,1) = "," Then strIDs.Remove(0,1)
        cmd = New SqlCommand("UPDATE Reminders SET EmailDate=DATEADD(mm, 1, EmailDate) WHERE ReminderID IN (" & strIDs & ")", conn)
    End If
    conn.Close()

'might want to write to a text file all the id's that failed to be sent.
Catch
    'write to a text file about the date this failed.
End Try

[foundsheep]

Ok, I'm going to up the db and go from there.

[SheSaidImaPregy]

Or what you can do, which would be a better bet cause it would leave less time with an open connection, is quickly store each value into an array (multi dimensional), then call it after.

The problem with this approach, you need to connect again to the server to do the update to ensure you don't update all recs, even ones that failed and never received the email.

If that is not an issue, grab all, put it into an array, do an update, then send emails.

[foundsheep]

I'm setting up the emailall page with code behind. Is that correct the way you coded it. I'm getting a lot of 'Statement cannot appear outside of a method body' errors popping up in Intellisense.

[SheSaidImaPregy]

No, that is coded for inline. Therefore it is going to need a lot of "System...." crap.

[foundsheep]

Yeah, I put a couple of those in and cleared some of them.

[foundsheep]

I'm not sure what the subroutine would be so I temporarily but some crap in there to dispel errors. Its crying about Property 'To' being read only and it doesn't like SMTPMail.whatever... Says it's obsolete.

Option Strict On
Option Explicit On

Imports System.Data.SqlClient
Imports System.Net.Mail.MailMessage
Imports System.Net.Mail.SmtpClient

Partial Class EmailAll
    Inherits System.Web.UI.Page

    Protected Sub asdfa()
        Dim conn As New SqlConnection("Data Source=IT-P02\SQLEXPRESS;Initial Catalog=EmailReminder;Integrated Security=True")
        Dim cmd As New SqlCommand("SELECT Email, FullName, Printer, ReminderID FROM Reminders WHERE Active=1 AND EmailDate<=getdate()", conn)
        Dim reader As SqlDataReader
        Dim strIDs As String = ""
        Dim strIDsFailed As String = ""
        Dim mymail As Net.Mail.MailMessage

        Try
            conn.Open()
            reader = cmd.ExecuteReader()

            If reader.HasRows Then
                While reader.Read()
                    Try
                        mymail = New Net.Mail.MailMessage()
                        mymail.To = reader("Email")
                ...
                        mymail.Body = bodyyoucreate & "Dear " & reader("FullName") & ", this is your reminder to report your meter reading for your " & reader("Printer") _
                        ". You can report it at http://www.hackworthimaging.com/meterreading.asp"
                        SMTPMail.smtpserver = ""
                        SMTPMail.Send(mymail)
                        strIDs &= "," & reader("ReminderID")
                    Catch
                        strIDsFailed &= "," & reader("ReminderID")
                    End Try
                End While
            End If

            reader.Close()

            'You should try looping through this again for all those who failed ("ids")

            If Len(strIDs) > 0 Then
                If strIDs.substring(0, 1) = "," Then strIDs.Remove(0, 1)
                cmd = New SqlCommand("UPDATE Reminders SET EmailDate=DATEADD(mm, 1, EmailDate) WHERE ReminderID IN (" & strIDs & ")", conn)
            End If
            conn.Close()

            'might want to write to a text file all the id's that failed to be sent.
        Catch
            'write to a text file about the date this failed.
        End Try
    End Sub
End Class

[SheSaidImaPregy]

yes it is obsolete, and that email stuff wasn't exactly what you needed. It was more to guide you.

You will have to make a choice whether or not you want to use System.Web.Mail or System.Net.Mail.

Web.Mail is obsolete, but guarantees a successful email (just about). Net.Mail puts it into a queue, whether it gets sent or not is unknown.

[foundsheep]

Well, that's special. I guess you'd have to do something else to get Net.Mail to work. So, I can use web.mail even though it is giving me errors?

[SheSaidImaPregy]

Yes but like the following example:

Dim MyMail As MailMessage = New MailMessage()
        MyMail.From = "youremail"
        MyMail.To = "another'semail"
        MyMail.Subject = subject
        MyMail.Body = bodyparagraph
        MyMail.BodyEncoding = Encoding.ASCII
        MyMail.BodyFormat = MailFormat.Text
        MyMail.Priority = MailPriority.Normal

        SmtpMail.SmtpServer = "mail.site.com"

[foundsheep]

For the time being I'm running this on my local machine via IIS and SMTP. How do I find out what the equivalent of "mail.site.com" would be for my machine? Will my IP work?

[SheSaidImaPregy]

I have no clue. I disable sending emails through testing, and enable it when finished. I haven't tried that as of yet.

You can try "localmachine", "localhost", "local", and then try th address of your testing server: "localhost:56023" or whatever your port is.

[foundsheep]

gotcha, I'll give that a try.

[foundsheep]

Should I be using datetime for my data type? I'm a little confused on how that will work since it is a recurring date. I will probably manually add the day it should be sent. How is that going to work?

As far as the vb, here is what I have now:

'Option Strict On
Option Explicit On

Imports System.Data.SqlClient
Imports System.Web.Mail
Imports System.Web.Mail.MailMessage


Partial Class EmailAll
    Inherits System.Web.UI.Page

    Protected Sub EmailReminder()
        Dim conn As New SqlConnection("Data Source=IT-P02\SQLEXPRESS;Initial Catalog=EmailReminder;Integrated Security=True")
        Dim cmd As New SqlCommand("SELECT Email, FullName, Printer, ReminderID FROM Reminders WHERE Active=1 AND EmailDate<=getdate()", conn)
        Dim reader As SqlDataReader
        Dim strIDs As String = ""
        Dim strIDsFailed As String = ""
        Dim MyMail As MailMessage = New MailMessage()
        Dim emailbody As String = ""

        Try
            conn.Open()
            reader = cmd.ExecuteReader()

            If reader.HasRows Then
                While reader.Read()
                    Try
                        MyMail = New MailMessage()
                        MyMail.From = "[e-mail]"
                        MyMail.To = reader("Email")
                        MyMail.Subject = "Meter Reading Reminder"
                        MyMail.Body = emailbody & "Dear " & reader("FullName") & ", this is your reminder to report your meter reading for your " & reader("Printer") & _
                        ". You can report it at http://www.hackworthimaging.com/meterreading.asp"
                        MyMail.BodyEncoding = Encoding.ASCII
                        MyMail.BodyFormat = MailFormat.Text
                        MyMail.Priority = MailPriority.Normal

                        SmtpMail.SmtpServer = "localhost"
                        SmtpMail.Send(MyMail)
                        strIDs &= "," & reader("ReminderID")
                    Catch
                        strIDsFailed &= "," & reader("ReminderID")
                    End Try
                End While
            End If

            reader.Close()

            'You should try looping through this again for all those who failed ("ids")

            If Len(strIDs) > 0 Then
                If strIDs.Substring(0, 1) = "," Then strIDs.Remove(0, 1)
                cmd = New SqlCommand("UPDATE Reminders SET EmailDate=DATEADD(mm, 1, EmailDate) WHERE ReminderID IN (" & strIDs & ")", conn)
            End If
            conn.Close()

            'might want to write to a text file all the id's that failed to be sent.
        Catch
            'write to a text file about the date this failed.
        End Try
    End Sub
End Class

What else needs to be done to test this motha out?

[SheSaidImaPregy]

smalldatetime field type within the database. That will come out with format:

MM/DD/YYYY HH:MM:SS AM

When you are looking if it is equal to today, then you are only looking at MM/DD/YYYY so that is fine.

[foundsheep]

Does it include that sweet emoticon in the db?

[foundsheep]

Based on what I have now, what needs to be done next in order to test it?

My DB:
ReminderID
Company
FullName
Printer
Email
Email Date

My code:

Option Explicit On

Imports System.Data.SqlClient
Imports System.Web.Mail
Imports System.Web.Mail.MailMessage


Partial Class EmailAll
    Inherits System.Web.UI.Page

    Protected Sub EmailReminder()
        Dim conn As New SqlConnection("Data Source=IT-P02\SQLEXPRESS;Initial Catalog=EmailReminder;Integrated Security=True")
        Dim cmd As New SqlCommand("SELECT Email, FullName, Printer, ReminderID FROM Reminders WHERE Active=1 AND EmailDate<=getdate()", conn)
        Dim reader As SqlDataReader
        Dim strIDs As String = ""
        Dim strIDsFailed As String = ""
        Dim MyMail As MailMessage = New MailMessage()
        Dim emailbody As String = ""

        Try
            conn.Open()
            reader = cmd.ExecuteReader()

            If reader.HasRows Then
                While reader.Read()
                    Try
                        MyMail = New MailMessage()
                        MyMail.From = "[e-mail]"
                        MyMail.To = reader("Email")
                        MyMail.Subject = "Meter Reading Reminder"
                        MyMail.Body = emailbody & "Dear " & reader("FullName") & ", this is your reminder to report your meter reading for your " & reader("Printer") & _
                        ". You can report it at http://www.hackworthimaging.com/meterreading.asp"
                        MyMail.BodyEncoding = Encoding.ASCII
                        MyMail.BodyFormat = MailFormat.Text
                        MyMail.Priority = MailPriority.Normal

                        SmtpMail.SmtpServer = "localhost"
                        SmtpMail.Send(MyMail)
                        strIDs &= "," & reader("ReminderID")
                    Catch
                        strIDsFailed &= "," & reader("ReminderID")
                    End Try
                End While
            End If

            reader.Close()

            'You should try looping through this again for all those who failed ("ids")

            If Len(strIDs) > 0 Then
                If strIDs.Substring(0, 1) = "," Then strIDs.Remove(0, 1)
                cmd = New SqlCommand("UPDATE Reminders SET EmailDate=DATEADD(mm, 1, EmailDate) WHERE ReminderID IN (" & strIDs & ")", conn)
            End If
            conn.Close()

            'might want to write to a text file all the id's that failed to be sent.
        Catch
            'write to a text file about the date this failed.
        End Try
    End Sub
End Class

[SheSaidImaPregy]

you can test it, however, I don't know if localhost is a valid smtp server, so it may fail on you with that.

oh, though you might want to.. response.write() your failed ID's

[foundsheep]

Excuse my ignorance ... how is the email reminder actually triggered? Do I put today's date in the db and let it rip or what?

[SheSaidImaPregy]

No it should be put in a file, then you can call the file for testing.

When it is real life practice, you should set up a trigger to target that file at a certain time. That way it will fire.

[foundsheep]

Ok, what kind of file are we talking about and where is the trigger? In the file? From my questions, you can ascertain, I'm clueless.

[SheSaidImaPregy]

.NET comes with a class called "Timer". It is used to set an interval to call a function after that interval. I am not sure at the max time allowed, but you might be able to set the interval to run at every 12 hours, or at every 24 hours (depending on how long it allows). Look up this on google:

site:microsoft.com Timer class

[foundsheep]

I did look up the timer class. I'm not sure about if it allows once every 24 hours. It can run more than that as long as it runs once. Does the timer class need to be set up in a separate file?

[SheSaidImaPregy]

No you can do it in the same class. However, I have never used the timer class, and I do not know if it will still run if the webpage has been closed.

You should look into running a windows service. However, if you will be able to do it or not is another question.

It's quite easy, and not much to it. Try it out. Here's a code project article that I like about this:

http://www.codeproject.com/KB/dotnet/simplewindowsservice.aspx

[foundsheep]

As far as I can tell, in order to create a windows service I need to have visual studio. I don't. I'm working with visual web dev.

Would it work if the webpage was open on the server continually?