Results 1 to 5 of 5
  1. #1
    Jorge is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2018
    Posts
    3

    Join all emails from a table into one string

    Hi,

    I have never posted any question in any forum so please excuse me if i am missing something.

    I have a query with a series of email addresses (the query is in fact a distribution list)
    a@gmail.com
    b@gmail.com
    c@gmail.com

    I want to put all of these addresses into the Outlook field of BCC. in the following format a@gmail.com; b@gmail.com; c@gmail.com

    i will then write different mails different, with subjects but always to the same group.

    I do not want to use the distribution list functionality of Outlook.

    I have seen various complicated VBA that send always the same e-mail to different people or adds attachments, or send it to one person only. But cannot send it to multiple.


    I would like to press a button on the top and send the addresses on a list to the BCC field in outlook and allow editing the email




    Thanks for your support

    J

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    submit a query to the function to collect the list into a single line,
    usage:
    sEmails = CvtList2Str("qsEmailList")

    Code:
      'convert a list into a single string (separated)
    Public Function CvtList2Str(ByVal pvQry)
    Dim rst   'As Recordset
    Dim sTxt As String
    Set rst = CurrentDb.OpenRecordset(pvQry)
    With rst
        While Not .EOF
             sTxt = sTxt & .Fields(0).Value & ";"
             .MoveNext
        Wend
    End With
    Set rst = Nothing
    End Function

  3. #3
    Jorge is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2018
    Posts
    3

    Thumbs up Not sure what to do?

    Thanks Ranman256,

    Not sure how to run a public function so this is what i did.

    Private Sub Command4_Click()
    sEmails = CvtList2Str("qsEmailList")
    End Sub
    'convert a list into a single string (separated)
    Public Function CvtList2Str(ByVal pvQry)
    Dim rst 'As Recordset
    Dim sTxt As String
    Set rst = CurrentDb.OpenRecordset(pvQry)
    With rst
    While Not .EOF
    sTxt = sTxt & .Fields(0).Value & ";"
    .MoveNext
    Wend
    End With
    Set rst = Nothing
    End Function

    the code stops on line
    Set rst = CurrentDb.OpenRecordset(pvQry)

    What am i doing wrong?

    Completely new to this
    Thanks

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    SORRY, I left off the line:
    CvtList2Str = sTxt

    Code:
     'convert a list into a single string (separated)
     Public Function CvtList2Str(ByVal pvQry)
     Dim rst 'As Recordset
     Dim sTxt As String
     Set rst = CurrentDb.OpenRecordset(pvQry)
     With rst
        While Not .EOF
           sTxt = sTxt & .Fields(0).Value & ";"
           .MoveNext
        Wend
     End With
     
     CvtList2Str = sTxt
     Set rst = Nothing
     End Function
    you can also send it a table name:
    sEmails = CvtList2Str("tEmails")

  5. #5
    Jorge is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2018
    Posts
    3
    Sorry Ranman256

    I really dont understand this. VBA is not my strong point. i put the code as follows and nothing happens.

    Im surely doing something wrong. Any help would be appreciated i am getting desperate hereClick image for larger version. 

Name:	Capture.JPG 
Views:	6 
Size:	60.5 KB 
ID:	32645

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-20-2017, 10:56 AM
  2. Replies: 1
    Last Post: 02-08-2017, 07:18 AM
  3. Replies: 3
    Last Post: 06-11-2016, 10:16 AM
  4. Replies: 2
    Last Post: 04-05-2015, 06:06 PM
  5. Getting table/form content into generated emails using VBA
    By Monterey_Manzer in forum Programming
    Replies: 5
    Last Post: 06-18-2012, 12:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums