Automatic eMail...

[weee]

I have an DB with Members details in it.
Is it posibble to send each member an automatic email whenever his registration period is expired?

Thanks

[PeOfEo]

Yes, but not with an asp or asp.net script. I would use a .exe and have it run as a service, or run on startup in the system tray, and this application could select user emails from the data base every hour on the hour (or every day on the day... whatever you want) and send out an email if their their registration period id expired

[CardboardHammer]

Here's VB.NET code for such an application, with the sensitive info edited out. (The application that this code derives from uses a local SQL Server db, which likely won't match your scenario, but it's probably better than nothing.)


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

Module Module1
Function Main() As Int32
Dim strEmail As String
Dim con As New SqlConnection("Data Source=local);Net=dbnmpntw;Database=BLAH;")
Dim cmd As New SqlCommand("GET_MAILING_LIST", con)
cmd.CommandType = CommandType.StoredProcedure

Dim msgmail As MailMessage = New MailMessage()
SmtpMail.SmtpServer = "###.###.###.###"
msgmail.From = "BLAH@BLAH.BLAH"

Dim rdr As SqlDataReader
con.Open()
rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
While rdr.Read()
Try
strEmail = rdr.GetString(0)
msgmail.To = strEmail
msgmail.Body = "BODY"
msgmail.Subject = "SUBJECT"
SmtpMail.Send(msgmail)
Catch exc As Exception
'handle however you want
End Try
End While

rdr.Close()
rdr = Nothing
cmd.Dispose()
cmd = Nothing
con.Close()
con.Dispose()
con = Nothing
msgmail = Nothing
Return 0
End Function
End Module

[buntine]

The reason you cant do it in ASP or ASP.NET is because the scripts will only be invoked when the page is requested from a clients machine.

PeOfEo's right, the best way would be to write an executable program which continually grabs the records from the database and then sends email to everyone whos account has expired.

CardboardHammers example will e helpful to you, however, it will take alot more code.

Regards,
Andrew Buntine.

[zingmatter]

If you get more than one hit per day on you site then there's no reason to not stick a script into your index.asp page that will check your db for expired members and then mail them.



'//code to detect expired members
:
:
if (membershipExpired) then
Server.execute("email_expired_message.asp")
end if



I do a similar thing to check for expired messages on a message board (I marked them as expired rather than sending out emails but the idea's the same).

[buntine]

Agreed. This method will be more efficient. Provided your site is invoked saveral times a day.

Regards.

[PeOfEo]

but you could also be invoking the script hundreds of times and spamming people who are past that time limit if you do not first flag their accounts after that first email.

[CardboardHammer]

Originally posted by buntine
...

CardboardHammers example will e helpful to you, however, it will take alot more code.

...

Actually, if one fills in the placeholders, has SQL Server, and writes the stored procedure (which need not be all that complex), that's all it takes. (Even switching to using a different db shouldn't take much).

Anything more mostly comes down to deciding whether the cost of avoiding a particular error is less than or equal to the expected cost of the error.

[CardboardHammer]

Another thing...

Scheduling and running a batch mailing once per day is tons more efficient than running it once per request of index.asp (unless that page is requested around once per day, in which case it's pretty obvious that there's not much interest in membership anyways).

[zingmatter]

but you could also be invoking the script hundreds of times and spamming people who are past that time limit if you do not first flag their accounts after that first email.

I would think it rather foolish to not mark an expired membership as having been mailed. It's not a big deal just to do is have a boolean field in you db e.g. expire_email_sent set to True.

Certainly a stored procedure on SQL Server would be the most efficient way to do this but not everyone has that luxury.

Anyway, running a quick 'SELECT expire_date FROM member_table' isn't that bad - chances are you may well be connecting to the db anyway if logging someone in.

Running a once a batch mailing would seem the best option though. All the webmaster need do is click a link to activate the script.

[PeOfEo]

It would still be calling that script every time the page is loaded and sifting through a (I am assumeing large) data base which would be very inefficient. I stnad by an application because the webmaster would only need to put it on the server and its done. It can be running as a service, be sitting in the system tray and run at startup, or just be called with schedualer and run.

[weee]

!