×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Multiple Emails via MS Access to BCC Recipients

Multiple Emails via MS Access to BCC Recipients

Multiple Emails via MS Access to BCC Recipients

(OP)
Hi
I have been using this codes for some time to send to Recipients using the query
The list of emails getting added to the "TO" list
Some of the people in the list have said they now do not want other people to see their email, how can I modify this code so that the list created gets added to "BCC" not "TO"

Thanks for your help

Private Sub CommandSendEmails_Click()

DoCmd.SetWarnings False
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT TblEmailList.* FROM TblEmailList"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rs.RecordCount = 0
With rs
.MoveFirst
.Delete
.MoveNext
End With
Loop

DoCmd.OpenQuery "QryMyEmailAddresses"

Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim myMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim MyBodyText As String
Dim rsemail As DAO.Recordset
Dim ns As Outlook.NameSpace
Dim Folder As Outlook.MAPIFolder
Dim mysql As String
Subjectline$ = "Information about Tai Chi"
DoCmd.SetWarnings False
Set MyOutlook = New Outlook.Application
Set MyOutlook = CreateObject("Outlook.Application")
Set ns = MyOutlook.GetNamespace("MAPI")
Set Folder = ns.GetDefaultFolder(olFolderInbox)

MyOutlook.Explorers.Add Folder
Set db = CurrentDb()
mysql = "SELECT DISTINCT TblEmailList.email FROM TblEmailList;"

Set rsemail = db.OpenRecordset(mysql)

Set myMail = MyOutlook.CreateItem(olMailItem)

Do Until rsemail.EOF

'this allows you to send one email to multiple recipients
myMail.Recipients.Add rsemail(0)


'And on to the next one...
rsemail.MoveNext

Loop
'This gives it a subject
myMail.BCC = strSQL
myMail.Subject = Subjectline$
myMail.SendUsingAccount = MyOutlook.Session.Accounts.Item(1)
myMail.Body = "Hi Everyone, " & Chr(13) & Chr(13) & "Enter your email text Here" & Chr(13) & Chr(13) & "Best Wishes" & Chr(13) & Chr(13) & "Dee"
myMail.Display

Set myMail = Nothing
Set MyOutlook = Nothing
DoCmd.SetWarnings True
rsemail.Close
db.Close
Set db = Nothing

Exit Sub

End Sub

RE: Multiple Emails via MS Access to BCC Recipients

Add a declaration for your e-mail list...

Dim strEmailList as string

Replace
myMail.Recipients.Add rsemail(0)

With
strEmailList = IIF(strEmailList <> "", strEmailList & "; "& rsemail(0), rsemail(0))

Replace
myMail.BCC = strSQL

With
myMail.BCC = strEmailList

RE: Multiple Emails via MS Access to BCC Recipients

(OP)
Hi lameid

Fantastic works a treat
Thanks you very much

RE: Multiple Emails via MS Access to BCC Recipients

Here's a pared down version of your code with some cvhanges I'd make;

CODE

Public Sub ParedDown()
    Dim db As DAO.Database
    Dim strSQL As String
    Dim MyOutlook As Outlook.Application
    Dim mysql As String
    Dim rsemail As DAO.Recordset
    Dim myMail As Outlook.MailItem
    
    ' Cleanup table
    Set db = CurrentDb()
    strSQL = "delete * FROM TblEmailList"
    db.Execute strSQL
    
    ' Repopulate
    DoCmd.OpenQuery "QryMyEmailAddresses"

    DoCmd.SetWarnings False
    
    Set MyOutlook = CreateObject("Outlook.Application")
    
    mysql = "SELECT DISTINCT TblEmailList.email FROM TblEmailList"
    Set myMail = MyOutlook.CreateItem(olMailItem)
    Set rsemail = db.OpenRecordset(mysql)
    
    Do Until rsemail.EOF
        ' this allows you to send one email to multiple BCC recipients
        With myMail.Recipients.Add(rsemail(0))
            .Type = olBCC ' Turn each recipient into a BCC recipient
            .Resolve ' make sure Outlook understands
        End With
        ' And on to the next one...
        rsemail.MoveNext
    Loop
    
    ' This gives it a subject
    myMail.Subject = "Information about Tai Chi"
    myMail.SendUsingAccount = MyOutlook.Session.Accounts.Item(1)
    myMail.Body = "Hi Everyone, " & Chr(13) & Chr(13) & "Enter your email text Here" & Chr(13) & Chr(13) & "Best Wishes" & Chr(13) & Chr(13) & "Dee"
    myMail.Display
    
    Set myMail = Nothing
    Set MyOutlook = Nothing
    DoCmd.SetWarnings True
    rsemail.Close
    db.Close
    Set db = Nothing
End Sub 

RE: Multiple Emails via MS Access to BCC Recipients

strongm,

Thanks for using TGML above and beyond. The extra formatting of an alternative color for comments shows huge commitment to helping others by making the code readable. Plus all of this was a bonus since the question had already been marked as satisfied!

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close