combine email addresses to one field

[michbla490]

I have a customer table with field listing their email address. I'm trying
to find the best way to combine all those email addresses, separated by
commas, to one field so I can cut and paste for a mass email mailing. Is
there an easy way to do this using an update query? Thanks so much in
advance!

 

[Lou]

I have a customer table with field listing their email address.  I'm trying
to find the best way to combine all those email addresses, separated by
commas, to one field so I can cut and paste for a mass email mailing.  Is
there an easy way to do this using an update query?  Thanks so much in
advance!

Please consider the alternative of sending one email at a time.

There are two reason I suggest this. First, email receivers divert
mass mailings to a "Junk Mail" box. Second, some email client
programs limit the number of addressees.

 

[Tom Wickerath]

I suggest separating the e-mail addresses by semicolons ( rather than
commas. The reason is that commas can fail in Outlook, unless one has an
option configured correctly to allow commas as address separators. In Outlook
2003, this option is found here:

Tools | Options... | E-mail Options...(on Preferences tab) |
Advanced E-mail Options... ---> "Allow comma as address separator" (shown in
the lower section).

Also, if you are going to send to multiple receipients, you must enter their
e-mail addresses *only* on the BCC (blind copy) line, unless you want to piss
off some of your customers for exposing their e-mail addresses to other
people. In addition, you'll need to first determine how many e-mail addresses
that your ISP allows per message, and you'll likely need to enter at least
one valid e-mail address (your's) on the To: line (otherwise, many other
configurations will automatically flag the message as spam, without an
address in the To: field). Another problem with this strategy is that often
times a message will not leave your Outbox, if there is even one invalid
e-mail address in the BCC line (this is how my ISP, Comcast, works). Given
that rate at which e-mail addresses come and go for people, this can be a
headache.

Some alternatives that will allow you to easily send one message at a time,
as Lou suggests, include:

Total Access E-Mailer by FMS
http://www.fmsinc.com/products/Emailer/Access_Emailer_2007.asp

and
Worldcast by Fairlogic (free only for non-commercial use)
http://www.fairlogic.com/worldcast/

Or, you can roll your own code. But, these two products include lots of nice
features that you would be hard-pressed to duplicate.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

PS. I forgot to include a function to concatenate e-mail addresses into one
string. Here it is. You will need to make the appropriate changes in the
field names and table name:

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 [EMailAddress] FROM [Customers] " _
& "WHERE [EMailAddress] Is Not Null;"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

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

lngLen = Len(strOut) - Len(conSEP)

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

' Debug.Print BulkEmail '(<--uncomment line to see results in Immed. Window).

ExitProc:
If Not rs Is Nothing = True Then
rs.Close: Set rs = Nothing
End If
Set db = Nothing
Exit Function

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

Function SendEmail()
On Error GoTo ProcError

DoCmd.SendObject _
To:="YourEmailAddressHere", _
BCC:=BulkEmail, _
Subject:="Enter your subject here", _
MessageText:="Enter your text here", _
EditMessage:=True

ExitProc:
Exit Function

ProcError:
Select Case Err.Number
'User cancelled message (2293 & 2296 are raised by Outlook, not Outlook
Express).
Case 2501, 2293, 2296
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure SendEMail..."
End Select
Resume ExitProc
End Function

 

[michbla490]

Tom,
Thank you for your expert answer! Actually my database is very small and
the most email addresses I'm trying to combine is less than 50. My
"customers" are actually members of a non-profit group. Isn't there a quick
query I could write to combine all the email addresses in this field to one
separated by commas, or semi-colons? Thank you again.

 

[Tom Wickerath]

You may need to send out two messages instead of just one, depending on the
limits imposed by your ISP. I *think* my ISP, Comcast, has a limit of 40
e-mail addresses, but I could be wrong (it's been a long time since I've had
a need to do this).

Create a query that selects all non-null E-mail addresses. You can then use
the copy and paste technique to copy records from the query and paste into
the BCC line of a new message. There is no need to attempt to get them all
into one string in this case; Outlook and Outlook Express are capable of
resolving the e-mail messages.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Fred]

Thunderbird is the reverse, it doesn't like semicolons but commas work.

A low tech way to do what you ask is is export only the email to a delimited
text document, and use global find/replace in Word to replace the returns
with commas or semicolons

 

[michbla490]

Perfect! That's what I was looking for! When we get bigger and progress, I
will certainly use the other excellent alternative. Much thanks.