Does anyone know how I can produce an email alert system that notifies subscribers of udates to the site, something similar autotrader.co.uk. FOr example, if I want to buy a used Ford Focus, I can set up an email alert when one comes up for sale.

Cheers

DMX | ASP | VBScript | ACCESS | IIS5

Edited by - sesame on 21 Jun 2004 18:00:07

From the sounds of it i would guess that you're talking about a deployed website rather than a system where you have control over permissions - so I think these are your options:

you can use xp_sendmail from within SQL server which would be called on a trigger fired when the car comes up for sale (hosts probably wont give you permisssion)

if you only need to send the mail once a day I would consider using WSH to automate a VBS file. My host was happy to set up the WSH for me after I uploaded the VBS's
in the vbs simply get a recordset of people who've requested to be notified of available cars then iterate through that and generate a recordset of cars that match their criteria and compose a nice html email.

i have a script i'm using that does basically what you want - if you want a lookie let me know

HTH

Simon

Edited by - ganseki on 21 Jun 2004 18:36:37

Thanks Simon. Yes it is a hosted website that I am developing. I want to be able to generate email alerts like you have on www.flatrents.com/

I have read your response a couple of times but I must admit it's still going straight over my head! I am only just getting to grips with SQL Server and have no experience of WSH. Can you please give me a bit more guidance. It is a once a day mail i'd like to send.

BTW - I also need a host with ASP mySQL or MSSQL that allows multiple e-mails to be sent out. I am currently with Fasthosts but they don't allow such use of their servers. I guess they don't want unsolicited emails sent through their servers.

Thanks

STefan

DMX | ASP | VBScript | ACCESS | SQL Server | IIS5

For hosting I went with www.redstation.co.uk - Flatrents is hosted on their business and SQL server plans and they were happy to set up the WSH stuff I needed.

The WSH is a bit like task scheduler, it just runs your script at a given time - I asked for www.flatrents.com automated emails to be sent during the night and they did the rest - but if you're interested here's the code for one:

[ScriptFile]
Path="C:\emailscript.vbs"
[Options]
Timeout=30
DisplayLogo=0

The vbs is probably too long to include here - I could email you a copy if you want.

In summary it looks like this though:
Open a link to the db
set a variable with a select for people you want to email
if not rs.eof then
do while
set variables for your select for what to send them

then build your cdo email using the variables


Sharing knowledge saves valuable time!

Simon

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote> I am currently with Fasthosts but they don't allow such use of their servers.<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

hmmm, me too. I may change now you've mentioned this.

Simon, how much is an SQL platform on redstation?

Regards,
Dave

<img src="www.nova-cs.co.uk/DmxAvatar.jpg" border="0">" border=0>

[DWMX 2004]|[FlashMX 2004 Pro]|[SQL]|[Access2000]|[ASP/VBScript]|[XP-Pro]

Redstation SQL 2000 DB hosting is £240 per year.
You get 100mb Webspace on a Dual Xeon driven PowerEdge box - anti virus, back up yada yada yada.
Their entry level hosting is £59 a year and .net enabled and so on...

Yikes, I sound like I work in their sales dept!

&lt;disclaimer&gt;
There have been a few times when I have wanted to get technical support (getting my IP unblocked from their firewall) and the phones were busy - so I had to use the email support. I would suggest checking that out before you move hosts.
&lt;/disclaimer&gt;

Sharing knowledge saves valuable time!

Simon

Thanks Simon,

That's given me a bit of a head start <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Stefan

DMX | ASP | VBScript | ACCESS | SQL Server | IIS5

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
From the sounds of it i would guess that you're talking about a deployed website rather than a system where you have control over permissions - so I think these are your options:

you can use xp_sendmail from within SQL server which would be called on a trigger fired when the car comes up for sale (hosts probably wont give you permisssion)

if you only need to send the mail once a day I would consider using WSH to automate a VBS file. My host was happy to set up the WSH for me after I uploaded the VBS's
in the vbs simply get a recordset of people who've requested to be notified of available cars then iterate through that and generate a recordset of cars that match their criteria and compose a nice html email.

i have a script i'm using that does basically what you want - if you want a lookie let me know

HTH

Simon

Edited by - ganseki on 21 Jun 2004 18:36:37
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Hi I am trying to the same as above - to schedule email alerts at the end of each day. Not havong much luck though. Basicaly I have a cdosys email script to iterate through a record set of saved job alerts and then I manually run this script at the end of each day to send emails to jobseekers whos email alerts match any jobs posted that day. However the script simply times out half way through. I was wondering if you could let me have a look at the VBS file that you are using to do the above. Perhaps it might be solution for me.

thanks

ozzii

Hi Ozzii

Here's the script I'm using. You'll need to modify it for your own situation but it should give you a starting point at least.
Hope it helps!

<pre id=code><font face=courier size=2 id=code>
' /////////////////////////////////////////////////////////////////////////////
' Simon Martin
' 24/10/2003
' VB Script Document
Option Explicit

Dim strTo, strFrom, strSubject, strBody, tmpBody
Dim Conn, rsCandidate_cmd, rsCandidate, rsJobs_cmd, rsJobs
Dim intCID, strFname, strLname, strEmail, intJobType, intJobHours, intJobDiv, intJobRegion, strKeyword

Dim siteURL
'siteURL = "testserver/mytestsite/" ' Dev server
siteURL = "www.mysite.co.uk/" ' Live Server

' Set up connection to database
Set Conn = CreateObject("ADODB.Connection"
Conn.Open("Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=123.456.789.012;Initial Catalog=MYDATABASE;User ID=MYUSER;Password=MYPASS; Application Name=MYSITE"
' Dev server conn
'Conn.Open("Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=192.168.1.18;Initial Catalog=MYDATABASE;User ID=MYUSER;Password=MYPASS; Application Name=MYSITE"

' Create a command
Set rsCandidate_cmd = CreateObject("ADODB.Command"
rsCandidate_cmd.ActiveConnection = Conn

' Open recordset and fill it with candidates who have opted to receive emails
rsCandidate_cmd.CommandText = "uspGet_JobAlertCandidates"
rsCandidate_cmd.CommandType = 4
Set rsCandidate = rsCandidate_cmd.Execute

' If we have results
If Not rsCandidate.EOF Then

' iterate through the rs assigning values to local vars
Do While Not rsCandidate.EOF
intCID = Trim(rsCandidate("cID")
strFname = Trim(rsCandidate("firstname")
strLname = Trim(rsCandidate("lastname")
strEmail = Trim(rsCandidate("email")

intJobType = Trim(rsCandidate("jobType")
intJobHours = Trim(rsCandidate("jobHours")
intJobDiv = Trim(rsCandidate("jobDiv")
intJobRegion = Trim(rsCandidate("jobRegion")
strKeyword = Trim(rsCandidate("keywords")

' select jobs that meet criteria for candidates
Set rsJobs_cmd = CreateObject("ADODB.Command"
rsJobs_cmd.ActiveConnection = Conn

rsJobs_cmd.CommandText = "{call dbo.uspGet_JobAlertJobs(?,?,?,?,?)}"
rsJobs_cmd.Prepared = true
rsJobs_cmd.Parameters.Append rsJobs_cmd.CreateParameter("jobType", 200, 1, 255, intJobType) ' adVarChar
rsJobs_cmd.Parameters.Append rsJobs_cmd.CreateParameter("jobHours", 200, 1, 255, intJobHours) ' adVarChar
rsJobs_cmd.Parameters.Append rsJobs_cmd.CreateParameter("jobDiv", 200, 1, 255, intJobDiv) ' adVarChar
rsJobs_cmd.Parameters.Append rsJobs_cmd.CreateParameter("jobRegion", 200, 1, 255, intJobRegion) ' adVarChar
rsJobs_cmd.Parameters.Append rsJobs_cmd.CreateParameter("keyword", 200, 1, 100, strKeyword) ' adVarChar

Set rsJobs = rsJobs_cmd.Execute

' If there are jobs for the candidate
If Not rsJobs.EOF Then
' build the body of the email
tmpBody = "&lt;style type=""text/css""&gt;" & vbcrlf
tmpBody = tmpBody & "&lt;!--" & vbcrlf
tmpBody = tmpBody & ".style1 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; font-weight: bold; ;}" & vbcrlf
tmpBody = tmpBody & ".style2 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px;}" & vbcrlf
tmpBody = tmpBody & ".style4 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; font-weight: bold; }" & vbcrlf
tmpBody = tmpBody & "--&gt;" & vbcrlf
tmpBody = tmpBody & "&lt;/style&gt;" & vbcrlf
tmpBody = tmpBody & "&lt;p&gt;Dear &lt;strong&gt;" & strFname & ", &lt;/strong&gt;&lt;br /&gt;" & vbcrlf
tmpBody = tmpBody & "Thank you for registering with MYSITE and welcome to your personal Job Alert. &lt;/p&gt;" & vbcrlf
tmpBody = tmpBody & "&lt;p&gt;Here is a selection of the latest properties which match your search criteria. "
tmpBody = tmpBody & "Click on to the link below each Job to see further details. &lt;/p&gt;" & vbcrlf

' Build the dynamic portion of the email
'rsJobs.MoveFirst
While Not rsJobs.EOF
tmpBody = tmpBody & "&lt;table width=""583"" border=""0"" cellpadding=""0"" cellspacing=""0"" style=""border:1px solid #ccc; padding:10px;""&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td width=""583"" height=""""&gt;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td height="""" valign=""top""&gt;&lt;table width=""100%"" border=""0"" cellpadding=""0"" cellspacing=""0""&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td width=""583"" height="""" valign=""top""&gt;&lt;span class=""style1""&gt;" & rsJobs.Fields("job_position" & " (" & rsJobs.Fields("job_reference" & "&lt;/span&gt;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/table&gt;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td height="""" valign=""top""&gt;&lt;hr style=""border:1px solid #e5e5e5;""/&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;table width=""100%"" border=""0"" cellpadding=""0"" cellspacing=""0""&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td width=""111"" height="""" valign=""top""&gt;&lt;span class=""style4""&gt;Description:&lt;/span&gt;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td width=""34""&gt;&nbsp;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td width=""438"" valign=""top""&gt;&lt;span class=""style2""&gt;"
tmpBody = tmpBody & Trim(rsJobs.Fields("job_description")
tmpBody = tmpBody & " &lt;/span&gt;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/table&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;table width=""100%"" border=""0"" cellpadding=""0"" cellspacing=""0""&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;hr style=""border:1px solid #e5e5e5;""/&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td width=""111"" height=""19"" valign=""top""&gt;&lt;span class=""style4""&gt;Location:&lt;/span&gt;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td width=""33""&gt;&nbsp;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td width=""439"" valign=""top""&gt;&lt;span class=""style2""&gt;"
tmpBody = tmpBody & rsJobs.Fields("job_location"
tmpBody = tmpBody & "&lt;/span&gt;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/table&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;table width=""100%"" border=""0"" cellpadding=""0"" cellspacing=""0""&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;hr style=""border:1px solid #e5e5e5;""/&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td width=""111"" height=""19"" valign=""top""&gt;&lt;span class=""style4""&gt;Salary:&lt;/span&gt;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td width=""34""&gt;&nbsp;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td width=""438"" valign=""top""&gt;&lt;span class=""style2""&gt;"
tmpBody = tmpBody & rsJobs.Fields("job_salary"
tmpBody = tmpBody & "&lt;/span&gt;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/table&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;table width=""100%"" border=""0"" cellpadding=""0"" cellspacing=""0""&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;hr style=""border:1px solid #e5e5e5;""/&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td width=""583"" valign=""top""&gt;&lt;a href=" & vbcrlf
tmpBody = tmpBody & siteURL & "jobdetails.asp?jobID=" & rsJobs.Fields("pk_job_id" & " class=""style2""&gt;"
tmpBody = tmpBody & siteURL & "jobdetails.asp?jobID=" & rsJobs.Fields("pk_job_id" & "&lt;/a&gt;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/table&gt;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;tr&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;td height=""16""&gt;&nbsp;&lt;/td&gt;" & vbcrlf
tmpBody = tmpBody & " &lt;/tr&gt;" & vbcrlf
tmpBody = tmpBody & "&lt;/table&gt;" & vbcrlf
tmpBody = tmpBody & "&lt;br /&gt;" & vbcrlf
tmpBody = tmpBody & "&lt;!-- End Job Table --&gt;" & vbcrlf

rsJobs.MoveNext
Wend

' Build the footer to the email
tmpBody = tmpBody & "&lt;p&gt;&lt;a href=""" & siteURL & "login.asp""&gt;Login&lt;/a&gt; to amend your search criteria at any time. &lt;/p&gt;" & vbcrlf
tmpBody = tmpBody & "&lt;p&gt;&lt;a href=""" & siteURL & "jobSearch.asp""&gt;Search all Jobs&lt;/a&gt;&lt;/p&gt;" & vbcrlf
tmpBody = tmpBody & "&lt;p&gt;Please tell your friends about &lt;a href=""" & siteURL & """&gt;MYSITE&lt;/a&gt; "
tmpBody = tmpBody & "and help us grow. &lt;/p&gt;" & vbcrlf
tmpBody = tmpBody & "&lt;p&gt;Yours sincerely, &lt;br /&gt;" & vbcrlf
tmpBody = tmpBody & "The MYSITE Recruitment Team. &lt;/p&gt;" & vbcrlf
tmpBody = tmpBody & "&lt;hr align=""left"" width=""300"" size=""1"" noshade color=""#333399""&gt; &lt;br /&gt;" & vbcrlf
tmpBody = tmpBody & "To unsubscribe from this email: &lt;br /&gt;" & vbcrlf
tmpBody = tmpBody & "You can unsubscribe from this newsletter by clicking on the following link or by cutting "
tmpBody = tmpBody & "and pasting it into your browser: &lt;br /&gt;" & vbcrlf
tmpBody = tmpBody & "&lt;a href=""" & siteURL & "commands/unsubscribe.asp?email=" & strEmail & """&gt;"
tmpBody = tmpBody & siteURL & "commands/unsubscribe.asp?email=" & strEmail & "&lt;/a&gt;&lt;br /&gt;" & vbcrlf
tmpBody = tmpBody & "This email comes from an unmonitored email account&lt;/p&gt;"

'response.Write(tmpBody)
strFrom = " " 'System administrator account
strTo = strEmail 'Recipient mail account
strSubject = "MYSITE Job Alert for " & strFname & " on " & Now() 'Mail subject

' ////////////////////////////////////////////////////////////////
' Hard work done. Send the email
Dim objNewMail
Set objNewMail = CreateObject("CDO.Message"
With objNewMail
.From = strFrom
.To = strTo
.Subject = strSubject
.HTMLBody = tmpBody
.Send
End With
Set objNewMail = Nothing

'Now move to the next candidate
rsCandidate.MoveNext

' ////////////////////////////////////////////////////////////////
Else ' no results for the candidate so go to the next one
rsCandidate.MoveNext
End If ' end if not EOF rsJobs

Loop ' iterate through the candidates

End If ' end if not eof candidates

rsCandidate.Close()
Set rsCandidate = Nothing
Set rsCandidate_cmd = Nothing

rsJobs.Close()
Set rsJobs = Nothing
Set rsJobs_cmd = Nothing

Conn.Close()
Set Conn = Nothing
</font id=code></pre id=code>

Live the life you love
Love the life you live
~ ~ ~ ~ ~ ~ ~
<b>Simon Martin</b> - <i>DMXzone Manager</i>
<font size=1>[ Dreamweaver MX/MX2004/8 | ASP | SQL | XHTML/CSS | Web Accessibility ] </font id=size1>

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Hi Ozzii

Here's the script I'm using. You'll need to modify it for your own situation but it should give you a starting point at least.
Hope it helps!

<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Hi simon thanks for the script. I actually have a very similar script however it simply times out due to the
sheer number of alerts it needs to send. Also how do u schedule the above script with WHS?

totally vote for you, thanks for the post.

Software
www.popsoftware.net/

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Does anyone know how I can produce an email alert system that notifies subscribers of udates to the site, something similar autotrader.co.uk. FOr example, if I want to buy a used Ford Focus, I can set up an email alert when one comes up for sale.

Cheers

DMX | ASP | VBScript | ACCESS | IIS5

Edited by - sesame on 21 Jun 2004 18:00:07
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
.hey, thanks, this post is very useful

----------------------------------------------

www.download.com/Bingo-iPod-Converter-Suite/3000-2194_4-10679677.html