Getting all records in one string text

[cardgunner]

First of all I'm not very good with VB. What I'm trying to do is get all my customers email addresses in one text string. So it would go like 123@aol.com; bob@yahoo.com; fred@hotmail.com; and etc. My customer could then copy it and paste it in her TO: box of her email server. Any help would be great.

 

[WayneRyan]

CG,

You're not gonna be able to do it without code. You can put the following
on a Command Button. But what are you going to do with the string?

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strAllEmails As String

' *** Start with nothing in the string
strAllEmails = ""

' *** Open your table for reading
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * From YourTable")

' *** While there are records add the Email Address to your string
While Not rst.EOF And Not rst.BOF
   strAllEmails = strAllEmails & rst@EmailAddress & "; "
   rst.MoveNext
   Wend

' *** Finally, remove the LAST "; " that you've been adding
strAllEmails = Left(strAllEmails, Len(strAllEmails) - 2)

' *** What to do with the string --> strAllEmails ???

Wayne

 

[cardgunner]

My customer wants to be able to hit a button and have all the email addresses appear in one long string so she can copy all of them and paste them in her TO: box of her email sender and then email all of them updates and information on products she sells that they have subsribed for. Some of these customer wants info on pillows and others on bed linen. She checks off the field they wants and runs a query to give her all the email address of these people. My dilema is how do I get all of those into her TO: box.

 

[WayneRyan]

CG,

I don't work with EMail in Access.

The above will leave you will all of the Email Address in a string variable named
strAllEmails in VBA.

If you substitute a textbox on your form, substitute Me.txtSomeControl
for each occurrence of strAllEmails in the example above. You could
manually copy & paste the textbox into whereever.

I'm sure that you could also take the result of the code & programmatically
insert it into the EMail software. Maybe someone that works with it can
jump in and help.

Either way, you need code like the previous example to extract/format
the data from the database.

hth,
Wayne

 

[ghudson]

I had done something like that before and I just dug it up to compare to Waynes. I have a text box named [txtEMailNames] and it is formatted so that the user never sees it. The final step in my code is to copy the values in the txtEMailNames text box to the clip board to be pasted by the user in the To section of thier email.

    Dim sNames As String
    Dim sMailNames As String
    Dim rMailNames As Recordset
    Dim MyDb As Database

    Set MyDb = CurrentDb()

    DoCmd.SetWarnings False

    sNames = ""

    sMailNames = "SELECT Email From YourTableNameHere;"

    Set rMailNames = MyDb.OpenRecordset(sMailNames)

        rMailNames.MoveFirst

    Do Until rMailNames.EOF = True
    
        sNames = sNames & ", " & rMailNames!Email
    
    If Not rMailNames.EOF Then rMailNames.MoveNext
    
    Loop

    rMailNames.Close

    Me.txtEMailNames.Value = sNames

    Me.txtEMailNames.SetFocus

    DoCmd.RunCommand acCmdCopy
 
    Beep
        
    MsgBox "You have copied all the current email addresses to the Clipboard." & Chr(13) & Chr(13) & "Now you can Paste (Ctrl + V) the Email addresses into the To: section of a new Email message.", vbInformation, "Copied Email Addresses To The Clipboard"

    DoCmd.SetWarnings True

 

[cardgunner]

Wayne,
I appreciate all the help. Unfortunely the code you gave me is like chineese. I will have to look at it in the morning. I really do need to learn VBA. Thank you
CG

 

[cardgunner]

ghudson,

I would like to revisit this if you are still interested. As I have said I don't know VBA however I really need to learn this function. In order to get this to work where do I need to start?

Rob

You wrote:

I had done something like that before and I just dug it up to compare to Waynes. I have a text box named [txtEMailNames] and it is formatted so that the user never sees it. The final step in my code is to copy the values in the txtEMailNames text box to the clip board to be pasted by the user in the To section of thier email.

    Dim sNames As String
    Dim sMailNames As String
    Dim rMailNames As Recordset
    Dim MyDb As Database

    Set MyDb = CurrentDb()

    DoCmd.SetWarnings False

    sNames = ""

    sMailNames = "SELECT Email From YourTableNameHere;"

    Set rMailNames = MyDb.OpenRecordset(sMailNames)

        rMailNames.MoveFirst

    Do Until rMailNames.EOF = True
    
        sNames = sNames & ", " & rMailNames!Email
    
    If Not rMailNames.EOF Then rMailNames.MoveNext
    
    Loop

    rMailNames.Close

    Me.txtEMailNames.Value = sNames

    Me.txtEMailNames.SetFocus

    DoCmd.RunCommand acCmdCopy
 
    Beep
        
    MsgBox "You have copied all the current email addresses to the Clipboard." & Chr(13) & Chr(13) & "Now you can Paste (Ctrl + V) the Email addresses into the To: section of a new Email message.", vbInformation, "Copied Email Addresses To The Clipboard"

    DoCmd.SetWarnings True

[/QUOTE]