Outlook To Excel (Items Folder)

[brucegn]

Hello everyone, I could use a fresh set of eyes on the below procedure. I outlined the line that seems to be giving trouble. What is happening is when it reaches the PropDescrip line it just stops with no error, even when I step through it. The wierd part is that it puts the information for PropDescrip and the following line into the spreadsheet, even though when stepping thru it it does not appear to do so?

Using Outlook 2002 & Excel 2002 and the procedure is in a Outlook Module.

Any advice or help is greatly appreciated.

Sub GetTaskItems()
Dim objWS As Excel.Worksheet
Dim objApp As Outlook.Application
Dim objNS As NameSpace
Dim CISTaskItems As MAPIFolder
Dim CISTask As Outlook.TaskItem
Dim PropDescrip As UserProperty 'used for accessing custom fields
Dim PropReportSent As UserProperty
Dim i As Integer
On Error GoTo Err_Handler
'UserProperty: Added Custom Fields by User
'Importance = Priority: 0 = Low, 1 = Normal, 2 = High
'Categories: Item(s) that is IN (Error, Would Like, Problem, Correction)
'Owner: Will Cause Security Alert When accessing Owner Attributes
'Status: 0 = Not Started, 1 = In Progress, 2 = Completed, 3 = Waiting On Someone Else, 4 = Deferred
'reference the folder to work with
Set objNS = Application.GetNamespace("MAPI")
Set CISTaskItems = objNS.GetDefaultFolder(olFolderTasks)
Set objWS = GetExcelWS() 'function for returning excel workbook to put data in
'Loop through all Tasks in Tasks Folder
For Each CISTask In CISTaskItems.Items
Set PropDescrip = CISTask.UserProperties("Description")
Set PropReportSent = CISTask.UserProperties("Report Sent")
If CISTask.Status = olTaskComplete Then
If PropReportSent = False Then
objWS.Range("A1").Offset(i, 0) = CISTask.Importance
objWS.Range("B1").Offset(i, 0) = CISTask.Owner
objWS.Range("C1").Offset(i, 0) = CISTask.ContactNames
objWS.Range("D1").Offset(i, 0) = CISTask.Status
objWS.Range("E1").Offset(i, 0) = CISTask.Categories
'####BUGGING OUT HERE
objWS.Range("F1").Offset(i, 0) = PropDescrip
'#######
objWS.Range("G1").Offset(i, 0) = CISTask.DateCompleted
PropReportSent = True
i = i + 1
End If
End If
Next
Exit Sub
Err_Handler:
MsgBox Err.Source & Space(1) & Err.Description
End Sub

 

[brucegn]

Okay, to make it even more frustrating, I chopped up some of the original code and put it into a spreadsheet and it works fine. The below code is what I put into a spreadsheet

Sub GetTaskItems()
Dim objApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim CISTaskItems As MAPIFolder
Dim CISTask As Outlook.TaskItem
Dim i As Integer
Dim PropReportSent As UserProperty
Dim PropDescrip As UserProperty 'used for accessing custom fields
'UserProperty: Added Custom Fields by User
'Importance = Priority: 0 = Low, 1 = Normal, 2 = High
'Categories: Item(s) that is IN (Error, Would Like, Problem, Correction)
'Owner: Will Cause Security Alert When accessing Owner Attributes
'Status: 0 = Not Started, 1 = In Progress, 2 = Completed, 3 = Waiting On Someone Else, 4 = Deferred
'reference the mailbox folder to work with
Set objApp = New Outlook.Application
Set objNS = objApp.Application.GetNamespace("MAPI")
Set CISTaskItems = objNS.GetDefaultFolder(olFolderTasks)
'Loop through all Tasks in Tasks Folder
For Each CISTask In CISTaskItems.Items
Set PropDescrip = CISTask.UserProperties("Description")
Set PropReportSent = CISTask.UserProperties("Report Sent")
If CISTask.Status = olTaskComplete Then
If PropReportSent = False Then
Range("A1").Offset(i, 0).Value = CISTask.Importance
Range("B1").Offset(i, 0).Value = CISTask.Owner
Range("C1").Offset(i, 0).Value = CISTask.ContactNames
Range("D1").Offset(i, 0).Value = CISTask.Status
Range("E1").Offset(i, 0).Value = CISTask.Categories
Range("F1").Offset(i, 0).Value = PropDescrip
Range("G1").Offset(i, 0).Value = CISTask.StartDate
PropReportSent = True
i = i + 1
End If
End If

Next

End Sub

basically the same code, maybe it has something to do with the workbook being called from outlook.

 

[VBAjedi]

Not a very scientific answer, but I've sometimes run into wierd problems when using properties and methods of a range object if the range is qualified with a sheet object (as it is here). Particularly troublesome are the COPY and OFFSET commands. I don't know why.

Anyway, try eliminating the OFFSET with something like:

objWS.Range("F" & (1 + i)) = PropDescrip

Let me know if that does it for you!




VBAjedi

 

[brucegn]

Thanks VBAjedi, but no dice. Same exact thing happened at the same line with same results. Thanks though, I will keep looking at it. I have also tried using Cells() with no luck. Back at it.

Thanks.

 

[PHV]

Set objWS = GetExcelWS()
Can you please post the code of the GetExcelWS ?
You can't instantiate an Excel.Worksheet object without at least an Excel.Application or Excel.Workbook object.
What is the scope of this object(s) and their state (Nothing ?) after the function call ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[brucegn]

Here it is;

Function GetExcelWS() As Excel.Worksheet
Dim objExcel As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet
On Error Resume Next
Set objExcel = New Excel.Application
Set objWB = objExcel.Workbooks.Add
Set GetExcelWS = objWB.Worksheets(1)
objExcel.Visible = True
End Function

 

[SkipVought]

hi,

 Dim PropDescrip As UserProperty 'used for accessing custom fields
 Range("F1").Offset(i, 0).Value = PropDescrip
...

don't think that PropDescrip as defined can be assigned to a cell???


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[brucegn]

Thanks Skip, that did not work either. I am a little perplexed by this, I appreciate everyones help. In the long run, it is not a big issue, I know if works from within Excel and I can make a custom menu to do it, I just was trying to get it to work from within Outlook

 

[PHV]

I would try this:
Sub GetTaskItems()
Dim objExcel As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet
Set objExcel = New Excel.Application
Set objWB = objExcel.Workbooks.Add
Set objWS = objWB.Worksheets(1) ' instead of GetExcelWS()
objExcel.Visible = True
...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

I did not do anything other than raise a question about PropDescrip declared as UserProperty.

Put a break on that statement and do a Watch...

faq707-4594 How to use the Watch Window as a Power Programming Tool

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[brucegn]

Thanks to everyone, I just figured it out it did have to do with the Propdescrip, I changed the line

objWS.Range("F" & (1 + i)).Value = PropDescrip

To

objWS.Range("F" & (1 + i)).Value = PropDescrip.Value

And it worked fine. Sorry for the trouble for what seemed to be a simple fix.

Thanks again,