Option Explicit
'Behind ThisOutlookSession
'Add a reference to MS Excel xx.0 Object Library
Public WithEvents oMnuExport As Office.CommandBarButton
Public WithEvents oTbbExport As Office.CommandBarButton
Private Sub oTbbExport_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'invoke the "Export Public Tasks" menu item from our toolbar button click event
oMnuExport_Click Ctrl, CancelDefault
End Sub
Private Sub oMnuExport_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'\\Public Folders\All Public Folders\Arbitron\RSS\DTXARB\Western Div Projects
Dim oWestern As Outlook.MAPIFolder
Dim oTask As Outlook.TaskItem
Dim oApp As Excel.Application
Dim oWB As Excel.Workbook
Dim oSht As Excel.Worksheet
Dim o As Long
'Navigate to the source folder and set out object variable to it.
Set oWestern = Application.GetNamespace("MAPI").Folders("Public Folders").Folders("All Public Folders").Folders("Arbitron").Folders("RSS").Folders("DTXARB").Folders("Western Div Projects")
'We are now connected to our source folder
'Create an instance of Excel for automation
Set oApp = New Excel.Application
'Add a new blank workbook to the collection
Set oWB = oApp.Workbooks.Add
'Initialize an object variable to the first sheet
Set oSht = oWB.Sheets(1)
'Setup column headers
oSht.Cells(1, 1).Value = "StartDate"
oSht.Cells(1, 2).Value = "CreationTime"
oSht.Cells(1, 3).Value = "Importance"
oSht.Cells(1, 4).Value = "Subject"
oSht.Cells(1, 5).Value = "Body"
'Loop through the Outlook tasks in the folder
'writting the task data out to Excel
For o = 1 To oWestern.Items.Count
Set oTask = oWestern.Items.Item(o)
oSht.Cells(o + 1, 1).Value = oTask.StartDate
oSht.Cells(o + 1, 2).Value = oTask.CreationTime
oSht.Cells(o + 1, 3).Value = oTask.Importance
oSht.Cells(o + 1, 4).Value = oTask.Subject
oSht.Cells(o + 1, 5).Value = oTask.Body
DoEvents
Next
oApp.Visible = True
Set oSht = Nothing
Set oWB = Nothing
Set oApp = Nothing
End Sub
Private Sub SyncMnuExportButton(btn As Office.CommandBarButton)
'Sync up the event to the object variable
Set oMnuExport = btn
If btn Is Nothing Then
MsgBox "Sync. of '" & btn.Caption & "' button event failed!", vbCritical + vbOKOnly
End If
End Sub
Private Sub SyncTbbExportButton(btn As Office.CommandBarButton)
'Sync up the event to the object variable
Set oTbbExport = btn
If btn Is Nothing Then
MsgBox "Sync. of '" & btn.Caption & "' button event failed!", vbCritical + vbOKOnly
End If
End Sub
Private Sub Application_MAPILogonComplete()
'Setup and add a new menu item to the Tools menu to enable us to invoke our custom Export method
Dim oCBmnuTools As Office.CommandBarPopup
Dim oCBmnuExport As Office.CommandBarButton
Dim oCBtbbStd As Office.CommandBar
Dim oCBtbbExport As Office.CommandBarButton
'Add a menu item if its not already there
Set oCBmnuTools = Application.ActiveExplorer.CommandBars("Menu Bar").Controls("&Tools")
Set oCBmnuExport = Application.ActiveExplorer.CommandBars("Menu Bar").FindControl(msoControlButton, 1, "888", True, True)
If TypeName(oCBmnuExport) = "Nothing" Then
Set oCBmnuExport = oCBmnuTools.Controls.Add(msoControlButton, 1, "888", , True)
End If
With oCBmnuExport
.BeginGroup = True
.Caption = "Export Public Tasks"
.Enabled = True
.Style = msoControlCustom
.Tag = "888"
.Visible = True
End With
Call SyncMnuExportButton(oCBmnuExport)
'Add the toolbar button
Set oCBtbbStd = Application.ActiveExplorer.CommandBars("Standard")
Set oCBtbbExport = oCBtbbStd.FindControl(msoControlButton, 1, "889", True, True)
If TypeName(oCBtbbExport) = "Nothing" Then
Set oCBtbbExport = oCBtbbStd.Controls.Add(msoControlButton, 1, "888", , True)
End If
With oCBtbbExport
.BeginGroup = True
.Enabled = True
.FaceId = 263 'Excel Icon
'Option: Use icon only
.Style = msoButtonIcon
'Option: Use icon and text
'.Style = msoButtonIconAndCaption
.ToolTipText = "Export Public Tasks"
.Tag = "889"
.Visible = True
End With
Call SyncTbbExportButton(oCBtbbExport)
End Sub
