How to Quickly Export Information of the Tasks & Appointments in a Specific Date Range to an Excel File

If you wish to export the primary information of all the tasks and appointments in a specific date range to an Excel file, you can use Outlook VBA to achieve it in quick time. This article will tell you the detailed steps.

When you need to export something from Outlook, you will firstly consider using the native “Import and Export” feature in your Outlook. However, in this way, you can only export the items in a selected folder, let alone export items in a specific date range. That is to say, for instance you aren’t allowed to export both the tasks and appointments in one go. Undoubtedly, it is quite troublesome. But fortunately, you can utilize Outlook VBA to get it quickly. Here we will introduce the elaborate steps and show the concrete VBA codes to you.

Export Information of the Tasks & Appointments in a Specific Date Range to an Excel File

Export Tasks & Appointments in a Specific Date Range to an Excel File

  1. At the very outset, start your Outlook as normal.
  2. Then in the main Outlook window, press “Alt + F11” key shortcuts.
  3. Next you will get into Outlook VBA editor.
  4. Subsequently, you need to create a new module or open a not-in-used one.
  5. Then copy and paste the following VBA codes into the module.
Sub ExportTasksAppointmentsinSpecificDateRange()
    Dim objTasks, objRestrictTasks As Outlook.Items
    Dim objAppointments, objRestrictAppointments As Outlook.Items
    Dim objItem As Object
    Dim strFilter As String
    Dim strStartDate, strEndDate As String
    Dim objExcelApp As Excel.Application
    Dim objExcelWorkbook As Excel.Workbook
    Dim objExcelWorksheet1, objExcelWorksheet2 As Excel.Worksheet
    Dim nRow As Integer
    Dim strFilePath As String
 
    strStartDate = InputBox("Enter the start date (format: yyyy/mm/dd)")
    strEndDate = InputBox("Enter the end date(format: yyyy/mm/dd)")
 
    'Get the tasks in the specific date range
    Set objTasks = Application.Session.GetDefaultFolder(olFolderTasks).Items
    strFilter = "[StartDate] >= " & Chr(34) & strStartDate & Chr(34) & " AND [DueDate] <= " & Chr(34) & strEndDate & Chr(34)
    Set objRestrictTasks = objTasks.Restrict(strFilter)
 
    'Get the appointments in the specific date range
    Set objAppointments = Application.Session.GetDefaultFolder(olFolderCalendar).Items
    strFilter = "[Start] >= " & Chr(34) & strStartDate & " 00:00 AM" & Chr(34) & " AND [End] <= " & Chr(34) & strEndDate & " 11:59 PM" & Chr(34)
    Set objRestrictAppointments = objAppointments.Restrict(strFilter)
 
    Set objExcelApp = CreateObject("Excel.Application")
    Set objExcelWorkbook = objExcelApp.Workbooks.Add
 
    'Export the tasks to the first worksheet
    Set objExcelWorksheet1 = objExcelWorkbook.Worksheets(1)
    With objExcelWorksheet1
         .Cells(1, 1) = "Task Subject"
         .Cells(1, 2) = "Start Date"
         .Cells(1, 3) = "Due Date"
         .Cells(1, 4) = "Duration"
    End With
 
    nRow = 2
    For Each objItem In objRestrictTasks
        With objExcelWorksheet1
             .Name = "Tasks"
             .Cells(nRow, 1) = objItem.Subject
             .Cells(nRow, 2) = objItem.StartDate
             .Cells(nRow, 3) = objItem.DueDate
             .Cells(nRow, 4) = objItem.ActualWork
        End With
        nRow = nRow + 1
    Next
 
    objExcelWorksheet1.Columns("A:D").AutoFit
 
    'Export the appointments to the second worksheet
    Set objExcelWorksheet2 = objExcelWorkbook.Worksheets(2)
 
    With objExcelWorksheet2
         .Name = "Appointments"
         .Cells(1, 1) = "Appointment Subject"
         .Cells(1, 2) = "Start Time"
         .Cells(1, 3) = "End Time"
         .Cells(1, 4) = "Duration"
         .Cells(1, 5) = "Location"
    End With
 
    nRow = 2
    For Each objItem In objRestrictAppointments
        With objExcelWorksheet2
             .Cells(nRow, 1) = objItem.Subject
             .Cells(nRow, 2) = objItem.Start
             .Cells(nRow, 3) = objItem.End
             .Cells(nRow, 4) = objItem.Duration
             .Cells(nRow, 5) = objItem.Location
        End With
        nRow = nRow + 1
    Next
 
    objExcelWorksheet2.Columns("A:E").AutoFit

    'Save the excel workbook
    strFilePath = "C:\Report\Tasks & Appointments from " & Format(strStartDate, "yyyy-mm-dd") & " to " & Format(strEndDate, "yyyy-mm-dd") & ".xlsx"
    objExcelWorkbook.Close True, strFilePath
 
    'Notify you of the export complete
    MsgBox ("Export Complete!")
End Sub

VBA Codes - Export Tasks & Appointments in a Specific Date Range to an Excel file

  1. After that, change your Outlook macro security level to low.
  2. Finally you can have a try.
  • Firstly, click on “Run” icon in the toolbar of VBA editor window to initiate the new macro.Initiate the new macro
  • Then you need to specify the date range, entering the specific start date and end date in the format “yyyy/mm/dd”, such as “2017/3/16”.Specify date range
  • Press two “OK” to confirm the date range.
  • After exporting successfully, you will get a prompt.Export Complete
  • Lastly you can go to the predetermined saving folder to find the specific Excel file. Find the specific Excel file

Handle Painful PST Issues

Due to the fact that Outlook PST is prone to error, you may have come across a lot of issues in your Outlook. Hence, you had better back up your PST data on regular basis. Moreover, it is recommended to prepare a PST repair tool in advance, such as DataNumen Outlook Repair, which will definitely come in handy one day.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair sql and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.