Email to lots of people from a query/form

[HansD]

Hello people

This is driving me MAD!!!, I have read hundreds of topics in this forum and still Ican't get it to work
I Want to sent a e-mail to adresses from a query using a selection on a form. I have a lot of names
in a table and each name has one or more selection codes (like: school or theater). In a form (Frmzoeken) i have a listbox with the selection codes in it
, so when i select school i see all the schools on a subform in a query (Qryfrmzoeken):



SELECT TblAdressen.AdresID, TblAdressen.Naam, TblAdressen.BezoekStraat, TblAdressen.BezoekPostcode, TblAdressen.BezoekPlaats, TblAdressen.PostStraat, TblAdressen.PostPostcode, TblAdressen.Postplaats, TblAdressen.Email, TblAdressen.Selectiecode, TblAdressen.Trefwoorden
FROM TblAdressen
WHERE (((TblAdressen.Selectiecode) Like "*" & [forms]![Frmzoeken]![txtselectiecode] & "*&quot) OR ((([Forms]![Frmzoeken]![txtselectiecode]) Is Null));



This all works great, i see the selection in the query, etc.

BUT:
I want to have a button on my form that opens outlook XP with all the addresses in BCC and my address in TO

I've tried this code i found here:


Private Sub Command0_Click()

Dim MyEmailList As String

MyEmailList = ColumnToLine("Qryfrmzoeken", "Email&quot

DoCmd.SendObject , , , "me@me.nl", , MyEmailList, "Test Message", , True

End Sub


Copy the following code into a new module for the above to work

Public Function ColumnToLine(TblQueryName, ColumnName)
On Error GoTo ErrInFunction
Dim myRs As DAO.Recordset
Dim ResultString As String
Set myRs = CurrentDb.OpenRecordset(TblQueryName)
If myRs.RecordCount > 0 Then
Do Until myRs.EOF
ColumnToLine = ColumnToLine & IIf(Len(ColumnToLine) = 0, "", "; &quot & myRs(ColumnName)
myRs.MoveNext
Loop
End If
FinishPoint:
On Error Resume Next
'MsgBox ColumnToLine
myRs.Close
Exit Function
ErrInFunction:
ColumnToLine = "Error: " & Err.Description
Resume FinishPoint
Exit Function
End Function

This code works great without my selection, but with it doesn't work

I hope somebody can help me, because i am realy missing something but i don't see it anymore


Thanks

 

[lameid]

The If statement is completely unnecessary...
If myRs.RecordCount > 0 Then

Remove it and the coresponding end if...

I assume you know that you need to make the appropriate substitutions for your database as far a query, table and field names.

 

[HansD]

Yeah, i know about the changes. As i said The email works perfect, when i have, say three addressess it works great. But when i put in a selection in the query:
Like "*" & [forms]![Frmzoeken]![txtselectiecode] & "*" OR [Forms]![Frmzoeken]![txtselectiecode]) Is Null

I get an error about that there are no addresses. When i look in the query the selection was made perfectly. but somehow VB doesn't see my selection

Hans

 

[lameid]

So the query works and is saved prior to running the code but you are getting undesired results... (If the query isn't saved, I'm not sure what it would do).

You might also try passing a literal SQL string to your procedure.

One other comment... If you pass the sql string instead of the query name, you can build an sql statement that does not have a goofy where clause to accomodate null values. Also, I find it is always best to place null comparisons in queries before testing for a value... I've seen some strange things.

 

[HansD]

Yeah the query works but i am not sure that it is saved (i don't know how to do it) Because when i press the button "e mail" outlook starts, but when i press send i get the error :"too few parameters. Expected 1". So i don't think it is saved. Do you know how to do that?

Thanks

 

[lameid]

Ahh an error message... I forgot about Recordset's thinking every parameter must be passed. If you use the a literal sql statement, it will work.

Otherwise you need to use a querdef object set it to the query, specify the parameter, and use the openrecordset method of the querydef. (In my opinion the literal SQL is less work, but maybe not if you can't write SQL cold). Only disadvantage is that SQL in code like that are compiled each time at execution. But an exact statement my execute fast enough to compensate for the compile time (you probably won't notice it).

 

[HansD]

how do you mean the literal sql statement?
could you give a example of a code, or something.
I really don't know how to write a code that saves or "remembers" the selection

Hans

 

[lameid]

Private Sub Command0_Click()

Dim MyEmailList As String
dim strSQL As String

strSQL = "SELECT TblAdressen.AdresID, blAdressen.Naam, TblAdressen.BezoekStraat, " & _ "TblAdressen.BezoekPostcode, TblAdressen.BezoekPlaats, TblAdressen.PostStraat, " & _
"TblAdressen.PostPostcode, TblAdressen.Postplaats, " & _ "TblAdressen.Email, TblAdressen.Selectiecode, TblAdressen.Trefwoorden " & _
"FROM TblAdressen "

IF Isnull([Forms]![Frmzoeken]![txtselectiecode]) = true
Then
Else
strSQL = strSQL & "WHERE ((TblAdressen.Selectiecode) " & _
"Like ""*" & [forms]![Frmzoeken]![txtselectiecode] & "*"&quot)"
End IF
strSQL = strSQL & ";"

MyEmailList = ColumnToLine(strSQL, "Email&quot

DoCmd.SendObject , , , "me@me.nl", , MyEmailList, "Test Message", , True

End Sub

 

[lameid]

I let the wrapping text make an error in CR in the code

IF Isnull([Forms]![Frmzoeken]![txtselectiecode]) = true
Then

The above should be all on one line and not two.

IF Isnull([Forms]![Frmzoeken]![txtselectiecode]) = true Then

 

[HansD]

Oh man thanks. I am going to try it tommorow. It's 0:30 here and i drunk a little bit to much beer.

Really thanks, i will let you know if it worked

Hans

 

[HansD]

Hey

I have it now like this:

Private Sub CmdEmail_Click()
Dim MyEmailList As String
Dim strSQL As String

strSQL = "SELECT TblAdressen.AdresID, TblAdressen.Naam, TblAdressen.BezoekStraat, " & _
"TblAdressen.BezoekPostcode, TblAdressen.BezoekPlaats, TblAdressen.PostStraat, " & _
"TblAdressen.PostPostcode, TblAdressen.Postplaats, " & _
"TblAdressen.Email, TblAdressen.Selectiecode, TblAdressen.Trefwoorden " & _
"FROM TblAdressen "

If IsNull([Forms]![Frmzoeken]![Txtselectiecode]) = True Then
Else
strSQL = strSQL & "WHERE ((TblAdressen.Selectiecode) " & _
"Like ""*" & [Forms]![Frmzoeken]![Txtselectiecode] & "*"&quot)"
End If
strSQL = strSQL & ";"

MyEmailList = ColumnToLine(strSQL, "Email&quot

DoCmd.SendObject , , , "me@me.nl", , MyEmailList, "Test Message", , True

End Sub



But within outlook i get the message:

Error: Extra ) in query expression '((TblAdressen.Selectiecode) Like "*Nieuwsbrief*&quot)'.

So I think this is really going to work but somewhere there is a &quot" missing

Hans

 

[lameid]

Private Sub CmdEmail_Click()
Dim MyEmailList As String
Dim strSQL As String

strSQL = "SELECT TblAdressen.AdresID, TblAdressen.Naam, TblAdressen.BezoekStraat, " & _
"TblAdressen.BezoekPostcode, TblAdressen.BezoekPlaats, TblAdressen.PostStraat, " & _
"TblAdressen.PostPostcode, TblAdressen.Postplaats, " & _
"TblAdressen.Email, TblAdressen.Selectiecode, TblAdressen.Trefwoorden " & _
"FROM TblAdressen "

If IsNull([Forms]![Frmzoeken]![Txtselectiecode]) = True Then
Else
strSQL = strSQL & "WHERE ((TblAdressen.Selectiecode) " & _
"Like ""*" & [Forms]![Frmzoeken]![Txtselectiecode] & "*"&quot"
End If
strSQL = strSQL & ";"

MyEmailList = ColumnToLine(strSQL, "Email&quot

DoCmd.SendObject , , , "me@me.nl", , MyEmailList, "Test Message", , True

End Sub

You had an extra &quot" in the like statemnet. Just like I wrote it

 

[HansD]

Lameid, i want to really thank you.

It is working great.
Thanks for learning me a new aspect of access

Thanks man

Hans