Send individual emails via Outlook from a table in Access

[bears1990]

My goal is to send out a message (with attachments) to individuals within my client table. In a form, I have created fields that will capture the Subject, Body and attachment path. I have modified codes that I have researched online to send out emails to individual clients, however, this page is meant to send out a generic message to all. The table contains over a 1000 emails, so I cannot just send them all at once because Outlook won't allow that (I believe the limit is 100 or so emails). So I was wondering if there was a way to loop it so that each individual gets sent out a separate email? This way, no one can see anyone else's email address, as well. Any assistance would be greatly appreciated.

[Seth Schrock]

You can loop through a recordset in VBA to send an individual email to each person in your clients table. First, open the recordset. Then use a Do While loop testing for the EOF (end of file) property of the recordset. Inside this loop run your code to send the email. Once the email is sent, move to the next record.

[zmbd]

... and there are tons of threads covering this here at bytes.com, see if one of these is close-enough for your application.

https://bytes.com/searchresults.php?q=ms%20access%20email%20vba#gsc.tab=0&gsc.q=ms%20access%20email%20vba&gsc.page=1

Simple emails can be sent using the:
DoCmd.SendObjec t Method
it will handle small body text and single attachment without much fuss. If you are not sending an object then leave that out of the command, also I highly advise using named parameters and building your sting first instead of embedding the value within the function as shown in most online tutorials. Finally, make sure to set the display email option to false or you'll have a ton of emails to confirm, and if canceled, error trap.

More complex situations may require a bit of automation code:
Microsoft Access / vba > insights > application automation

Once you have decided on a course of action, build your code and test it out, I suggest using a small batch of temporary email addresses as generated by one of the free "temporary email" services. If things work, yea, if not, then post back your code, detailed explanation of what is (and/or isn't) happening along with what steps you have taken to troubleshoot the situation.

[bears1990]

Seth, I've attempted to use your recommendation but I've run into issues. The first email send successfully but the others do not work. Obviously I'm doing something incorrectly; could you please review & advise.

[Seth Schrock]

Sorry, I can't open attachments at work. You will need to post your code.

[bears1990]

Here it is:

Sub SendEmail()

Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem
Dim rs As DAO.Recordset

On Error Resume Next
Err.Clear

Set oOutlook = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set oOutlook = New Outlook.Application
End If

Set oEmailItem = oOutlook.CreateItem(olMailItem)
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryMain")

If Not (rs.BOF And rs.EOF) Then

rs.MoveFirst

Do Until rs.EOF = True

With oEmailItem
.To = rs!PersonEmail
.Subject = "NKS: Test"
.Body = "Just a test to see if this works"
.Send
End With

rs.MoveNext
Loop

End If
rs.Close

Set oEmailItem = Nothing
Set oOutlook = Nothing
Set rs = Nothing

End Sub

[Seth Schrock]

Nothing jumps out at me. When you say "the others don't work", what do you mean? Do you get an error message? Does the user not get the email?

[PhilOfWalton]

I believe you nee to put
Set oEmailItem = oOutlook.Create Item(olMailItem )
within the loop i.e after Do Until Rs.EOF

Phil

[Seth Schrock]

Good catch Phil, you are correct.

[bears1990]

Phil, that did the trick. Thank you for your keen eye. And thank you once again to Seth for initially pointing me in the right direction. This forum is invaluable because of individuals like yourselves. Until next time.