Reading attachment in the outlook

[kbsudhir]

Hi All


I am checking for the attachments of excel type in the mail & saving them in a temporary location & then opening them & searching for a string in column B of the sheet(1).

Most of the time it works nicely but there are instances when I am getting the following error:

Runtime error '91':
Object variable or with block variable is not set.

When I check the excel the required file is open. I just stop the macro & srun again, now its working fine. I am not making any changes to the code.

Hence I am to understand why I am getting this error.

I getting this error in the following line:
If Trim(ActiveSheet.Range("B" & snt).Value) <> "" Then

Below is my complete code.



Dim OlItems As Outlook.Items
Dim olItem As Outlook.MailItem
Dim olFolder As Outlook.MAPIFolder
Dim KillFile As String
Dim oxlApp As New Excel.Application
Dim rowcnt, linecnt As Long
Dim X, y As Long
Dim strFileName As String
Dim strFile As String
Dim Vcode As String
Dim cn As ADODB.Connection, rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\My Documents\Team\TeamData.mdb;"

'open a recordset
Set rs = New ADODB.Recordset

'providing the path of the inbox
Set olFolder = Application.GetNamespace("MAPI").Folders("Mailbox - Sudhir").Folders("Inbox")

'providing the object of the mails in the inbox
Set OlItems = olFolder.Items
For Each olItem In OlItems

X = olItem.Attachments.Count
If X > 0 Then
For y = 1 To X

'getting the attachment name
strFileName = olItem.Attachments.Item(y).FileName

'verifying whether the attachment is an excel.
If Right$(strFileName, 3) = "xls" Or Right$(strFileName, 4) = "xlsx" Then

'saving the attachment in a temporary location
olItem.Attachments.Item(y).SaveAsFile "C:\My Documents\Temp\" & olItem.Attachments.Item(y).FileName

'getting the path of the attachment saved
strFile = "C:\My Documents\Temp\" & olItem.Attachments.Item(y).FileName


If Len(Dir(strFile, vbDirectory)) <> 0 Then

'making the excel object visible
oxlApp.Visible = True

'Opening the attachment
oxlApp.Workbooks.Open (strFile)

End If

'activating the file opened
oxlApp.Workbooks(strFileName).Worksheets(1).Activate

snt = 6

Do Until snt > 100

'seraching for the code

If Trim(ActiveSheet.Range("B" & snt).Value) <> "" Then

If Len(Trim(ActiveSheet.Range("B" & snt).Value)) = 4 Or Len(Trim(ActiveSheet.Range("B" & snt).Value)) = 3 Then

Vcode = Trim(ActiveSheet.Range("B" & snt).Value)
If Len(Vcode) = 3 Then
Vcode = "0" & Vcode
End If
End If

'Getting the handler for that Code
rs.Open "SELECT CodeList.[Handler], CodeList.[Type] FROM CodeList WHERE (((CodeList.VC)='" & Vcode & "')) GROUP BY CodeList.[Handler], CodeList.[Type];", cn, adOpenStatic, adLockOptimistic

'Assinging the mail to the handler
If rs.RecordCount > 0 Then
olItem.Categories = rs(0).Value
End If

rowcnt = 6

Vcode = ""
rs.Close

GoTo h
End If

snt = snt + 1
Loop
h:


Next y
End If

olItem.Save

Next


Please guide.

:doh: :doh:

Thanks
Sudhir

[JP2112]

My first suggestion is never use the New keyword in a declaration statement in VBA.

Dim oxlApp As New Excel.Application

It should be
Dim oxlApp As Excel.Application

And then when you need the object reference:

Set oxlApp = New Excel.Application

(Although my understanding is that GetObject is faster than New.)


Second, you've used "ActiveSheet" which is where VBA is probably confused. Instead, set an object reference to the Excel.Worksheet object you want to work with, and refer to that instead.

HTH



Hi All


I am checking for the attachments of excel type in the mail & saving them in a temporary location & then opening them & searching for a string in column B of the sheet(1).

Most of the time it works nicely but there are instances when I am getting the following error:

Runtime error '91':
Object variable or with block variable is not set.

When I check the excel the required file is open. I just stop the macro & srun again, now its working fine. I am not making any changes to the code.

Hence I am to understand why I am getting this error.

I getting this error in the following line:
If Trim(ActiveSheet.Range("B" & snt).Value) <> "" Then
Below is my complete code.