Take separate records email addresses and combine them with ; separato

[MacroScope]

A simple query will provide a list of email addresses from a table in the DB, but each one is on a separate record. I want to create a list of those addresses for sending, with each one separated from the other by a ; but with all of them in one memo control for insertion in the To: field of an outgoing mass email.

Any ideas on how this can be accomplished?

 

[TheAceMan1]

How are ya MacroScope . . .

Here's an example using a form with a memo field and a command button. Following is the code for the command button. [blue]you![/blue] substitute proper names in [purple]purple[/purple]:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, Pack As String
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("[purple][b]QueryName[/b][/purple]", dbOpenDynaset)
   
   If Not rst.BOF Then
      Do
         If Not IsNull(rst![purple][b]FieldName[/b][/purple]) Then
            If Pack <> "" Then
               Pack = Pack & ";" & rst![purple][b]FieldName[/b][/purple]
            Else
               Pack = rst![purple][b]FieldName[/b][/purple]
            End If
         End If
         
         rst.MoveNext
      Do Until rst.EOF
   Else
      MsgBox "NO RECORDS IN [purple][b]QueryName[/b][/purple]!"
   End If
   
   If Paxk <> "" Then Me.[purple][b]MemoFieldName[/b][/purple] = Pack

   Set rst = Nothing
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MacroScope]

Thanks, AceMan. I'll check it out tomorrow and let you know.

 

[dhookom]

There is a generic function for this at faq701-4233.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

You may also consider the GetString method of the ADODB.Recordset object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MacroScope]

Thanks to all. I was able to get Ace's solution to work so I didn't try the others, but I'm going to save them for future experimentation.

 

[TheAceMan1]

MacroScope . . .

So sorry about:

[blue]Do Until rst.EOF
   [green][b]which should be[/b][/green]
[purple][b]Loop[/b][/purple] Until rst.EOF[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

MacroScope . . .

I thought the link provided by [blue]dhookom[/blue] looked familiar. Checking it out proved true ... I've used it in a number of places. My code is specific to your needs. However that provided by [blue]dhookom[/blue] is [blue]Global[/blue] and works well. I'm prompting you to check it out ... as it will serve you better as a global! (BTW: ... nice function [blue]dhookom![/blue]).

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]