Create a recordset for multiple emails and send one email

[mousemat]

Hi

I have some code that takes from a query, an email address and sends out an email.

Due the security warning that happens for each individual email, i want to create ONE email for all email address in the query and populate them to the BCC section of the sendobject.

Here is the code that I currently use

Private Sub cmdSendEmail_Click()
Dim MyDB As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
 
Set MyDB = CurrentDb()

Set rsEmail = MyDB.OpenRecordset("SELECT * FROM qrySendEmails WHERE CourseID  = " & CourseID & "")
With rsEmail
        .MoveFirst
        Do Until rsEmail.EOF
            If IsNull(.Fields(11)) = False Then
                sToName = "mycurrentworkemailaddress@somewhere.co.uk"
                sBccName = .Fields(11) ' Email address of the recipient
                sSubject = .Fields(1) & " " & .Fields(2)
                sMessageBody = "Dear Delegate " & vbCrLf & _
                vbCrLf & _
                vbCrLf & _
                "This email is confirmation of your place on the following course:" & _
                vbCrLf & _
                vbCrLf & _
                "" & .Fields(1) & _
                vbCrLf & _
                "" & .Fields(2) & _
                vbCrLf & _
                "The course Trainer is: " & .Fields(3) & _
                vbCrLf & _
                vbCrLf & _
                "The Course Venue is:" & _
                vbCrLf & _
                vbCrLf & _
                    "" & .Fields(5) + vbCrLf & _
                    "" & .Fields(6) + vbCrLf & _
                    "" & .Fields(7) + vbCrLf & _
                    "" & .Fields(8) + vbCrLf & _
                    "" & .Fields(9) + vbCrLf & _
                    "" & .Fields(10)

                DoCmd.SendObject acSendNoObject, , , _
                    sToName, , sBccName, sSubject, sMessageBody, False, False
            End If
            .MoveNext
        Loop
End With
 
Set MyDB = Nothing
Set rsEmail = Nothing


End Sub

I want to populate the BCC field with multiple emails addresses seperated by ":" and send just one email

Any help would be great

 

[ted.martin]

You can prevent to Security warning if you use the Outlook Object Library to create and send the eMail. Also take a look at Redemption from Dimastr.

 

[mousemat]

Ted

Many thanks for that. I'm aware of Redemption but that's not the issue i really have.

My issue is my lack of ability to create a recordset to populate the Bcc part of the email.

My code currently sends one email per person, so if my query throws up 10 recipients, then outlook will send 10 emails. I want to populate the Bcc part with the 10 email address, thus sending only one email from outlook and getting only one instance of the Security Warning

 

[ted.martin]

Yes but by using the Outlook Object model you can use for example objMail.bcc = Your addy and then loop it in your code inserting a semicolon between each addy.

objmail = objmail & ";" & next addy

 

[mousemat]

Hi Ted. thanks for the suggestion. I have resolved this issue in a slightly different way

Set rsEmail = MyDB.OpenRecordset("SELECT * FROM qrySendEmails WHERE CourseID = 406")
Do While Not rsEmail.EOF
Bccmail = Bccmail & rsEmail.Fields("Email") & ";"