Mulitple Address email

[Les]

I have a query which produces a list of email addresses. Can anyone give me
a simple way to send these to Outlook for group email.

Thanks

Les

 

[Jonathan Parminter]

-----Original Message-----
I have a query which produces a list of email addresses. Can anyone give me
a simple way to send these to Outlook for group email.

Thanks

Les

Hi Les,

use online help to lookup docmd.sendobject

Luck
Jonathan

 

[ScottE]

Here's a slick way to do it, which avoids OL security
prompts and does not require referencing the OL libraries:

First, iterate through your query results. Take each
email address and add it and a semicolon to a string
variable:
strEddresses = strEddresses & {email} & ";"

Then generate the email with:
Application.FollowHyperlink ("mailto:" & strEddresses)

Hope this helps!

- Scott

 

[Les]

Scott

Thanks for this idea looks just what I need Im happy with the result of the
strEddress idea and using the FollowHyperlink command but Im not sure where
I would create the string variable, and how to get a vertical output into
the horizontal string. Are you suggesting this is in a form?

Les

 

[ScottE]

I suggest doing it in code, which you can call with a
button on a formor from a custom menu. To get
the "vertical" query results into "horizontal" format,
what you need to do is open the query (aka recordset0 in
code and iterate through it. Try the following:

Sub GenerateList ()
dim rstRecips as object
dim strEddresses as string

strEddresses = ""

set rstRecips = currentdb.openrecordset("{query name}")
if rstRecips.BOF then 'BOF=no results from query
msgbox "Query produced no results."
else
rstRecips.Movefirst
do
strEddresses = strEddresses & _
rstRecips.{email field} & ";"
rstRecips.movenext
Loop until rstRecips.EOF
Application.FollowHyperlink ("mailto:" & strEddresses)
end if

rstRecips.close
set rstRecips = nothing

end sub

Hope this helps!

- Scott

 

[Les]

Scott thanks for this.

I have completed the code as you suggest but get a Runtime (87) error on the
Mailto section. If I hover the mouse over the & strEddresses it shows all
the email addresses its holding so its picking them up but will not open
outlook. I have the same command in another form which takes the value from
a text box and this works fine. Any Ideas?

Les

 

[Les]

Scott

Having investigated further it seems to be restricted by the number of email
adresses generated. If I get over 50 it falls over. Any Ideas?

Les

 

[ScottE]

Interesting. I tested the code, and the problem appears
to be related to the length of the string eddresses, not
the exact number of recipients: running the base query
with different parameters and maxing the query results at
43 records resulted in a string length of 1009 one way
(followhyperlink worked), and a length of 1030 another way
(f/h did not work). Splitting the string into two parts
didn't help.

The first responder to your thread had it right: use
docmd.sendobject. I tried the same code, but switched the
line

Application.FollowHyperlink ("mailto:" & strEddresses)

to

DoCmd.SendObject , , , strEddresses

and it handled 333 recipients (length of eddresses was

7700) without a problem. However, sendobject errors if

the user cancels (err 2501), so you need to trap that
error. Also, on the machine I'm on right now (with OL
perhaps not fully configured because I don't use it to
send mail), sendobject or OL pops an annoying dialog
asking you for the profile name.

Sorry to have led you down the wrong path!

- Scott

 

[Les]

Scott

Thanks for the help I have used the SendObject with error checking and thats
fine now.

Les

 

[ScottE]

Excellent. Oddly, I ran into the same problem yesterday
with maxing out the followhyperlink method. I used the
sendobject solution. What I don't like about S/O is that
it essentially puts the draft email up in dialog mode for
both Outlook and Access, making it impossible to drag
atttachments from other emails, etc. The F/H method does
not have this failing. My solution was to test len
(strEddresses). If it's < 1000, use F/H, otherwise use
S/O. Out of paranoia, I trapped for the error we were
getting in F/H, and if that error happens, I have the code
use S/O. Thanks for the discussion - it was very useful!

- Scott