Windows 7: Outlook export VBA macro - type mismatch

0nyx175 · 04 Dec 2012

Hi guys. I've got the following code which works just fine - it exports tasks to an excel spreadsheet. I upgraded to outlook 2010 and windows 7 and suddenly it doesn't work on my PC. It does however work on one of my work-colleagues PC - same references and evertyhing. The references i'm using are

Visual basic for applications
Microsoft outlook 14.0 object library
OLE automation
Microsoft Office 14.0 object library
Microsoft Access 14.0 object library
Microsoft Excel 14.0 object library
Microsoft Word 14.0 object library
Microsoft DAO 3.6 object library
Microsoft Feeds
Microsoft Script Control 1.0
Microsoft Scripting runtime

Code:

Sub export_tasks()

'*******************************************************************************************
'******************************DECLARE VARIABLES********************************************
Dim strReport As String
Dim olnameSpace As Outlook.NameSpace
Dim taskFolder As Outlook.MAPIFolder
Dim tasks As Outlook.Items
Dim tsk As Outlook.TaskItem
Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Dim sht As Excel.Worksheet
 
Dim strMyName As String
Dim x As Integer
Dim y As Integer
'*******************************************************************************************
'*****************************REMOVE EXISTING DATA******************************************
Set exWb = objExcel.Workbooks.Open("P:\systems\Tasks\PDW.xls")
exWb.Sheets("Sheet1").Range("A1:H2500").Select
exWb.Sheets("Sheet1").Range("A1:H250").ClearContents
'*******************************************************************************************
'*****************************REFERENCE WORKBOOKS*******************************************
Set olnameSpace = Application.GetNamespace("MAPI")
Set taskFolder = olnameSpace.GetDefaultFolder(olFolderTasks)
Set tasks = taskFolder.Items
strReport = ""
 
'*******************************************************************************************
'*****************************CREATE THE HEADER*********************************************
  
exWb.Sheets("Sheet1").Cells(1, 1) = "Subject"
exWb.Sheets("Sheet1").Cells(1, 2) = "Start Date"
exWb.Sheets("Sheet1").Cells(1, 3) = "Due Date"
exWb.Sheets("Sheet1").Cells(1, 4) = "Percent Complete"
exWb.Sheets("Sheet1").Cells(1, 5) = "Status"
exWb.Sheets("Sheet1").Cells(1, 6) = "Owner"
exWb.Sheets("Sheet1").Cells(1, 7) = "Requested by"
exWb.Sheets("Sheet1").Cells(1, 8) = "Completed Date"
exWb.Sheets("Sheet1").Cells(1, 10) = "Notes"
y = 2
For x = 1 To tasks.Count
Set tsk = tasks.Item(x)
 
'*******************************************************************************************
'*****************************FILL IN THE DATA**********************************************
'If the task is not set to private then continue
If Not tsk.Sensitivity = olPrivate Then
'Add the data
exWb.Sheets("Sheet1").Cells(y, 1) = tsk.Subject
exWb.Sheets("Sheet1").Cells(y, 2) = tsk.StartDate
exWb.Sheets("Sheet1").Cells(y, 3) = tsk.DueDate
exWb.Sheets("Sheet1").Cells(y, 4) = tsk.PercentComplete
exWb.Sheets("Sheet1").Cells(y, 5) = tsk.Status
exWb.Sheets("Sheet1").Cells(y, 6) = tsk.Owner
exWb.Sheets("Sheet1").Cells(y, 7) = tsk.Delegator
exWb.Sheets("Sheet1").Cells(y, 8) = tsk.DateCompleted
exWb.Sheets("Sheet1").Cells(y, 10) = tsk.Body
    
        
        y = y + 1
 
       End If
 
  Next x
'*******************************************************************************************
'******************************SAVE AND CLOSE***********************************************
 
exWb.Save
exWb.Close
 
Set exWb = Nothing
End Sub

The error that comes up is 'Run Time Error 13 - Typ mismatch' and highlights

Set tsk = tasks.Item(x)

Can anyone help?

Windows 7: Outlook export VBA macro - type mismatch

Outlook export VBA macro - type mismatch problems?