Emailing search results

[Dutchguy]

I have a SQL statement that returns the following list:

Email | Client_ID | Search | Description
test@test.com | 1 | chevrolet | chevrolet, in new condition
test@test.com | 1 | chrysler | very nice chrysler, $ 500
mail@mail.com | 2 | chrysler | good chrysler as new

Email = e-mail address
Clienst_ID = ID of the client
Search = Keyword that a client entered in the database to search for
Description = The result from the database based on the keyword.

These are matching search results for all clients. The amount of matching results will always vary, just like the number of clients. Sometimes there won't be any matches at all.

Now I would like to achieve the following:

Send an e-mail to all clients with the right search results. In this case it would be:

One e-mail to test@test.com, with two search results in the e-mail. It would have to show the 2 description fields:
- chevrolet, in new condition
- very nice chrysler, $ 500

One e-mail to mail@mail.com with one search result in the e-mail. It would have to show one desciption field:
- good chrysler as new

Any ideas ? I will be using CDONTS.

 

[sodakotahusker]

Try this:
I used variables dataline1 and dataline2 for your different descriptions. You would want to use the stringbuilder to concatenate more strings than that - which is what it sounds like you need to do.


Public Function SendEmail(ByVal m_recipient As String, ByVal m_password As String) As String

Dim iLoop As Integer
Dim outlmail As New CDO.Message
Dim m_subject As String
Dim m_sender As String
Dim m_text As String

On Error GoTo EmailError
m_text = dataline1 & chr(13) & chr(10) & dataline2

m_subject = "Whatever you want"
On Error GoTo EmailError

outlmail.Subject = m_subject

outlmail.From = "youraddress@provider.com"
outlmail.To = m_recipient
outlmail.TextBody = m_text

outlmail.Send()

outlmail = Nothing
SendEmail = "0"


Exit Function


EmailError:

SendEmail = Err.Description

Exit Function



End Function

End Class

 

[sodakotahusker]

Forget my comment about the stringbuilder. I thought I was on the ASP.NET forum!

 

[Dutchguy]

I don't think this is what I mean.

The number of client's and search results will always be different.

 

[Dutchguy]

At this point I have the following code:

Set rs = Server.CreateObject("ADODB.RECORDSET")
rs.Open "Select * from Matchen where description Like '%'+Zoekopdracht+'%'", Conn
body = body & "Your search results:" & "<br>" & "<br>"

While NOT rs.EOF
body = ""
body = body & rs("description") & "<br>"

response.write "To: " & rs("Email") & "<br>"
response.write "Results: " & body & "---------------------------------" & "<br>"
recipient = rs("Email")

rs.MoveNext
Wend


I wrote it like this for easier debugging, instead of emailing it at this moment.

With this code it displays all search results:

To: test@test.com
Results: chevrolet, in new condition
----------------------------------

To: test@test.com
Results: very nice chrysler, $ 500
----------------------------------

To: mail@mail.com
Results: good chrysler as new
----------------------------------

I just can't figure out how to 'merge' the search results for each user, it has to display:

To: test@test.com
Results: chevrolet, in new condition
Results: very nice chrysler, $ 500
----------------------------------

To: mail@mail.com
Results: good chrysler as new
----------------------------------

 

[Tarwn]

basically the only thing you need to add i a variable to keep track of the previous records value for the user field. This will allow you to conditionally add everything back in OR just add the item description, something like this:

Dim last_user 
While NOT rs.EOF
   If last_user <> rs("Email") Then
      'if we had a previous user, output there info (or mail it)
      If Len(last_user) > 0 Then
         response.write "To: " & rs("Email") & "<br>"
         response.write "Results: " & body & "---------------------------------" & "<br>"
      End If

      'start this next user's output
      body = ""
      body = body & rs("description") & "<br>"

      'update out last_user variable
      last_user = rs("Email")
   Else
      'user hasn't changed, continue adding to their results
      body = body & rs("description") & "<br>"
   End if
rs.MoveNext
Wend

basically we have moved things around a little, but now the logic reads as:

For Each Record
   If our current record's user doesn't match the previous record or there was no previous record (last_user empty) Then
      If there was a previous user/record, output their information that we built - this is where you would send the email
      Start building a new body string for this user
   Else (our user is the same as the one in the previous record)
      Just add on the next addition to search results

HOpe this helps,

-T

 

[Dutchguy]

Unfortunately not working, It now displays one result, and the wrong result for the wrong person.

I get your point though, but it's not working out yet.

 

[Dutchguy]

Have been playing with it all evening, but no luck. Anyone can help with this ?