asked on

VBA to export Outlook Tasks to Excel worksheet

Hi Everyone-

I am in need of assistance in developing some vba script to automate the following process that I take when exporting a Outlook 2007 Task folder to excel and then copy and past to another excel file:

1) In Outlook 2007, I export the following task folder to my desktop: PI Group. The following fields are exported: 1) Subject, 2) Billing Information, 3) Total Work, 4) Start Date, 5) Due Date, 6) Categories, 7) Role, and 8) Notes. The export dates are always 6/1/2008 to 12/31/2008

2) Next, I open the excel file of exported tasks the was created on my desktop

3) I next open another excel file called "RO Time Commitments 2008.xlsm" that is located here: N:\Research Operations\Resources\RO Time Commitments. A message pops up asking if I want to open the file as Read Only, I select "No"

4) I then copy the exported tasks from the file that was created on my desktop and paste them on the "PI Data" worksheet that is in the "RO Time Commitments 2008.xlsm" file. I start the paste on cell A1. After the paste is complete, I sort the PI Data Worksheet by the "DueDate" column. Then I switch to view the "PI Availability" worksheet in the "RO Time Commitments 2008.xlsm" file

That is the process that I would like to automate. A couple of additional things: I would for the button to be run from Outlook. Also, I would like for the script to be able to run if the "RO Time Commitments 2008.xlsm" file is open or not. Thank you so much for taking the time to help me out with this little project. Please let me know if you have any questions. Have a great day!!

David Lee

Hi, bclivell.

I'm not the best person here to be doing Excel work, so I'm going to leave that to one of the other Experts. Here's the Outlook portion of this. This code exports the tasks to Excel. You didn't say where the tasks are, e.g. in their own folder or in the default tasks folder and identified by a group), so this code is exporting everything from the default tasks folder. It's simple to change to export from another folder or to export based on something in the task. I'll just need to know the answer to make the change.

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/2008'"
    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
    excBook.SaveAs "C:\David\PI Group.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

bclivell

ASKER

Thank you for your help. The Task folder, "PI Group", is a sub folder under the default folder.

Thank you again!

ASKER CERTIFIED SOLUTION

David Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bclivell

ASKER

Thank you. This works. Do you know the link of code to have Outlook open an excel file. For example, after it creates the "PI Group.xls" file, then it opens it?

Thanks!

David Lee

The spreadsheet is actually already open. There is no way to script an export in Outlook, you simulate it in code. That means the code above opens the spreadsheet and writes to it. There's no need to close and re-open it if you want to do something with it. You can close and re-open it if you want, perhaps for the sake of modularity, but it's not required. In the code above, the spreadsheet is open until line 30.

bclivell

ASKER

Thank you for helping me with part of the solution. Much appreciated!

David Lee

You're welcome.