0

I have a form that has a list of people that I want my code to loop through and send individual emails. I've done this before, but somehow I am overlooking something. The main variables I am using in my form are NAME (the name of the person I am emailing) and EMAIL (the email address). Not sure why I am getting the Array Index Out of Bounds error, but hopefully it's a simple fix. Any help is appreciated!


Set OutApp = CreateObject("Outlook.Application")

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("My_Table")

With rs

    If .EOF And .BOF Then

        MsgBox "No emails will be sent becuase there are no records assigned from the list", vbInformation

    Else

        Do Until .EOF

            stremail = Me![Email] 'This is the list of people's email addresses I want to email

            strsubject = "the subject - placeholder for now"

            strbody = "Dear " & Me![Name] & "Hello and goodbye."

 

            Set OutMail = OutApp.CreateItem(olMailItem)

            With OutMail

                .To = stremail

                .CC = ""

                .BCC = ""

                .Subject = strsubject

                .Body = strbody

 

                .SendUsingAccount = OutApp.Session.Accounts.Item(2)

                .Send

            End With

            .MoveNext

        Loop

    End If

End With

End Sub
tenebris silentio
  • 519
  • 5
  • 23
  • Which line triggers error? Is it the .Item(2)? How many accounts are there? – June7 Oct 22 '20 at 19:04
  • Does this answer your question? [MS Access / Outlook 2010 - how to choose which account to send email from?](https://stackoverflow.com/questions/17977035/ms-access-outlook-2010-how-to-choose-which-account-to-send-email-from) – June7 Oct 22 '20 at 19:08
  • .SendUsingAccount = OutApp.Session.Accounts.Item(2) triggers the error. I can get this to work with single emails, but not with the loop... – tenebris silentio Oct 22 '20 at 19:12
  • Did you review the linked answer in my other comment? Try setting the account outside the loop. – June7 Oct 22 '20 at 19:25
  • I looked at it, tried it, and I'm not having luck. Maybe I'm missing something. Would you care to output how you mean this? Thanks in advance. – tenebris silentio Oct 23 '20 at 11:26
  • "not having luck" means what - error message, wrong result, nothing happens? Yesterday I referred another poster to the same link and they responded it worked perfectly. Take a look at https://www.utteraccess.com/topics/2059679/posts/2766044. A reply there says Set is not needed. – June7 Oct 23 '20 at 17:02

0 Answers0