e-mail list in Access 2000

[Kungen]

Hi!
I have a column with e-mail addresses. Some of the rows are NULL.
What can I do to retreive all the addresses in one single line,
separated with semicolons?
I know how to use calculated fields (like this + ' ; ' + that etc) but
how do I write a query that "knows" how many addresses there are, omits

NULL values and puts it all in one nice row?

Note that I have only one table, and only one column of data to be
retrieved. No relations whatsoever. Just this single column who's
content I need to get in a single row, separated by semicolons.

I'm not very knowledgeble, so I'd really appreciate if someone could
write a snippet of code for me.
I tried to excerpt what I needed from
http://www.mvps.org/access/modules/mdl0004.htm but I just couldn't do
it.

Kungen

 

[Guest]

Hi Kungen,

Try this function. It will produce a concatenated list of ContactNames in
the Northwind sample database (Northwind.mdb). To use it in your database,
change the field referenced, ContactName, to the name of your field, and
change the table referenced, Customers, to the name of your table.


Option Compare Database
Option Explicit

Function BulkEmail() As String
On Error GoTo ProcError

'Purpose: Return a string containing all the email addresses to mail to.
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOut As String
Dim lngLen As Long
Const conSEP = ";"

strSQL = "SELECT ContactName FROM Customers WHERE ContactName Is Not Null;"
Set DB = CurrentDb
Set rs = DB.OpenRecordset(strSQL)

With rs
Do While Not .EOF
strOut = strOut & ![ContactName] & conSEP
.MoveNext
Loop
End With

lngLen = Len(strOut) - Len(conSEP)

If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
End If

Debug.Print BulkEmail

ExitProc:
On Error Resume Next
rs.Close: Set rs = Nothing
DB.Close: Set DB = Nothing
Exit Function

ProcError:
MsgBox Err.Number & ": " & Err.Description, _
vbCritical, "Error in BulkEmail function..."
Resume ExitProc
End Function



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[John Vinson]

Try this function.

Thanks Tom!! Very clear and straightforward.

John W. Vinson[MVP]

 

[Kungen]

Thanks Tom!

Sorry for delay, been away a few days. Now I've tried your script on
Northwind but all I get is an error message. I guess I'm just not
educated enough to understand what to do with your script. It does not
start with a valid SQL expression and I guess I'm supposed to embed it
within a script that does. I don't know exactly how to do that.
(Dont shoot me)

Kungen

 

[Guest]

Hi Kungen,

but all I get is an error message.

Okay, describe exactly how you tried the procedure out. What did you do?
What error message did you receive?

Here is what you should have done:
1.) Create a new module. Select the Modules tab. Click on the New button.
You should see two lines of code:

Option Compare Database
Option Explicit

If you do not see the second line of code, then type it in. Go correct this
problem right now. See the following article:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Save the module as "basConcatentate" (without the quotes). Copy the code
that I provided before (Ctrl C, after selecting it), and then paste it into
your new module below the second line.

Still in the VBE (Visual Basic Editor), click on Tools > References. Verify
that you have a reference set to the "Microsoft DAO 3.6 Object Library". If
not, scroll down the list until you find it. If you are using Access 97, then
you will want to select the "Microsoft DAO 3.5 Object Library" instead.

Change the names of the table and field, as I indicated before, to match
your table and field names, for the field that contains the e-mail addresses.
Make sure to use the actual name of the field; do not use a captioned name.
If your field and/or table names include any special characters, reserved
words, or spaces, then you will need to enclose them in square brackets.
Hopefully, you will have avoided such naming convention mistakes.

Click on Debug > Compile ProjectName where ProjectName is the name of
your VBA project, which is likely the same as the name of your database.
Hopefully, your code will compile without any errors. If the option appears
"greyed out" (unavailable) after compiling, then this means that your code
compiled without any errors.

Still in the VBE, press the Control and G keys at the same time (ie. Ctrl
G). You should see the Immediate Window. Copy the following command and paste
it into the Immediate Window. Then press the Enter key:

BulkEmail


In this case, you should see a concatenated list of ContactName data, since
the SQL (Structured Query Language) statement is written for the Northwind
database.

Now, I'll only shoot you if you report back "It doesn't work!", without
telling me what errors you got. Fair deal? <smile>


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Kungen]

IT WORKS!!!

:O)

Kungen

 

[Kungen]

Follow-up question:
Can this be called from a SQL-query?
And be presented in a view consisting of one row, one column?

Kungen