30. September 2008
Bob
Office , VBA
Using this Outlook VBA Macro
Over the years, I had noticed that I had appointments from years ago stuck in my calendar, so I wrote this Outlook VBA Macro to export a list of all my appointments to a tab-separated (TSV) file so that I could open it in Microsoft Excel and analyze all of my appointments. (After writing this macro, I wrote my Delete Old Appointments macro to delete old appointments.)
Outlook VBA Macro Example Code
Sub ExportAppointmentsToTsvFile()
Dim objOutlook As Outlook.Application
Dim objNamespace As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim objAppointement As Outlook.AppointmentItem
Dim objNetwork As Object
Dim objFSO As Object
Dim objFile As Object
Dim strUserName As String
Set objOutlook = Application
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)
Set objNetwork = CreateObject("WScript.Network")
strUserName = objNetwork.UserName
If InStr(strUserName, "\") = 0 Then
strUserName = objNetwork.UserDomain & "\" & strUserName
End If
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("c:\outlook-calendar.tsv")
objFile.WriteLine "UserName" & vbTab & _
"AppointementStart" & vbTab & _
"AppointementEnd" & vbTab & _
"AppointementRecurrenceState" & vbTab & _
"AppointementSubject" & vbTab & _
"AppointementSize" & vbTab & _
"AppointementUnRead" & vbTab & _
"AppointementLocation"
For Each objAppointement In objFolder.Items
DoEvents
objFile.WriteLine strUserName & vbTab & _
objAppointement.Start & vbTab & _
objAppointement.End & vbTab & _
objAppointement.RecurrenceState & vbTab & _
objAppointement.Subject & vbTab & _
objAppointement.Size & vbTab & _
objAppointement.UnRead & vbTab & _
objAppointement.Location
Next
MsgBox "Done!"
End Sub