Mailing list from database.

[techskool]

Ive currently got an ASP site with an Access Database. I add records to the database sometimes daily sometimes only weekly.

I want a mailing list so current customers (who are also logged in one table in the database) and new ones that sign up can be informed when new stock is added to the stock table.

The stock table has a date added field that i did in anticipation a few months back so when an item is logged the date is written to a field.

I currently use CDONTS for generating system messages from my shopping basket so thats not a problem but what would the code look like for this mailing list.

I started to think about it, and i cant just have it sending a mail everytime there is a new item added, someone might get ten emails a day.

Any ideas

Thanx

Dave

 

[bduke]

Do it once a day - if added by checking the date and if the date is current date and current time = 1:00 pm then send an email - they might not get current after 1:00 but you can think about this.

 

[techskool]

Okay so something like (very roughly):
----
For all records
If Datelogged = Today
and Currenttime = 11.59pm
read these stock items into a new recordset object


For all opted in emails in the database
Call CDONTS FUNCTION
send message including text examples of all new stock.
---

Questions:
1)How would the system look through the records, would there be a recordset object open all the time equating the truth of new stock on current day?

2)What would the code look like a little more accurately than my plan above.

Thanks in advance

Dave

 

[techskool]

Im a bit tired now, so im off to bed, but ive done this so far:

-----
<!--#include file=&quot;cdonts.asp&quot; -->
<%
If Time = &quot;02:22:01&quot; Then

'fill the recordset with matches for todays records
dim conn
dim rsNew
dim rsCust
dim custSQL
dim sql
set conn = server.createobject(&quot;adodb.connection&quot
conn.open Application(&quot;strconn&quot
set rsNew = server.createobject(&quot;adodb.recordset&quot
'grab the new records
sql = &quot;SELECT IDref, Artist, Title, Format, YearMade, Price, DateLogged FROM stock WHERE DateLogged =&quot;& Date
rsNew.open sql, conn, 3
'grab the customers who want mailouts
custSQL = &quot;SELECT Email FROM customer&quot;
Set rsCust = conn.Execute(custSQL)

If rsNew <> &quot;&quot; Then

If rsCust <> &quot;&quot; Then

Do While Not rsCust.Bof And Not rsCust.Eof

SetLocale(&quot;en-gb&quot
eTo = (&quot;dvdtsh113@tiscali.co.uk&quot 'rsCust(&quot;Email&quot
eFrom = (&quot;admin@recordsnorthwest.com&quot
eSubject = (&quot;recordsnorthwest mailing list&quot

Do While Not rsNew.Bof And Not rsCust.New
eBody = eBody & &quot;Stock No :&quot;&rsNew(&quot;IDref&quot & vbcrlf
eBody = eBody & &quot;Format :&quot;&rsNew(&quot;Format&quot & vbcrlf
eBody = eBody & &quot;Artist :&quot;&rsNew(&quot;Artist&quot & vbcrlf
eBody = eBody & &quot;Title :&quot;&rsNew(&quot;Title&quot & vbcrlf
eBody = eBody & &quot;Year :&quot;&rsNew(&quot;YearMade&quot & vbcrlf
eBody = eBody & &quot;Price :&quot;&formatcurrency(rs2(&quot;Price&quot,2) & vbcrlf & vbcrlf
rsNew.MoveNext
Loop

oEmail.Send

rsCust.MoveNext
Loop

End If

End If

End If
%>

Anyone see any problems with it??

Thanx again to all

Dave



-----

 

[techskool]

ps...

please ignore:
Do While Not rsNew.Bof And Not rsCust.New
it should be
Do While Not rsNew.Bof And Not rsNew.Eof

Ive also made the Eto my email cos i dont want customers getting random mails till this is working.

Thanx

Dave