I came up with an idea, but I can't make it work because of my limited SQL knowledge.

I want to create a script that will run once or twice a day that will generate an email to be send to my users to let them know there is a spam message addressed to them in the quaruntine. Can anyone help me with this?

Kevin

Hey Dan, as you know i'm not much of a programmer, but I can come up with great ideas :)

Basically, what I would it to do is this.

Count the # of spam for a user, and email them with a generic email to the counted email address, containing the email address mailed to, the number of spam counted and a link to where they can check the spam?

can this be done easily?

What I don't want is a spam sent out EACH time a spam enters the q. I want this to happen only twice a day. Maybe even onyl once a day.

Kevin

Kevin -

I've had this working since I first started using SpamFilterISP.  It's dependant on the Windows scheduler (on the SF server) to run one page(auto_notify.asp') in IE hourly during business hours, and a second page('auto_reminder.asp') at 6am each business day.  I am not an ISP; I use SF at my business... so this will be different for those of you processing 100's of thousands of messages a day (server load to process these pages, script timeout, etc.)... we average under 10K messages daily.

Some users are annoyed with messages throughout the day, and just want one reminder in the morning, so I gave them both options (some choose to have both).  The morning reminder is limited (you can easily change this) to only notify those with more than 50 messages in the quarantine.

I modified the 'tbllogins' table to add two fields - 'notify' and 'reminder'. (I'm using MySQL).  They are both type 'bool' fields, defaulting to -1 (on).

At the top of the 'listspam.asp' page I added:
--------------------------------------------------------------------------------
<%
 'update the reminder field for this user
 IF Request.QueryString("Reminder") = "on" THEN
  SQL = "UPDATE tblLogins SET Reminder = -1 WHERE (Email = '" & EmailTo & "')"
  Set rs = con.Execute(SQL)
 ELSEIF Request.QueryString("Reminder") = "off" THEN
  SQL = "UPDATE tblLogins SET Reminder = 0 WHERE (Email = '" & EmailTo & "')"
  Set rs = con.Execute(SQL) 
 END IF

 'update the notification field for this user
 IF Request.QueryString("Notify") = "on" THEN
  SQL = "UPDATE tblLogins SET Notify = -1 WHERE (Email = '" & EmailTo & "')"
  Set rs = con.Execute(SQL)
 ELSEIF Request.QueryString("Notify") = "off" THEN
  SQL = "UPDATE tblLogins SET Notify = 0 WHERE (Email = '" & EmailTo & "')"
  Set rs = con.Execute(SQL) 
 END IF
%>
--------------------------------------------------------------------------------

At the bottom of the 'listspam.asp' page, I have added the following:
--------------------------------------------------------------------------------
<%
 SQL = "SELECT Reminder FROM tblLogins WHERE (Email = '" & EmailTo & "')"
 Set rs = con.Execute(SQL)
 IF clng(rs("Reminder")) = -1 THEN
  response.write "Reminder is <font size=""2"" color=""red"">ON</font>. Turn Reminder <a href=""ListSpam.asp?Reminder=off"">Off</a>"  & vbCRLF
 ELSE
  response.write "Reminder is <font size=""2"" color=""red"">OFF</font>. Turn Reminder <a href=""ListSpam.asp?Reminder=on"">On</a>"  & vbCRLF
 END IF
 response.write "&nbsp; - - A daily reminder to check your quarantined messages will be sent to you if this feature is turned on.<br><br>" & vbCRLF

 SQL = "SELECT Notify FROM tblLogins WHERE (Email = '" & EmailTo & "')"
 Set rs = con.Execute(SQL)
 IF clng(rs("Notify")) = -1 THEN
  response.write "Auto-Notify is <font size=""2"" color=""red"">ON</font>. Turn Auto-Notify <a href=""ListSpam.asp?notify=off"">Off</a>"  & vbCRLF
 ELSE
  response.write "Auto-Notify is <font size=""2"" color=""red"">OFF</font>. Turn Auto-Notify <a href=""ListSpam.asp?notify=on"">On</a>"  & vbCRLF
 END IF
 response.write "&nbsp; - - An immediate notification of possible spam will be sent to you if this feature is turned on.<br>" & vbCRLF
%>
--------------------------------------------------------------------------------

The above code gives the users a 'toggle' to turn on and off their notification messages.

Next, I have the following saved as 'auto_notify.asp':
--------------------------------------------------------------------------------
<html><body>
Sending Reminders to those with messages in queue and reminders tuned on.<br><br>
<%
X = 0
SQL = "SELECT * FROM tbllogins WHERE Notify = -1 ORDER BY Email ASC"

 Set rs = con.Execute(SQL)
 IF NOT (rs.eof AND rs.bof) THEN
  rs.MoveFirst
  DO UNTIL rs.eof
   SQL = "SELECT COUNT(*) AS MessageCount FROM tblQuarantine WHERE (EmailTo = '" & rs("Email") & "') AND Expire = 0 AND Deliver = 0"
   Set rs2 = con.Execute(SQL)
   IF clng(rs2("MessageCount")) > 0 THEN
    X = X + 1
    SendMessage rs("Email"), rs2("MessageCount")
    response.write rs("Email") & " has " & rs2("MessageCount") & " messages in queue... notification sent.<BR>" & vbcrlf
   END IF
   rs2.close
   set rs2 = nothing
   rs.MoveNext
  LOOP
 END IF

response.write "<BR>" & X & " notification messages sent.<br><br><br>" & vbCRLF

SUB SendMessage(strEmail, lMessageCount)
 Set rs3 = Server.CreateObject("ADODB.Recordset")
 Set rs3.ActiveConnection = con
 rs3.CursorType = 1
 rs3.LockType = 3
 'Send message to user by way of the quarantine.
 MessageDate = Now
 Message = "From: System Administrator <>" & vbCRLF
 Message = Message & "To: " & strEMail & vbCRLF
 Message = Message & "Subject: possible SPAM Notification: You have messages being held in quarantine" & vbCRLF
 Message = Message & "Date: " & FormatDateTime(MessageDate, vbLongDate) & " " & FormatDateTime(MessageDate, vbLongTime) & vbCRLF
 Message = Message & "Content-Type: text/html;charset=""ISO-8859-1""" & vbCRLF & vbCRLF
 Message = Message & "Your have " & lMessageCount & " messages being held in the quarantine area" & vbCRLF
 Message = Message & "For your email account '" & strEmail & "'." & vbCRLF & vbCRLF
 Message = Message & "THESE MESSAGES **MAY** HAVE ALREADY BEEN CLEARED BY THE SYSTEM ADMINISTRATOR FOR YOU" & vbCRLF & vbCRLF
 Message = Message & "Please log into http://(your quarantine website) and check your messages." & vbCRLF & vbCRLF
 Message = Message & "This message was sent because you turned auto-notification on." & vbCRLF
 Message = Message & "." & vbCRLF
 SQL = "SELECT * FROM tblMsgs WHERE 0 = 1"
 rs3.Open(SQL)
 rs3.AddNew
 rs3("Msg") = Message
 rs3.Update
 MsgID = rs3("MsgID")
 response.write "MsgID= " & msgID & "<BR>" & vbCRLF
 rs3.Close
 SQL="SELECT * FROM tblQuarantine WHERE 0 = 1"
 rs3.Open(SQL)
 rs3.AddNew
 rs3("EMailFrom") = "System Administrator <>"
 rs3("EMailTo") = strEMail
 rs3("Subject") = "Daily Reminder: You have messages being held in quarantine"
 rs3("MsgID") = MsgID
 rs3("MsgDate") = MessageDate
 rs3("Expire") = 0
 rs3("Deliver") = 1
 rs3("RejectDetails") = ""
 rs3("RejectID") = 0
 rs3("ServerID") = 1
 rs3.Update
 rs3.Close
 Set rs3 = Nothing
END SUB
%>
</body>
</html>
--------------------------------------------------------------------------------

Thew following is saved as 'auto_reminder.asp':
--------------------------------------------------------------------------------
<html>
<body>
Sending Reminders to those with messages in queue and reminders tuned on.<br><br>
<%
X = 0
 SQL = "SELECT * FROM tbllogins WHERE Reminder = -1 ORDER BY Email ASC"
 Set rs = con.Execute(SQL)
 IF NOT (rs.eof AND rs.bof) THEN
  rs.MoveFirst
  DO UNTIL rs.eof
   SQL = "SELECT COUNT(*) AS MessageCount FROM tblQuarantine WHERE (EmailTo = '" & rs("Email") & "') AND Expire = 0 AND Deliver = 0"
   Set rs2 = con.Execute(SQL)
   IF clng(rs2("MessageCount")) > 0 THEN
    X = X + 1
    SendMessage rs("Email"), rs2("MessageCount")
    response.write rs("Email") & " has " & rs2("MessageCount") & " messages in queue... reminder sent.<BR>" & vbcrlf
   ELSE
    response.write rs("Email") & " has 0 messages in queue... reminder NOT sent.<BR>" & vbcrlf
   END IF
   rs2.close
   set rs2 = nothing
   rs.MoveNext
  LOOP
 END IF
response.write "<BR>" & X & " reminder messages sent.<br><br><br>" & vbCRLF
response.write "Sending reminders to those with excessive messages in queue (50 or more).<BR><BR>" & vbCRLF & vbCRLF
X = 0
  SQL = "SELECT DISTINCT EmailTo "
  SQL = SQL & "FROM tblquarantine "
  SQL = SQL & "WHERE Expire = 0 "
  SQL = SQL & "ORDER BY EmailTo"
  Set rs = con.Execute(SQL)
  If rs.EOF Then
   Response.Write "<h2>No addresses in database!</h2>"
  Else
   rs.movefirst
   While Not rs.EOF
  &nb

Wow, thanks for the quick reply!

Just so I understand this, the auto-notify only sends to people who switch this option on, and autoreminder sends to everyone in the database?

Well, i'm playing around with the code, I do want to allow people who have setup an account in spamfilter to turn off notification, right now i'm just playing around with trying to get the web code working so they can turn it on and off lol

Hey Ric, when you get a chance, can you fire me an email to kevin@pare.ca so I can talk to you about this code? The logic is perfect, I just can't get it to work!

Kevin