How do I send an email from Access to each recipient in a query?

That was fast!

Thanks a lot! I'll try it right now.

matthewspatrick,

How would you modify this code to get multiple recipients on the TO line?  Does the string need to have a certain format or do you need a loop to add each name seperately?

Thanks

I have a problem with the DAO.Recordset part. Apparently this type of variable is not recognized by my
Access.

appc ... go to Tools - References while your code module is open and set a reference to the Microsoft DAO 3.6 Object Library.  Then the DAO.Recordset variable will be recognized.

To answer my own question: For multiple recipients in the same .TO line, use the following format:

strRecipient = "EmailName1" & "; " & "EmailName2" & "; " & "EmailName3" & "; " & "EmailName4"

That is my code right now:

Public Function Sendemails()

Dim olApp As Object
Dim olook As Object
Dim olMsg As Object
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT BranchEscEmailAddresses FROM [NPL Report Calculation for emails]"

Set rs = CurrentDb.OpenRecordset(strSql)

Set olook = CreateObject("Outlook.Application")

Do Until rs.EOF
   
    Set olMsg = olApp.CreateItem(0)
   
    With olMsg
        .To = rs![BranchEscEmailAddresses]
        '.CC = "y@z.com"
        '.BCC = "z@z.com"
        .Subject = "subject"
        .Body = "body"
       ' .Attachments.Add "c:\folder\subfolder\foo.xls"
        .Send
       
    End With
   
    rs.MoveNext
   
Loop

Set olMsg = Nothing
Set olApp = Nothing
rs.Close
Set rs = Nothing

End Function

and when I try and run the function I get the following error message:

Image.bmp

If you want to send more professional emails such as HTML pages with embedded graphics, avoid the limitations of Outlook/MAPI, easily personalize each email, and attach linked objects such as tables/reports, etc. filtered for each contact, consider our Total Access Emailer product.  
It's a commercial product and can use any table or query as its data source for the list of emails to send out. There's a free demo, so you can use that if the commercial option isn't viable.
More info here: http://www.fmsinc.com/MicrosoftAccess/Email.asp

appc,

The code looks fine.  Your screen shot says nothing about what was in your OnClick property for that form.

I typically just embed that code in the Click event sub for the commandbutton I use on the form.

Patrick

Hi Patrick,

Apparently there is a problem with the yellow part....
Access.bmp

The field BrEmails contains email addresses.

For example one record will be: x@domain.com; y@domain.com

And again "email' is a query.

But I still get the error message...
Emails.bmp

Patrick,

First of thanks a lot for all your help.  I will not include spaces anymore. :)

I have set a reference to Microsoft DAO and as you can see on the last screenshot I changed the field and query names to get rid of the spaces.

But I still get the debugger. Is it because of the variable type of strsql? I have no clue..

I guess this will help a little.
Error.bmp

The reference is set, and I tried SELECT BrEmails FROM email from Access and it worked...

Why would it say "too few parameters. Expected 1"??.....

appc,

If the table or column name weren't being recognized, then Jet would think that you were passing a parameter,
and with no value for that parameter you would see that message.

The attached file shows the approach working, albeit in a vastly simplified database.

Patrick
Q-25131500.mdb

Something else to try: save that query, and give it a name like qryBrEmails.

Then change the OpenRecordset call to use the name of the query instead of a SQL statement.

The code for this emailing process automatically sends the emails.

What if I would like to just open the emails and then manually send them like a regular email so I can make modifications?

Public Function Sendemails()

Dim oLook As Object
Dim oMail As Object
Dim rs As DAO.Recordset
Dim strSql As String


Set oLook = CreateObject("Outlook.Application")

strSql = "SELECT BrEmails FROM email"

Set rs = CurrentDb.OpenRecordset(strSql)

Do Until rs.EOF
    
    Set oMail = oLook.CreateItem(0)
    
    With oMail
        .To = rs![BrEmails]
        '.CC = "y@z.com"
        '.BCC = "z@z.com"
        .Subject = "subject"
        .Body = "body"
       ' .Attachments.Add "c:\folder\subfolder\foo.xls"
        .Send
        
    End With
    
    rs.MoveNext
    
Loop

Set oMail = Nothing
Set oLook = Nothing
rs.Close
Set rs = Nothing

End Function

Select allOpen in new window