Extracting a list from a query

[Ali Edwards]

I have a query with a list of email addresses in it, one per record. Is there a way of getting these into a list, each one separated by a comma, so that I can copy and paste it into an email? The list could be a form with one large unbound field to temporarily place the email addresses in until I have copied and pasted into the email.

Up until now I had a very clunky method which worked around the sendkeys method.
It opened the query, copied the first record, opened a form (with just one large unbound field), pasted, reopened the query, copied the second record, reopened the form, pasted a comma followed by the second record etc etc. It used to work but for some reason it is now failing me (since changing to Windows 2000). In any case I always new there should be a better way but I still can't find one!

Any help appreciated.

Thanks
Ali

 

[Fornatian]

Ali,

Try this for a simple solution:

Say your field is , build a calculated column in the query which adds a "," on to each field. Like:

NewEmail: [Email]&","

Run the Query and you'll get a list like:

Name1,
Name2,
Name3,

If you then copy the column you can paste it into the To field of your email.

I know there is another method using recordset objects and string variables but it may but hammers and nutshells for what you want.

Let me know if this suggestion meets your needs.

Ian

 

[Ali Edwards]

Ian,

That kind of does the trick, thank you so much.
Now that I have a list with each email separated by a comma I'd like to try and work out how I could paste that so that it isn't a long column (there are 150 email addresses) but rather an email, next email, next email, etc.

I'm sure there is a way of doing it with a recordset and 'do until rst end', but heck knows how!

Many thanks for your help.

Ali

 

[Pat Hartman]

Look up this article for an idea:

Q141624 - ACC How to Concatenate a List of Items from a Many-Side Table

 

[Ali Edwards]

Pat - thanks very much for that! Must admit I was pretty terrified when I first read that code but I've managed to adapt it to my needs without any kind of nervous breakdown.

Thanks again.

Ali