Object Variable or With Block Variable not set 2

[Cleis]

Can someone please tell me what I'm doing wrong? I'm getting "Object Variable or With Block Variable not set"

Thanks!!

Dim db As Database
Dim rs As DAO.Recordset


Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set db = CurrentDb
Set rs = db.OpenRecordset("qry_EmailAddress")

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)


rs.MoveFirst
Do Until rs.EOF

With objEmail
.To = rs!Email
.Subject = rs!MemberName
.HTMLBody = "Test<br><br>"
.Attachments.Add (rs!FilePath)
.Send
End With

Set objEmail = Nothing

rs.MoveNext

Loop

 

[bubba100]

Are you getting the error message when the routine begins and which line is highlighted?

 

[Cleis]

Hi Bubba100

The code will go through the 1st time and send the e-mail. However on the loop I only get to

.To = rs!Email

Should I be declaring the fields of the query??? I've tried that as well to no avail?


Thanks for any input

 

[PWise]

move line

Dim db As Database
Dim rs As DAO.Recordset


Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qry_EmailAddress")
    
    Set objOutlook = CreateObject("Outlook.application")
    [COLOR=green]'Set objEmail = objOutlook.CreateItem(olMailItem)[/color]


    rs.MoveFirst
    Do Until rs.EOF
    [COLOR=Red]Set objEmail = objOutlook.CreateItem(olMailItem)[/color]
        With objEmail
          .To = rs!Email
          .Subject = rs!MemberName
          .HTMLBody = "Test<br><br>"
          .Attachments.Add (rs!FilePath)
          .Send
        End With
        
        Set objEmail = Nothing
    
    rs.MoveNext
    
    Loop

 

[bubba100]

Try moving "Set objEmail = Nothing" to outside the loop.

 

[Cleis]

pwise (Programmer)and bubba100 (TechnicalUser)

Thanks for your input and help! Two stars!!