Concatenate email addresses

[Bonnie A]

Hi everyone. Using A02 on XP. I know this is possible but I just cannot
find it. I have searched all through here. Most of the concatenate posts do
not apply to what I need. The link to Roger's Library also does not. They

I need to create a text string from ALL RECORDS IN ONE FIELD so I can send a
group email.

My table has a field [EmailAddr], I have over 200 records and would like to
get this:

(e-mail address removed),[email protected],[email protected],[email protected],[email protected],etc.

I know it's an easy one but cannot figure it out. The best I can find is
this mention about the article in Roger's Library from John Nurick to Eli the
Iceman back on May of 2006:

"That concatenates values in multiple fields from the current record;
Eli's question implied concatenating values in one field of multiple
records. "

The post did not answer the question though. It only identified it. But I
need the answer.

Can someone help?

Thank you in advance for your time and advice!

Bonnie

 

[NuBie via AccessMonster.com]

You need to loop thru all records and get each email address and then
CONCATENATE :
This is how i do it:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select emailadd from yourTable")
If rst.RecordCount = 0 Then
MsgBox "No Record In the Table"
rst.Close
Set rst = Nothing
Set db = Nothing
Else
With rst

.MoveFirst
Do While Not .EOF
emailadd = emailadd & !emailadd & ", "
.MoveNext
Loop
.Close
End With
End If

Set rst = Nothing
Set db = Nothing


NOTE: remove the extra comma ","

Hi everyone. Using A02 on XP. I know this is possible but I just cannot
find it. I have searched all through here. Most of the concatenate posts do
not apply to what I need. The link to Roger's Library also does not. They

I need to create a text string from ALL RECORDS IN ONE FIELD so I can send a
group email.

My table has a field [EmailAddr], I have over 200 records and would like to
get this:

(e-mail address removed),[email protected],[email protected],[email protected],[email protected],etc.

I know it's an easy one but cannot figure it out. The best I can find is
this mention about the article in Roger's Library from John Nurick to Eli the
Iceman back on May of 2006:

"That concatenates values in multiple fields from the current record;
Eli's question implied concatenating values in one field of multiple
records. "

The post did not answer the question though. It only identified it. But I
need the answer.

Can someone help?

Thank you in advance for your time and advice!

Bonnie

 

[Duane Hookom]

Rather than create a single function for a single use, there is a generic
concatenate function with sample usage at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16.
--
Duane Hookom
Microsoft Access MVP

You need to loop thru all records and get each email address and then
CONCATENATE :
This is how i do it:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select emailadd from yourTable")
If rst.RecordCount = 0 Then
MsgBox "No Record In the Table"
rst.Close
Set rst = Nothing
Set db = Nothing
Else
With rst

.MoveFirst
Do While Not .EOF
emailadd = emailadd & !emailadd & ", "
.MoveNext
Loop
.Close
End With
End If

Set rst = Nothing
Set db = Nothing


NOTE: remove the extra comma ","

Hi everyone. Using A02 on XP. I know this is possible but I just cannot
find it. I have searched all through here. Most of the concatenate posts do
not apply to what I need. The link to Roger's Library also does not. They

I need to create a text string from ALL RECORDS IN ONE FIELD so I can send a
group email.

My table has a field [EmailAddr], I have over 200 records and would like to
get this:

(e-mail address removed),[email protected],[email protected],[email protected],[email protected],etc.

I know it's an easy one but cannot figure it out. The best I can find is
this mention about the article in Roger's Library from John Nurick to Eli the
Iceman back on May of 2006:

"That concatenates values in multiple fields from the current record;
Eli's question implied concatenating values in one field of multiple
records. "

The post did not answer the question though. It only identified it. But I
need the answer.

Can someone help?

Thank you in advance for your time and advice!

Bonnie

--
spread the WORD

Message posted via http://www.accessmonster.com

.

 

[John W. Vinson]

I need to create a text string from ALL RECORDS IN ONE FIELD so I can send a
group email.

Try this link:
http://www.mvps.org/access/modules/mdl0004.htm

 

[Bonnie A]

Hi NuBie! Thanks so much for the reply. Sorry I took so long to check it.

So I need to take your code and create a new MODULE? Then name it, compile
it and then create a button on a form to run it?

I need a quick paragraph on 'what to do with what you gave me'. It would
give me the trail to follow WITH the tool provided.

Thank you very much for your time and the wonderful reply. I just need to
be sure what to do with it. I know I need to be sure I use MY table name and
MY field name. I'm okay with that.

It doesn't look like an expression, it looks like module VB. I just don't
get in there much but being able to do this would so much help me with
notifying clients in bulk rather than copy and paste myself ill.

Thanks again!
--
Bonnie W. Anderson
Cincinnati, OH

You need to loop thru all records and get each email address and then
CONCATENATE :
This is how i do it:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select emailadd from yourTable")
If rst.RecordCount = 0 Then
MsgBox "No Record In the Table"
rst.Close
Set rst = Nothing
Set db = Nothing
Else
With rst

.MoveFirst
Do While Not .EOF
emailadd = emailadd & !emailadd & ", "
.MoveNext
Loop
.Close
End With
End If

Set rst = Nothing
Set db = Nothing


NOTE: remove the extra comma ","

Hi everyone. Using A02 on XP. I know this is possible but I just cannot
find it. I have searched all through here. Most of the concatenate posts do
not apply to what I need. The link to Roger's Library also does not. They

I need to create a text string from ALL RECORDS IN ONE FIELD so I can send a
group email.

My table has a field [EmailAddr], I have over 200 records and would like to
get this:

(e-mail address removed),[email protected],[email protected],[email protected],[email protected],etc.

I know it's an easy one but cannot figure it out. The best I can find is
this mention about the article in Roger's Library from John Nurick to Eli the
Iceman back on May of 2006:

"That concatenates values in multiple fields from the current record;
Eli's question implied concatenating values in one field of multiple
records. "

The post did not answer the question though. It only identified it. But I
need the answer.

Can someone help?

Thank you in advance for your time and advice!

Bonnie

--
spread the WORD

Message posted via http://www.accessmonster.com

.

 

[NuBie via AccessMonster.com]

So I need to take your code and create a new MODULE? Then name it, compile

it and then create a button on a form to run it?

1. Yes, that's how i'll do it: Create it in a module so that you can call it
anywhere in the application.
In a module create:

Function GetEmailAddress() As String

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select emailadd from yourTable")
If rst.RecordCount = 0 Then
MsgBox "No Record In the Table"
rst.Close
Set rst = Nothing
Set db = Nothing
Else
With rst

.MoveFirst
Do While Not .EOF
emailadd = emailadd & !emailadd & ", " 'I
think you should use semicolon (instead
.MoveNext
Loop
.Close
End With
End If

Set rst = Nothing
Set db = Nothing


GetEmailAddress = Left(emailadd, Len(emailadd) - 2)

End Function

2. In a Send Email button use GetEmailAddress()

On Error GoTo Err_btnSendMail

DoCmd.Hourglass True
Dim olApp As Object
Dim olMsg As Object

Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(olMailItem)

DoCmd.Hourglass False
With olMsg
.To = GetEmailAddress()
.Subject = "Your application has been approved!" ' you
know you can customize this
.Body = "whatever..."
' and this
.Send
End With
DoCmd.Hourglass False

Exit_btnSendMail:
Exit Sub

Err_btnSendMail:
MsgBox Err.Number & " " & Err.Description, vbOKOnly + vbInformation
Resume Exit_btnSendMail
End Sub

Hi NuBie! Thanks so much for the reply. Sorry I took so long to check it.

So I need to take your code and create a new MODULE? Then name it, compile
it and then create a button on a form to run it?

I need a quick paragraph on 'what to do with what you gave me'. It would
give me the trail to follow WITH the tool provided.

Thank you very much for your time and the wonderful reply. I just need to
be sure what to do with it. I know I need to be sure I use MY table name and
MY field name. I'm okay with that.

It doesn't look like an expression, it looks like module VB. I just don't
get in there much but being able to do this would so much help me with
notifying clients in bulk rather than copy and paste myself ill.

Thanks again!

You need to loop thru all records and get each email address and then
CONCATENATE :

[quoted text clipped - 55 lines]

 

[NuBie via AccessMonster.com]

See John's suggestion, also Duane has a very good sample on the link he
provided. The logic is almost the same but making it generic makes the
function more flexible, i.e. you can pass Table Name, Field Name, Separator...
.etc.

Hi NuBie! Thanks so much for the reply. Sorry I took so long to check it.

So I need to take your code and create a new MODULE? Then name it, compile
it and then create a button on a form to run it?

I need a quick paragraph on 'what to do with what you gave me'. It would
give me the trail to follow WITH the tool provided.

Thank you very much for your time and the wonderful reply. I just need to
be sure what to do with it. I know I need to be sure I use MY table name and
MY field name. I'm okay with that.

It doesn't look like an expression, it looks like module VB. I just don't
get in there much but being able to do this would so much help me with
notifying clients in bulk rather than copy and paste myself ill.

Thanks again!

You need to loop thru all records and get each email address and then
CONCATENATE :

[quoted text clipped - 55 lines]