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 Tasks & Appointments in a Specific Date Range to an Excel File
- At the very outset, start your Outlook as normal.
- Then in the main Outlook window, press “Alt + F11” key shortcuts.
- Next you will get into Outlook VBA editor.
- Subsequently, you need to create a new module or open a not-in-used one.
- 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
- After that, change your Outlook macro security level to low.
- Finally you can have a try.
- Firstly, click on “Run” icon in the toolbar of VBA editor window to 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”.
- Press two “OK” to confirm the date range.
- After exporting successfully, you will get a prompt.
- Lastly you can go to the predetermined saving folder to 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.
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