Run-time error 3251 - Operation is not supported for this type of object (1 Viewer)

Glynne · Aug 7, 2012

Hi
I've just upgraded from 2003 to 2010 and split the database into front and back-end.

The following code now produces "Run-time error ‘3251’: Operation is not supported for this type of object" and stops at .Attachments.Add rst!ConsolidatedFile (highlighted red):

Private Sub Command0_Click()

Dim rst As dao.Recordset
Dim stEmail As String
Dim stDate As String
Dim stPath As String
Dim stSubject As String
Dim salPosition As String
Dim stBody As String
Dim HTML As String
Dim OLApp As Object 'New Outlook.Application
Dim OLMsg As Object 'Outlook.MailItem
Dim oShell As Object
Dim salName As String
Dim salMessage As String
Dim salFooter As String
Dim salPhone As String
Dim salCell As String
Dim salFax As String

On Error Resume Next
Set OLApp = CreateObject("Outlook.Application") 'GetObject(, "Outlook.Application") 'Gets Outlook if open
If err.Number > 0 Then
Set oShell = CreateObject("WScript.Shell") 'Opens instance of Outlook
oShell.Run "outlook" 'Outlook is not open so open it
Else
'Outlook is already open
End If
On Error GoTo 0

frmCreatingEmails.Visible = True

Set rst = CurrentDb.OpenRecordset("Select * from tblEmailList", dbOpenDynaset)

salMessage = Nz(Me.SalutationMessage, "")
salName = Nz(Me.SalutationName, "")
salFooter = Nz(Me.SalutationFooter, "")
salCell = Nz(Me.Cell, "")
salPhone = Nz(Me.Phone, "")
salPosition = Nz(Me.Position, "")
salFax = Nz(Me.Fax, "")

If rst.EOF Then
frmCreatingEmails.Visible = False
MsgBox "Nothing to send, please check the dates", vbOKOnly, "Warning"
Else
Do While Not rst.EOF
'stName = rst.Fields("ClientName").Value ' Glynne, this field is not used at all. CC
stPath = rst.Fields("Filepath").Value
stDate = rst.Fields("InvoiceEndDate").Value
stEmail = rst.Fields("EmailAddress").Value

stSubject = "The Buying Group Fuelcard Invoice for month ending " & stDate

HTML = "<html><body>"
HTML = HTML & "Hi there"
HTML = HTML & "Please find attached your Fuelcard invoice for the month ending" & " " & stDate & "." & ""
HTML = HTML & "Our current Terms of Trade took effect from 1/10/11. "
HTML = HTML & "Also, save 10 to 50 basis points on each foreign exchange transaction compared to the big banks. Very low Telegraphic Transfer fees. "
HTML = HTML & "Kind regards"
HTML = HTML & "" & salName & "" & salPosition & " THE BUYING GROUP LIMITED P: " & salPhone & " | F:" & salFax & ""
HTML = HTML & "" & salMessage & ""
HTML = HTML & "" & salFooter & ""
HTML = HTML & "</body></html>"
stBody = HTML

Set OLMsg = OLApp.CreateItem(0)
With OLMsg
.Recipients.Add stEmail
.Subject = stSubject
.Attachments.Add stPath
If Nz(rst!ConsolidatedFile, "X") <> "X" Then
.Attachments.Add rst!ConsolidatedFile
End If
If Nz(rst!ExcelFile, "X") <> "X" Then
.Attachments.Add rst!ExcelFile
End If

'Set body format to HTML
.BodyFormat = 2 'olFormatHTML
.HTMLBody = stBody

.Save

End With
rst.MoveNext
Loop
rst.Close

Set rst = Nothing

frmCreatingEmails.Visible = False

End If

DoCmd.Close acForm, Me.Name
End Sub

vbaInet · Aug 7, 2012

Check the value of rst!ConsolidatedFile. I suspect that the file it's pointing to doesn't exist or it's returning Null or zero-length string.

Glynne · Aug 9, 2012

Thanks vbaInet

I checked the path and the item was there. I posted the path into Explorer and it opened the perfectly.

I've managed to resolve the problem.

You'll notice two lines above the problem code is .Attachments.Add stPath, where stPath is defined as a string.

This piece of code was working perfectly, and yet it was tripping over .Attachments.Add rst!ConsolidatedFile

I started to suspect that it was having problems using a variable that was defined as dao.recordset, so i modified the code as follows:

If Nz(rst!ConsolidatedFile, "X") <> "X" Then
strConsolidatedFile = rst!ConsolidatedFile
.Attachments.Add strConsolidatedFile
End If

Of course I added the necessary Dim statement.

And it worked perfectly. No idea why a variable that is defined as dao.recordset wouldn't work, but at least I got it to work with a string-defined variable.

Cheers
Glynne

vbaInet · Aug 9, 2012

So like mentioned, it was returning Null and Null points to nothing. Not because it can't use values from variables.

Glynne · Aug 9, 2012

Hi vbaInet

It wasn't returning Null, that's the problem. If it was, then the If statement would skip the .attachment.add command.

As it is, there is an entry with a value assigned to the rst variable, but it seems my database wasn't able to process a value assigned to a dao.recordset variable. As soon as I assigned it to a string, it proceeded without fault.

Cheers
Glynne

vbaInet · Aug 10, 2012

I've seen that happen before where it fails when you directly assign a value from a recordset field. What data type is ConsolidatedFile?

Try this:

.Attachments.Add rst!ConsolidatedFile & vbNullString

Run-time error 3251 - Operation is not supported for this type of object (1 Viewer)

Glynne

New member

vbaInet

AWF VIP

Glynne

New member

vbaInet

AWF VIP

Glynne

New member

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread