Sending a group Email from Access

[JohnB]

Hi.

With help from Doug Steele and David Cox, I've managed to set things up so
that users can, within Access, filter down a set of records, then enter a
subject and body text and click on a command button which automatically sends
a common Email to that set.

However, now that I've got this working, I realise that it takes the form of
a seperate Email to each recipient and means that users have to 'allow' each
Email to be sent, which is tedious and probably would not be acceptable.

Is there a way transfer the recipients addresses to Outlooks 'To' box in a
block, or to otherwise build up the recipients list in Outlook prior to
sending, so that they all get sent the same Email in one send operation? Even
if this could be done, is there a maximun number of Email Addresses that
could be transferred to Outlooks 'To' box?

I'm using Access2000 on XT, networked and I've set things up based on this
Microsoft article:

http://support.microsoft.com/?id=318881#appliesto

Thanks for any help

 

[BruceS]

John,

Cycle through the "recipient" records and append the email address, plus a
semi-colon, to a string variable. When finished, remove the final
semi-colon, then use that variable for the "To" parameter. (Use BCC if you
don't want to reveal all of the addresses to all recipients.)

I'm sure that there's some kind of limit on Recipients, but I don't do that
many recipients at one time, so I've never hit it.

Bruce

 

[JohnB]

Thanks Bruce.

I do have a concatenate program elsewhere in my mdb and I should be able to
incorporate this into the query supplying the Email code one but there is a
limit of 255 characters on a queries concatenated field.

I just wondered if there is another way of creating a block of recipients in
Outlook that could avoid that limit. I'm sure I read somewhere a post about
someone sending an email to hundreds of recipients and this couldn't have
been individual Emails.

I'll do some more searching but can anyone else suggest anything?

Thanks.

 

[BruceS]

John,

I've never had a problem building very long strings (well over 255) into a
user-defined variable. For example:

Dim myStr as String
Dim x as Integer
myStr = ""
For x = 1 to 1000
myStr = myStr & "abcdef"
Next x

runs fine. (Try it.)

I actually build my delivery string in the above manner, then store it in a
memo field in a table I use to record the emails sent. (Note "Memo", not
"Text" field.)

Good luck,
Bruce

 

[Duane Hookom]

There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
will return more than 255 characters.

 

[JohnB]

Thanks Duane and Bruce.

That looks like exactly the sort of solution I want, provided it ends up
with one Email with a group of addresses in it's 'To' window, rather than
sending the same EMail, one at a time, to a set of recipients.

My current method is to start with a set of records and allow the user to
filter that down using a parameter query. Hopefully I will be able to bolt
this method on to the code in the sample mdb you've pointed me towards. I
will repost if I hit problems.

Thanks again both.

 

[JohnB]

Hi.

Just thought I'd say that I've got the suggested code working and not
involving a 'make table' query. It does exactly what I want, although it is a
bit slow to bring up the Outlook page. That's probably the fault of the slow
network here, rather than the code.

Many thanks for the pointer.