Merge rows

[THANOS]

Hi!

Is there a way to merge a table list with client emails into one cell? What I have is a list of emails etc:
john@hotmail.com
mary@msn.com
lussy@hotmail.com
and I want to merge them in one cell using a comma as a separator, like:
john@hotmail.com, mary@msn.com, lussy@hotmail.com.

Is this possible?

Thank you very much

 

[DCrake]

This is possible, but why do you want to do it? I pressume you want to create a block sending list for emails.

Try the following method

Public Function GetEmailList() As String

Dim Rs As DAO.Recordset
Dim StrAddresses As String

'Open a table of query that contains the list of email addresses
Set Rs = CurrentDb.OpenRecordset(MyTableOrQuery)

'Check that there is at least on record
Do Until Rs.EOF
    'Is there an email address in the field (Not Null or Empty)
    If Nz(Rs("EmailAddressField","") <> "" Then
        StrAddresses = StrAddresses & Rs("EmailAddressField") & ";"
    End If
    Rs.MoveNext
Loop
'Close the open recordset and remove from memory
Rs.Close
Set Rs = Nothing

'Is there at least one address in the list
'All email addresses will always have an @ sign in them

'Drop the last semi-colon
If InStr(StrAddresses),"@") > 0 Then
   StrAddresses = Left(StrAddresses,Len(StrAddresses)-1)
End If

GetEmailList = StrAddresses

End Function

Save this function in a module and call it as and when required. This function could be modified to pass the name of the table or query to it to make it more versatile.

Such As

Public Function GetEmailList(StrTableOrQuery As String) As String

Set Rs = CurrentDb.OpenRecordset(StrTableOrQuery)

CodeMaster:

 

[THANOS]

First I would like to thank you for your reply.

Second I made some changes to the code provided above to fit to my database tables and fields. But when I run it I get no action at all. Nothing happens.

Can you please check what am I doing wrong ?
Public Function GetEmailList() As String

Dim Rs As DAO.Recordset
Dim StrAddresses As String

'Open a table of query that contains the list of email addresses
Set Rs = CurrentDb.OpenRecordset("tblMailingList")



'Check that there is at least on record
Do Until Rs.EOF
'Is there an email address in the field (Not Null or Empty)
If Nz(Rs("EmailAddress"), "") <> "" Then
StrAddresses = StrAddresses & Rs("EmailAddress") & ";"
End If
Rs.MoveNext
Loop
'Close the open recordset and remove from memory
Rs.Close
Set Rs = Nothing

'Is there at least one address in the list
'All email addresses will always have an @ sign in them

'Drop the last semi-colon
If InStr((StrAddresses), "@") > 0 Then
StrAddresses = Left(StrAddresses, Len(StrAddresses) - 1)
End If

GetEmailList = StrAddresses

End Function

 

[THANOS]

To reply to your question, what I want is to select a group of contact emails (different each time, ok I have a query for that) and send them an email directly from Outlook. Preferably I would prefer to use a code to pick all selected by the query emails and send automatically to Outlook in the "To:" field. Although I did a search I couldn' t find something relative. So if only I could "merge" my list in one field that could be a good solution for me.

Thanks

 

[DCrake]

Ok To test if this is working I want you to click on modules then select the module that contains the function GetEmailList. Navigate to the function. Next I want you to press Ctrl+G to open up the immediate window, if it is not already open.

Then within the immediate window type in the following

?GetEmailList

Press return or enter key.

What should happen now is a block of email addresses with a semi-colon as a delimiter should appear underneath the above command. If it does not then move you mouse upto the first line of the function and click on the grey vertical bar to the left of the line. This should then turn the line brown with a brown dot in the vertical bar.

Return to the immediate window and repeat the command. This time the cursor will stop on the first line (which becomes yellow) press the F8 function key to step through the code one line at a time and follow it through. This should give you an indication as to the nature of the fault.

When resolved remove the breakpoint and retry.

If you are familiar with this procedure please ignore the notation.

CodeMaster:

 

[THANOS]

I did as you said. I got a debug error (run time error 3265 object not found in this collection (sorry I am translating from greek and I am not sure if I use the right words)

in this line:

If Nz(Rs(EmailAddress), "") <> "" Then

What I have is a table named tblMailingList with a field of emails named EmailAddress. Is there anything that I forgot to do or to use?

 

[DCrake]

What this is saying is Ok I have found the table tblMailingList but there is not a field called EMailAddress in the table.

Please check the spelling, if need be, design the table, highlight the field and select copy. Then go to your sql statement and select paste.

 

[THANOS]

It is ok now. Looks great. Exactly what I wanted. It worked when I included Emailaddresses field in "".

If Nz(Rs("EmailAddress"), "") <> "" Then

This is great but I got results only if I run the code through vba in the immediate window. Can't I see my results directly in my table or in a text box on my form ?

 

[DCrake]

Yes you can.

If you want to show them on a form, I suggest you create an unbound text box on your form (TxtEmailList), make it wider and deeper than a normal text box so the user can see the results. In the properties set the vertical scroll bar on and make the field locked.

Then you can call the function at any point on your form by either using a button or use the OnLoad Event of the form to populate the text box as soon as the form opens.

Command Button:

Add a command button to your form and name it CmdEmailList

On the OnClick property call the function

Privater Sub CmdEmailList_Click()
     Me.TxtEmailList = GetEmailList()
End Sub

Form Load:

Private Sub OnLoad()
     Me.TxtEmailList = GetEmailList()
End Sub

 

[THANOS]

RESPECT!!!

Works fantastic! Thank you very much for this.

 

[DCrake]

Glad to be of assistance and hope you continue to use this forum.

 

[THANOS]

Βe sure for this. You all are doing a great job and it is respectfull that you share your knowledge with unknown people from all over the world.