Object Variable or With Block Variable Not Set (1 Viewer)

[Novice1]

I'm at my wits end ... why I'm I getting this error (Object Variable or With Block Variable Not Set)?

Private Sub Command415_Click()

On Error GoTo errhandlers:

Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem
Dim rs As Recordset
Dim customerEmail As String
Dim n As Integer

If oOutlook Is Nothing Then
Set oOutlook = New Outlook.Application
End If
Set omailitem = oOutlook.CreateItem(olMailItem)
With oEmailItem
Set rs = CurrentDb.OpenRecordset("Select * from tblOutbounds")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
If IsNull(rs!EMail) Then
rs.MoveNext
Else
customerEmail = customerEmail & rs!EMail & ";"
.To = customerEmail
rs.MoveNext
End If

Loop

Else
MsgBox "No customers on the list"

End If
.CC = ""
.Subject = "Attached files"

For n = 0 To Me.List3.ListCount - 1
.Attachments.Add (Me.List3.ItemData)
Next n
.Display

End With
Exit_errhandlers:
Exit Sub

errhandlers:
MsgBox Err.Description, vbCritical
Resume Exit_errhandlers:

End Sub

 

[sneuberg]

Edited: Ignore this

Instead of

Set rs = CurrentDb.OpenRecordset("Select * from tblOutbounds")

Try

Dim db as Database
Set db = CurrentDB
Set rs = db.OpenRecordset("Select * from tblOutbounds")

 

[sneuberg]

Set omailitem = oOutlook.CreateItem(olMailItem)

should be

Set oEmailItem = oOutlook.CreateItem(olMailItem)

Suggest putting Option Explicit in your modules. If you had it you would have seen this during compile. You can have Access put this in for you. Go to the VBA editor, click Tools, Options and in the Editor tab click on Require Variable Declarations.

 

[Galaxiom]

Instead of

Set rs = CurrentDb.OpenRecordset("Select * from tblOutbounds")

Try

Dim db as Database
Set db = CurrentDB
Set rs = db.OpenRecordset("Select * from tblOutbounds")

I know that is how it is most often seen in Microsoft's help but that won't make any difference. Setting an object to the return from CurrentDb is only necessary when something down the chain from CurrentDb needs to be referred to again.

Despite appearances, CurrentDb is not an object in itself but function (or a Method) of the Application object that returns an object. As such each time it is called a (same but) different object is returned.