Mutliple Email Recipients from Access Form 1

[nq]

I have a form that displays data about objects. Each object has an email address associated with it. I have a button that can send an email to the address shown on the current record. The following code does this:

varEmail = Me!Premail
DoCmd.SendObject acSendNoObject, , , varEmail

I also have a need to send a bulk email to every email address from every record. I can create a query to extract the email addresses from the table. At this point I could export to a Word Mail merge document etc. However, I would prefer the user to click a button to open a blank email with all email addresses loaded in the TO or BCC fields from the query. The user would then type the subject and message and then Send.
It would seem that I need to read the query results into an array or direct into a variable (varEmail)

I would prefer to use the SendObject command since it is MAPI compliant and some users are using Thunderbird or Eudora instead of Outlook.

nq

 

[Remou]

Hi
This worked with 77 records (I do not know maximum length that can be used):

Dim varEmail
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.MoveFirst
Do While Not rs.EOF()
    If Me!Email & "" <> "" Then
        varEmail = varEmail & ";" & rs!Email
    End If
    rs.MoveNext
Loop
varEmail = Mid(varEmail, 2)
DoCmd.SendObject acSendNoObject, , , varEmail

 

[nq]

Remou.
Thanks for the code. Yes, this is what I wanted to do and it partially works. I changed it a bit:

varEmail = varEmail & "; " & rs!Pemail - a space required "; "
varEmail = Mid(varEmail, 3) - removes the space at the start

At this point it did not pass any data to Outlook and returned the following error: "Unknown message recipient(s)"

I put an unbound text box on the form and dumped the addresses to check: Me.Address = varEmail

All the addresses were displayed in the box, so the code works. For some reason the SendObject command is not passing the data. Any ideas? There are 156 addresses - I don't know if there is a size limit.

nq

 

[Remou]

Here is the problem:

If Me!Email & "" <> "" Then

Should read:

If [red]rs[/red]!Email & "" <> "" Then

 

[nq]

Remou.
Thanks for that. It works.
I found that two addresses in the table were blank. This is what caused the problem. If I put a dummy email address in the blanks, it worked. However, using your fix it worked even with blank email addresses. Thanks.

nq