Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-06-2012, 08:45 PM   #1
Glynne
Newly Registered User
 
Join Date: May 2011
Location: Nelson, New Zealand
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Glynne is on a distinguished road
Run-time error 3251 - Operation is not supported for this type of object

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><font face = Tahoma size = 2>"
HTML = HTML & "<p>Hi there</p>"
HTML = HTML & "<p>Please find attached your Fuelcard invoice for the month ending" & " " & stDate & "." & "</p>"
HTML = HTML & "<p>Our current Terms of Trade took effect from 1/10/11. </p>"
HTML = HTML & "<p>Also, save 10 to 50 basis points on each foreign exchange transaction compared to the big banks. Very low Telegraphic Transfer fees. </p>"
HTML = HTML & "<p>Kind regards</p>"
HTML = HTML & "<p><font color = #C00000 face = Calibri size = 3><b>" & salName & "</font><font color = #595959 face = Calibri size = 3>" & salPosition & "</font></b><br><font color = #595959 face = Calibri size = 2>THE BUYING GROUP LIMITED<br>P: " & salPhone & " | F:" & salFax & "</font></p>"
HTML = HTML & "<p><font color = #595959 face = Calibri size = 2><b>" & salMessage & "</b></font></p>"
HTML = HTML & "<p><font size = 1>" & salFooter & "</font></p>"
HTML = HTML & "</font></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

Glynne is offline   Reply With Quote
Old 08-07-2012, 05:26 AM   #2
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Run-time error 3251 - Operation is not supported for this type of object

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.
vbaInet is offline   Reply With Quote
Old 08-08-2012, 07:58 PM   #3
Glynne
Newly Registered User
 
Join Date: May 2011
Location: Nelson, New Zealand
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Glynne is on a distinguished road
Talking Re: Run-time error 3251 - Operation is not supported for this type of object

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

Glynne is offline   Reply With Quote
Old 08-09-2012, 12:25 AM   #4
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Run-time error 3251 - Operation is not supported for this type of object

So like mentioned, it was returning Null and Null points to nothing. Not because it can't use values from variables.
vbaInet is offline   Reply With Quote
Old 08-09-2012, 10:38 AM   #5
Glynne
Newly Registered User
 
Join Date: May 2011
Location: Nelson, New Zealand
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Glynne is on a distinguished road
Re: Run-time error 3251 - Operation is not supported for this type of object

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
Glynne is offline   Reply With Quote
Old 08-10-2012, 12:44 AM   #6
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Run-time error 3251 - Operation is not supported for this type of object

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

vbaInet is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Operation is not supported for this type of object error when importing a table zvikagat General 1 07-06-2010 12:53 AM
Requested operation requires an ole db session object which is not supported by the c beanlucy Modules & VBA 1 07-15-2009 03:57 PM
run-time error 438 property or method not supported by the object caere Forms 4 07-08-2009 01:31 AM
Error #3251 this process is not available for this type of object odrap Forms 1 03-06-2009 04:16 PM
3251 - Operation is not supported for this type of object. hooi General 3 11-13-2003 06:55 PM




All times are GMT -8. The time now is 07:55 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World