CODEWITHSUNDEEP
ms-accessms-access-2007vba
ASM2701
ASM2701

Reputation: 139

Access 2007 Multiple Recipient Email

Currently I have this code for sending an email based on criteria from another form. The department I'm building this for has specified more than one person may receive the email. How do i get Access to look at a query that i have built. Which looks at the user table checks to see who can receive these emails and email the the list of emails from the query? 

Select Case Forms!FRM_CallDetails!Model.Value

Case "SM", "TW", "LM", "LV", "SV"

On Error Resume Next

DoCmd.OutputTo acOutputForm, "FRM_CallDetails", acFormatXLS, "C:\temp\WatchList.xls", False

   'Get Outlook if it's running
    Set oApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then
         'Outlook wasn 't running, start it from code
         Set oApp = CreateObject("Outlook.Application")
        Started = True
    End If

                       Set oItem = oApp.CreateItem(olMailItem)
                     With oItem
                        .To = "google@google"
                        .Subject = "AutoEmail"
                        .Body = " this is the body of the email... this is a test email "
                        .Attachments.Add "C:\temp\WatchList.xls"
                        'Send the email
                        .Send
                    End With

                                    Set oItem = Nothing
                                    If Started Then
                                        oApp.Quit
                                    End If

'Display message to the user
MsgBox "A model that is on the watch list has been selected. An Automatic Email has been sent.", vbOKOnly

'Message Body Here



Case Else
     'no email
End Select

Here is the SQL for the query i'm using which I have called Mail_List

SELECT TBL_Users.Email_Address
FROM TBL_Users
WHERE (((TBL_Users.EW_Email)="Y"));

Upvotes: 0

Views: 1151

Answers (1)

parakmiakos
parakmiakos

Reputation: 3020

you could replace your With block with the following:

With oItem
    s = " SELECT TBL_Users.Email_Address" & _
    " FROM TBL_Users " & _
    " WHERE (((TBL_Users.EW_Email)='Y'));"
    Set rs = CurrentDb.OpenRecordset(s)
    listOfMails = ""
    While Not rs.EOF
        listOfMails = listOfMails & rs(0) & ";"
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing

    .To = listOfMails
    .Subject = "AutoEmail"
    .Body = " this is the body of the email... this is a test email "
    .Attachments.Add "C:\temp\WatchList.xls"
    'Send the email
    .Send
End With

Add a declaration for the three variables used as well :

Dim rs As Recordset
Dim s As String, listOfMails as String

This does not actually use your premade query but rather generates it on the spot, but it gets the trick done.

Upvotes: 1

Related Questions