combine multiple email addresses into .bcc from recordset to send outlook email from access vba

[adhoustonj]

Hello,
I hope this post is in the right place. The code below is populating the bcc and to fields in my outlook message. Is it possible to only populate the bcc and leave the to field blank? Or am I approaching this wrong and should loop through and send an individual email to each email from the recordset?

Private Sub Command180_Click()
    Dim rs As DAO.Recordset
    Dim OlApp As Object
    Dim OutMail As Object
    Dim strEmail As String
    Dim bccEmails As String


    Set OlApp = CreateObject("Outlook.Application")
    Set OutMail = OlApp.CreateItem(olMailItem)


    Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Email FROM tblEmail")
    With rs
        Do Until .EOF
            strEmail = !Email
            ' add email to BCC email list string
            bccEmails = bccEmails & strEmail & ";"


            With OutMail
                .Recipients.Add strEmail
            End With


            rs.MoveNext
        Loop
    End With
    rs.Close
    Set rs = Nothing


    ' set BCC using string of concatenated emails                 
    OutMail.BCC = left(bccEmails, Len(bccEmails) - 1) ' trims trailing semicolon


    OutMail.Display


End Sub

 

[adhoustonj]

Never mind... I see what was happening. the .recipients.Add strEmail was adding them to the .to and then outMail.BCC is being set with bccEmails

 

[ebs17]

Concat is shorter when using an ADODB.Recordset.

' set BCC using string of concatenated emails                 
 OutMail.BCC = ADODBRecordset.GetString(adClipString, , , ";")

 

[Gasman]

Hello,
I hope this post is in the right place. The code below is populating the bcc and to fields in my outlook message. Is it possible to only populate the bcc and leave the to field blank? Or am I approaching this wrong and should loop through and send an individual email to each email from the recordset?

Private Sub Command180_Click()
    Dim rs As DAO.Recordset
    Dim OlApp As Object
    Dim OutMail As Object
    Dim strEmail As String
    Dim bccEmails As String


    Set OlApp = CreateObject("Outlook.Application")
    Set OutMail = OlApp.CreateItem(olMailItem)


    Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Email FROM tblEmail")
    With rs
        Do Until .EOF
            strEmail = !Email
            ' add email to BCC email list string
            bccEmails = bccEmails & strEmail & ";"


            With OutMail
                .Recipients.Add strEmail
            End With


            rs.MoveNext
        Loop
    End With
    rs.Close
    Set rs = Nothing


    ' set BCC using string of concatenated emails                
    OutMail.BCC = left(bccEmails, Len(bccEmails) - 1) ' trims trailing semicolon


    OutMail.Display


End Sub

You really should be checking for eof before trying to use a recordset?

 

[adhoustonj]

You really should be checking for eof before trying to use a recordset?

Would a "If rs.RecordCount > 0 Then" after the set rs take care of this?

 

[Gasman]

I just check for EOF. If the recordset is empty when you open it, that is True as is BOF, however I just use the one test, EOF.

 

[adhoustonj]

I just check for EOF. If the recordset is empty when you open it, that is True as is BOF, however I just use the one test, EOF.

so would you use "if not rs.eof then"? Thanks!

 

[Gasman]

Yes, but if you are looping, I would use Do Until rst.EOF

 

[adhoustonj]

Yes, but if you are looping, I would use Do Until rst.EOF

Understood. Thanks for the help!