Export outlook task to Excel

Karl001
Karl001 used Ask the Experts™
on
Hi,
I tried to export outlook task in Excel by “File-Export option”.
But the field I created are not exported.
How can I export to excel, or import from excel all outlook task fields?
Thanks
Carol
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Clark KentSoftware Engineer cum Blogger
Top Expert 2014

Commented:
Hello Karl,

I would like to tell you that Outlook does not export custom fields or user defined fields but you can do the rest of the thigns by following given method.

Open Outlook and follow the given instructions:

File >> Open >> Import >> Export to a File (Next) >> Comma Seperated Values (Windows - Next) >> Tasks (Next) >> Browse (to save CSV file) >> 

Feel free to ask further!!!

Regards
Clark Kent
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
you can manipulate task fields information with VBA.

BlueDevilFan has answered similar question on EE link is here http://www.experts-exchange.com/questions/23533230/VBA-to-export-Outlook-Tasks-to-Excel-worksheet.html

he also has a blog with very useful code exporting tasks from outlook to excel
this is also another way to do it.


Select the tasks you want export in excel. (If you want to export all tasks then press "ctrl + A") to select all tasks in your tasks folder and then drag it to excel or you can copy (Ctrl + C) go to excel sheet and paste it there (Ctrl + v).

Best,
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi ProfessorJimJam,

I tried to export my own tasks field, but It doesn't work.

Do you know if it's possible ?

Thanks,
Carol
Hi,

The code is amended it will work. please copy the code and open outlook click (Alt + F11) insert a module and paste it.

The file will be saved in your desktop, File name Outlook Tasks.

before you run the code please ensure that microsoft Excel 14.0 object library is ticked as shown in the screen shot. to check click Tools--References .Microsoft-excel.PNG


[ Sub ExportTasks2Excel()
    Dim olkFolder As Outlook.MAPIFolder, _
        olkList As Outlook.Items, _
        olkTask As Outlook.TaskItem, _
        excApp As Object, _
        excBook As Object, _
        excSheet As Object, _
        intRow As Integer, _
        strQuery As String
    Set excApp = CreateObject("Excel.Application")
    Set excBook = excApp.Workbooks.Add
    Set excSheet = excBook.ActiveSheet
    intRow = 1
    Set olkFolder = Session.GetDefaultFolder(olFolderTasks)
    strQuery = "[DueDate] >= '6/1/2008' AND [DueDate] <= '12/31/2010'"
    Set olkList = olkFolder.Items.Restrict(strQuery)
    For Each olkTask In olkList
        excSheet.Cells(intRow, 1) = olkTask.Subject
        excSheet.Cells(intRow, 2) = olkTask.BillingInformation
        excSheet.Cells(intRow, 3) = olkTask.TotalWork
        excSheet.Cells(intRow, 4) = olkTask.StartDate
        excSheet.Cells(intRow, 5) = olkTask.DueDate
        excSheet.Cells(intRow, 6) = olkTask.Categories
        excSheet.Cells(intRow, 7) = olkTask.Role
        excSheet.Cells(intRow, 8) = olkTask.Body
        intRow = intRow + 1
    Next
    'Change the file path and name on the following line
    On Error Resume Next
    excBook.SaveAs "\\C:\Users\Public\Desktop\Outlook task.xlsx"
    excBook.Close False
    Set excSheet = Nothing
    Set excBook = Nothing
    Set excApp = Nothing
    Set olkTask = Nothing
    Set olkList = Nothing
    Set olkFolder = Nothing
End Sub
]
'The file path is changed to user account:  "C:\Documents and Settings\" & UID & "\Outlook Tasks.xls" ' UID = User ID.

Sub ExportTasks2Excel()
    Dim olkFolder As Outlook.MAPIFolder, _
        olkList As Outlook.Items, _
        olkTask As Outlook.TaskItem, _
        excApp As Object, _
        excBook As Object, _
        excSheet As Object, _
        intRow As Integer, _
        strQuery As String
    Set excApp = CreateObject("Excel.Application")
    Set excBook = excApp.Workbooks.Add
    Set excSheet = excBook.ActiveSheet
    intRow = 1
    Set olkFolder = Session.GetDefaultFolder(olFolderTasks)
    strQuery = "[DueDate] >= '6/1/2008' AND [DueDate] <= '12/31/2010'"
    Set olkList = olkFolder.Items.Restrict(strQuery)
    For Each olkTask In olkList
        excSheet.Cells(intRow, 1) = olkTask.Subject
        excSheet.Cells(intRow, 2) = olkTask.BillingInformation
        excSheet.Cells(intRow, 3) = olkTask.TotalWork
        excSheet.Cells(intRow, 4) = olkTask.StartDate
        excSheet.Cells(intRow, 5) = olkTask.DueDate
        excSheet.Cells(intRow, 6) = olkTask.Categories
        excSheet.Cells(intRow, 7) = olkTask.Role
        excSheet.Cells(intRow, 8) = olkTask.Body
        intRow = intRow + 1
    Next
    'Change the file path and name on the following line
    UID = Environ("Username")
   
  
    excBook.SaveAs "C:\Documents and Settings\" & UID & "\Outlook Tasks.xls"
    
    excBook.Close False
    Set excSheet = Nothing
    Set excBook = Nothing
    Set excApp = Nothing
    Set olkTask = Nothing
    Set olkList = Nothing
    Set olkFolder = Nothing
End Sub

Open in new window

Author

Commented:
Hi,

I  tested BlueDevilFan code gave by ProfessorJimJam, and it works.      
Excel amusant, it’s the same code as yours.

My problem is the result, I got only default task fields ( subject, billinginformation, ...) and not my custom  field.

As an example, I created  "ProjectName" custom field task.
In the code, I added the line:     excSheet.Cells(intRow, 9) = olkTask.ProjectName

And it doesn’t work, I got all others fields but not my custom field.
Hi,

I have amended the code which was written by bluedevil as it was given error on line 17 and 34.

Please find below code the custom field ("ProjectName") is now included. I tested the code and it works perfectly.


Sub ExportTasks2Excel()

Dim objProperty As Outlook.UserProperty 'Defining the UDF Item

    Dim olkFolder As Outlook.MAPIFolder, _
        olkList As Outlook.Items, _
        olkTask As Outlook.TaskItem, _
        excApp As Object, _
        excBook As Object, _
        excSheet As Object, _
        intRow As Integer, _
        strQuery As String
    Set excApp = CreateObject("Excel.Application")
   
    Set excBook = excApp.Workbooks.Add
    Set excSheet = excBook.ActiveSheet
    intRow = 1
    Set olkFolder = Session.GetDefaultFolder(olFolderTasks)
    strQuery = "[DueDate] >= '6/1/2008' AND [DueDate] <= '12/31/2015'"
    Set olkList = olkFolder.Items.Restrict(strQuery)
    For Each olkTask In olkList
        excSheet.Cells(intRow, 1) = olkTask.Subject
        excSheet.Cells(intRow, 2) = olkTask.BillingInformation
        excSheet.Cells(intRow, 3) = olkTask.TotalWork
        excSheet.Cells(intRow, 4) = olkTask.StartDate
        excSheet.Cells(intRow, 5) = olkTask.DueDate
        excSheet.Cells(intRow, 6) = olkTask.Categories
        excSheet.Cells(intRow, 7) = olkTask.Role
        excSheet.Cells(intRow, 8) = olkTask.Body
        
        Set prop = olkTask.UserProperties.Item("ProjectName")
If Not (prop Is Nothing) Then
  excSheet.Cells(intRow, 9) = prop.Value
End If
        
        intRow = intRow + 1
    Next
    'Change the file path and name on the following line
    UID = Environ("Username")
   
  
    excBook.SaveAs "C:\Documents and Settings\" & UID & "\Outlook Tasks.xls"
    
    excBook.Close False
    Set excSheet = Nothing
    Set excBook = Nothing
    Set excApp = Nothing
    Set olkTask = Nothing
    Set olkList = Nothing
    Set olkFolder = Nothing
End Sub

Open in new window

Author

Commented:
Yeh! you made my day

Now I got in the exportation my custom field.

Thanks
Glad that it was helpful:)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial