Return all results in one cell/As one block

[Asma]

Hi

I'm saving email address for contacts in a database. I want to send an email to all the contacts and I need the emails to be like one block. That will make it easier by just copy and paste it in the To field in an email.

I've got the data in a table as:
a@b.com;
c@d.com;
e@f.com;

And I need it to be as:

a@b.com; c@d.com; e@f.com

how can I make all results appear in the same cell not as a table



plzzzzzz help

[RuralGuy]

Create a User Defined Function in a standard module that walks through the table and returns the concantenated string.

[Asma]

Create a User Defined Function in a standard module that walks through the table and returns the concantenated string.

Thank you so much for the idea. I understand what your saying ( I worked with java and other programming languages, but I never coded in access), but I don't know where to write the code and what to write ??
any refrences ??

currently all I have is a query that takes the email addresses and adds ";" to the end of each email and it returns a table.

can you plz provide me with more details about how to do it?

[ajetrumpet]

Create a User Defined Function in a standard module that walks through the table and returns the concantenated string.

what's the word on me over with the big boys ruralguy? have I been declared insane yet? I waited like 2 months after they ousted me and took another look but Bob was still on the lookout for my IP. It only took him like 4 or 5 days to browse the new accounts and catch it.


At any rate, here's a module for you:

function t

dim emails as string
dim db as dao.database
dim rs as dao.recordset

set db = currentdb
set rs = db.openrecordset("tablename")

rs.movelast
rs.movefirst

emails = ""

do until rs.eof

emails = emails & rs!fieldname & " "

rs.movenext

loop

rs.close

set rs = nothing
set db = nothing

end function 

of course after this you have to throw the EMAIL string somewhere otherwise it'll just stay in memory.

[RuralGuy]

And there is always something more universal like: http://allenbrowne.com/func-concat.html

[Asma]

what's the word on me over with the big boys ruralguy? have I been declared insane yet? I waited like 2 months after they ousted me and took another look but Bob was still on the lookout for my IP. It only took him like 4 or 5 days to browse the new accounts and catch it.


At any rate, here's a module for you:

function t

dim emails as string
dim db as dao.database
dim rs as dao.recordset

set db = currentdb
set rs = db.openrecordset("tablename")

rs.movelast
rs.movefirst

emails = ""

do until rs.eof

emails = emails & rs!fieldname & " "

rs.movenext

loop

rs.close

set rs = nothing
set db = nothing

end function 

of course after this you have to throw the EMAIL string somewhere otherwise it'll just stay in memory.

That was really helpful , but I still have one last stupid question
How to link this to my query?? and what are the choices I have to return the String Emails ??
Now, im using a msgbox to check the output ,but I'm looking for a better way because I want to be able to copy and paste the info .

[Asma]

and here's the function with small editting cz I wanted to return the two optional email fields if they have something

Function t() As String


Dim emails As String
Dim db As dao.Database
Dim rs As dao.Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("Client Contact")


rs.MoveLast
rs.MoveFirst


emails = ""


Do Until rs.EOF


emails = emails & rs!EmailAddress1 & "; "


If Not (EmailAddress2 = " ") Then
emails = emails & rs!EmailAddress2 & "; "
End If


If Not (EmailAddress3 = " ") Then
emails = emails & rs!EmailAddress3 & "; "
End If


rs.MoveNext


Loop


rs.Close


msgbox( emails)


Set rs = Nothing
Set db = Nothing




End Function

[weekend00]

replace
msgbox( emails)
with
debug.print emails

then run the function in VB Editor. the emails will show in "immediate window", then you can copy and paste.