Report with email addresses

D

Dylan Moran

Hi,

I want to create a report with all my clients email addresses, with each
separated by a semi colon. I then want to be able to "Analyze it with Word"
so that I can copy and paste all the email addresses (that are separated by a
semi colon into Outlook.

All is cool to do this, BUT, each record that selects the email addresses is
spearated by a carriage return (Paragraph Mark). I want this not to be the
case4. Therefore I want to produce a report that takes all the email
addresses (Email) from my table (tblCustomer) and produces a report in one
section that essentially has the following:

[email protected]; email@client2; [email protected] etc

NOT

[email protected];
email@client2;
[email protected]
etc

Any assistance would be most appreciated.
 
6

'69 Camaro

Hi, Dylan.
I want to create a report with all my clients email addresses, with each
separated by a semi colon. I then want to be able to "Analyze it with Word"
so that I can copy and paste all the email addresses (that are separated by a
semi colon into Outlook.
Click to expand...

If the end result is the important part, not the interim report and Word
document, then just create a new module, and paste the code from the
following Web page into it:

http://www.mvps.org/access/modules/mdl0008.htm

Remove the data type parameter in the function and the WHERE clause
criteria, including the SELECT CASE block, in the VBA code. Compile the code
and save the module. (You could rename the function to something like
fConcatRecords, since it's no longer concatenating fields, but the name of
the function is used within the code, so don't forget to change that line of
code, too.) Name the module anything except the name of this function.
Create a new query and use this function in the query for a calculated value.
For example:

SELECT fConcatRecords("tblCustomer","ContactName","EMail", [ContactName]) AS
Addresses
FROM tblCustomer
GROUP BY fConcatRecords("tblCustomer","ContactName","EMail", [ContactName]);

Run the query and copy/paste the calculated value for "Addresses" that
contains all the E-mail addresses in the table into your new Outlook E-mail
To: line.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Top