Email Multiple Recipients

[LDMueller]

I have a query named "qryEmailList" which consists of two related tables as
follows:
INVENTOR.InvName
INVENTOR.Email
INVENTORLIST.InvMtr

My code is as follows. This code runs a query named "qryEmailList" and
pulls the email address from each record so I can email the list. All works
fine, however, I need to add criteria to this query based on Inventor number.
If I put a specific Inventor number in the criteria field of the query
itself, all works. If I put the prompt "Like "*" & [Enter Inventor Number]"
in the criteria field of the query itself it doesn't work and I need it to
prompt so I can enter the Inventor Number.

Based on this I assume I need to be able to add VBA code which puts the
criteria prompt "Like "*" & [Enter Inventor Number]" in the field
INVENTORLIST.InvMtr

Private Sub cmdEmailAll_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sAddress As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select Email From qryEmailList")
With rs
If (Not .BOF) And (Not .EOF) Then
.MoveFirst
sAddress = .Fields("Email")
.MoveNext
End If
If (Not .BOF) And (Not .EOF) Then
Do Until .EOF
sAddress = sAddress & "; " & .Fields("Email")
.MoveNext
Loop
End If
.Close
End With
DoCmd.SendObject , , acFormatTXT, sAddress, , , , , -1
End Sub

If anyone can help me with this or give me advise on going in another
direction I would sincerely appreciate it.

Thanks,

LDMueller

 

[Rick Brandt]

I have a query named "qryEmailList" which consists of two related tables as
follows:
INVENTOR.InvName
INVENTOR.Email
INVENTORLIST.InvMtr

My code is as follows. This code runs a query named "qryEmailList" and
pulls the email address from each record so I can email the list. All works
fine, however, I need to add criteria to this query based on Inventor number.
If I put a specific Inventor number in the criteria field of the query
itself, all works. If I put the prompt "Like "*" & [Enter Inventor Number]"
in the criteria field of the query itself it doesn't work and I need it to
prompt so I can enter the Inventor Number.

Please clarify "doesn't work". Do you get an error? Too many records? Too few
records?

Is InvMtr in fact a number? If so then you cannot reliably use "Like" as that
is for string comparisons. If it is a text field then what you have is fine so
long as you are only looking for matches at the end of the string. If you want
to match any part of the string then you need another asterisk on the right hand
side.

 

[LDMueller]

I'm sorry, when I indicated doesn't work I get the error message "Run-time
error '3061': Too few parameters. Expected 1".

The InvMtr is a number like ABCD1234. The ABCD part is always the same,
that's why I wrote it as "Like "*" & [Enter Inventor Number]" so my user
wouldn't have to enter the ABCD part. But even if the prompt was just [Enter
Inventor Number] and the user input ABCD1234, the outcome is still the same
(e.g. I get the above error).

I have a query named "qryEmailList" which consists of two related tables as
follows:
INVENTOR.InvName
INVENTOR.Email
INVENTORLIST.InvMtr

My code is as follows. This code runs a query named "qryEmailList" and
pulls the email address from each record so I can email the list. All works
fine, however, I need to add criteria to this query based on Inventor number.
If I put a specific Inventor number in the criteria field of the query
itself, all works. If I put the prompt "Like "*" & [Enter Inventor Number]"
in the criteria field of the query itself it doesn't work and I need it to
prompt so I can enter the Inventor Number.

Please clarify "doesn't work". Do you get an error? Too many records? Too few
records?

Is InvMtr in fact a number? If so then you cannot reliably use "Like" as that
is for string comparisons. If it is a text field then what you have is fine so
long as you are only looking for matches at the end of the string. If you want
to match any part of the string then you need another asterisk on the right hand
side.