MS Access VBA Loop Not working

[MatthewM]

Hi All, I am trying to get my Database to send an email with and attachment, I have that part working. It it the Loop that I am having an issue with. I have the code to look at a folder and attache the correct attachment but my code does not go to the next record. I have the code looking at form that I made, the form uses a Query to pull the correct records.... Please help it sends the email to only the person that I am looking at on the Form and it sends about 10 emails to that person only, and does not move to the next record.

Private Sub EmailSend_Click()

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL As String

Set DB = CurrentDb
strSQL = "select ..."
Set RS = DB.OpenRecordset("Email")
      
Do Until RS.EOF

  
   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.Recipient
   Dim objOutlookAttach As Outlook.Attachment
   Dim DWRFile As String
  
     DWRFile = [psw/sid] & ".pdf"
    

   ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")

   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

   With objOutlookMsg
      ' Add the To recipient(s) to the message.
     Set objOutlookRecip = .Recipients.Add(Me.Email)
      objOutlookRecip.Type = olTo

       ' Set the Subject, Body, and Importance of the message.
      .Subject = "This is a test"
      .Body = "Good Afternoon " & [Contact] & "," & Chr(10) & Chr(10) & "Please see the attached Drinking Water report that was generated on " & Format(Now(), "short date") & "." & Chr(10) & Chr(10) & "Thank You," & Chr(10) & "DSHS Laboratory" & Chr(10) & "Environmental Sciences Branch" & Chr(10) & "100 W.49th St." & Chr(10) & "Austin, Tx 78756" & Chr(10) & "512-776-7587"

      

      ' Add attachments to the message.
      If Not IsMissing(AttachmentPath) Then
         On Error Resume Next
         Set objOutlookAttach = .Attachments.Add("C:\Users\mmessmer828\Desktop\Emailed Reports\Alot\" & DWRFile)
         .Send
           RS.MoveNext
      End If

      ' Resolve each Recipient's name.
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
      

End With
RS.MoveNext

Loop
RS.Close
Set RS = Nothing
Set DB = Nothing

   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing

  
MsgBox "Emails Sent"
 

End Sub

 

[Gasman]

Start with the basics
Walk through your code with F8 after setting a breakpoint and see what the code actually does, not what you think it does.?

Do you really need a Movenext when you send an email? wouldn't the one at the bottom of the loop do that.?

 

[MatthewM]

Start with the basics
Walk through your code with F8 after setting a breakpoint and see what the code actually does, not what you think it does.?

Do you really need a Movenext when you send an email? wouldn't the one at the bottom of the loop do that.?

I have tried that it does not move to the next record with or without the movenext. Both ways it sends out like 25 emails.

 

[plog]

Why do you have a recordset? You never use any data from it.

Set objOutlookRecip = .Recipients.Add(Me.Email)

What do you think that line is doing?

 

[Gasman]

Comment out On Error Resume Next and try again.

 

[MatthewM]

Why do you have a recordset? You never use any data from it.

Set objOutlookRecip = .Recipients.Add(Me.Email)

What do you think that line is doing?

That is where the Email address that I am sending it to goes.

 

[plog]

Correct but not specific enough. Where is the email address coming from?

 

[MatthewM]

Correct but not specific enough. Where is the email address coming from?

it is coming off a query that i have set up on my database. I use a form so that I can remove emails that I would not want to send them to. It is a split form that I use and I filter some of the emails that I may not need to send to those people, i use the query to filter out people that have not given me an email.

 

[plog]

Nope throw all the background info away, discard whatever it is you are trying to do. Step back and just look at this code likes its the first time you've ever seen it:

Set objOutlookRecip = .Recipients.Add(Me.Email)

What does Me.Email really do?

Hint:

Google Search

Also, I asked 2 questions in my first post--the first one is why are you even using a recordset because you never use the data from it.

 

[MatthewM]

Nope throw all the background info away, discard whatever it is you are trying to do. Step back and just look at this code likes its the first time you've ever seen it:

Set objOutlookRecip = .Recipients.Add(Me.Email)

What does Me.Email really do?

Hint:

Google Search

Also, I asked 2 questions in my first post--the first one is why are you even using a recordset because you never use the data from it.

Sorry about miss the recordset part, I tried to find a few email codes that would work and that is the one that i found and i just kept what worked. As for the me. i get that it looks at the form, which i am using but i would need it to go to the next record after it sends that email. If I don't use me. then what would I use? Would I have it look at the Query that I am using to populate the form then?

 

[plog]

I am assuming the recordset has an email field that you want to use to send to, correct? Then you need to not reference the form but the field of the recordset that contains that email value:


Set objOutlookRecip = .Recipients.Add(Rs.Email)

 

[MatthewM]

Thank you all I got it to work with the help of plog.