Exporting from a Custom Form in Outlook 2003 into Excel

That is awesome!

Ok, I think I have every basic feature that they would want and tweaked it to look more complete and detailed.

Simply copy/paste all this code into your ThisOutlookSession. Then unzip the UserForm1 files anywhere to your hard drive. Then click File > Import on the VBA IDE menus and browse to where you have unzipped the UserForm1 files. You should then see my UserForm1 as shown in the picture below.

Save the code and restart Outlook. You should then get the "Export to Excel" toolbar button and menu item. When clicked they will bring up the UserForm and allow you to make your choices. For testing at home I set up a few comments in the code so you can make the necessary comment changes to have it all set for when your at work. The changes are the setup of the oWestern folder object and the Task's custom UserProperties. ;)

:D

Let me know if the progressbar and date teim pickers show in your toolbox and on the UserForm. I can help you fix that if they dont.



http://vbforums.com/attachment.php?attachmentid=50009

VB Code:

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 dStartDate As Date
Private dEndDate As Date
Private bCancelExport As Boolean
Private bExportAll As Boolean
 
Public Property Let StartDate(ByVal sDate As Date)
    dStartDate = sDate
End Property
 
Public Property Get StartDate() As Date
    StartDate = dStartDate
End Property
 
Public Property Let EndDate(ByVal sDate As Date)
    dEndDate = sDate
End Property
 
Public Property Get EndDate() As Date
    EndDate = dEndDate
End Property
 
Public Property Let ExportAll(ByVal bTrue As Boolean)
    bExportAll = bTrue
End Property
 
Public Property Get ExportAll() As Boolean
    ExportAll = bExportAll
End Property
 
Public Property Let CancelExport(ByVal bCancel As Boolean)
    bCancelExport = bCancel
End Property
 
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 oItems As Outlook.Items
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSht As Excel.Worksheet
    Dim sSQL As String
    Dim o As Long
    Dim lHwnd As Long
    
    'Show our date picker userform
    UserForm1.Show vbModal
    'Check if Cancel was pressed on date picker form.
    If bCancelExport = True Then
        Exit Sub
    End If
    UserForm1.MousePointer = fmMousePointerHourGlass
    'Navigate to the source folder and set out object variable to it.
    'Uncomment for when your at work.
    'Set oWestern = Application.GetNamespace("MAPI").Folders("Public Folders").Folders("All Public Folders").Folders("Arbitron").Folders("RSS").Folders("DTXARB").Folders("Western Div Projects")
    'For home testing on default task folder.
    Set oWestern = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderTasks)
    '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
    oApp.Columns.WrapText = False
    'Initialize an object variable to the first sheet
    Set oSht = oWB.Sheets(1)
    'Setup column headers
    oSht.Cells(1, 1).Font.Bold = True
    oSht.Cells(1, 1).Value = "StartDate"
    oSht.Cells(1, 2).Font.Bold = True
    oSht.Cells(1, 2).Value = "CreationTime"
    oSht.Cells(1, 3).Font.Bold = True
    oSht.Cells(1, 3).Value = "Importance"
    oSht.Cells(1, 4).Font.Bold = True
    oSht.Cells(1, 4).Value = "Subject"
    oSht.Cells(1, 5).Font.Bold = True
    oSht.Cells(1, 5).Value = "Body"
    oSht.Cells(1, 6).Font.Bold = True
    oSht.Cells(1, 6).Value = "AdditionalText"
    oSht.Cells(1, 7).Font.Bold = True
    oSht.Cells(1, 7).Value = "CallsGroup"
    'Filter the items in the desired folder by start date and / or Created
    'writting the task data out to Excel
    'Also, if the export all is checked then we will want to run a different sql statement filter.
    If ExportAll = False Then
        'Take a filtered range
        sSQL = "(([Start] >= '" & StartDate & " 12:00 AM') AND ([Start] < '" & DateAdd("d", 1, EndDate) & " 12:00 AM'))"
        sSQL = sSQL & " OR "
        sSQL = sSQL & "(([Created] >= '" & StartDate & " 12:00 AM') AND ([Created] < '" & DateAdd("d", 1, EndDate) & " 12:00 AM'))"
        Set oItems = oWestern.Items.Restrict(sSQL)
        oItems.Sort "[Start]", False
    Else
        'Take all items in the folders collection of items.
        Set oItems = oWestern.Items
        oItems.Sort "[Created]", False
    End If
    UserForm1.prbProgress.Max = IIf(oItems.Count = 0, 1, oItems.Count)
    For o = 1 To oItems.Count
        If o = 1 Then
            Set oTask = oItems.GetFirst
        Else
            Set oTask = oItems.GetNext
        End If
        oSht.Cells(o + 1, 1).Value = IIf(DateDiff("yyyy", #1/1/1900#, oTask.StartDate) > 100, "None", oTask.StartDate)
        oSht.Cells(o + 1, 2).Value = oTask.CreationTime
        oSht.Cells(o + 1, 3).Value = IIf(oTask.Importance = olImportanceHigh, "ImportanceHigh", IIf(oTask.Importance = olImportanceLow, "ImportanceLow", "ImportanceNormal"))
        oSht.Cells(o + 1, 4).Value = oTask.Subject
        oSht.Cells(o + 1, 5).Value = oTask.Body
        'Commented for testing at home...
'        oSht.Cells(o + 1, 6).Value = oTask.UserProperties.Item("AdditionalText").Value
'        oSht.Cells(o + 1, 7).Value = oTask.UserProperties.Item("CallsGroup").Value
        DoEvents
        UserForm1.prbProgress.Value = UserForm1.prbProgress.Value + 1
    Next
    oApp.Columns("E:E").ColumnWidth = 100
    oApp.Columns.WrapText = True
    oApp.Columns.AutoFit
    oApp.Rows.AutoFit
    MsgBox "Done Exporting (" & oItems.Count & ") Public Task(s) to Excel!", vbOKOnly + vbInformation
    oApp.Visible = True
    UserForm1.MousePointer = fmMousePointerDefault
    Unload UserForm1
    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
        .Caption = "Export Public Tasks"
        .Enabled = True
        .FaceId = 263 'Excel Icon
        'Option: Use icon only
        '.Style = msoButtonIcon
        'Option: Use icon and text
        .Style = msoButtonIconAndCaption
        .TooltipText = "Export Public Tasks to a new Excel Workbook"
        .Tag = "889"
        .Visible = True
    End With
    Call SyncTbbExportButton(oCBtbbExport)
End Sub

Awesome! Can't wait to try it. Will let you know how it goes!

Just realized that you may be wanting to install this on other employees systems and if so are they all running the same version of Outlook and Excel? Also, you will need to place a password on the VBA Project to lock it from viewing and modifications and users creating errors etc. ;)

Perhpas this should be converted to a COM Add-In. Easier to distribut and users cant mess with the code.

I thought about installing on other users' machines, but I didn't realize we could lock it down like that. Can you walk me through how to do that?

In the VBA IDE, Tools > Project properties > Protection tab > check the box - "Lock project from viewing", enter in password and confirm > click OK. :)

Thanks! I'm back-tracking a little because things broke down. I need to get a working basic version and then add the enhancements, so I'll be circling back to you on this until it's all hunky-dory (it's a Texan thing for 'A-OK'). Is that okay?

Quick question though... Two of the fields aren't exporting ('Due Date' and 'Date Completed') and I suspect it is because there is a space in the field name... how can I get around that?

Okay... that was easy... I just deleted the space in the code. DOH!

Or you could try using brackets surrounding it.

[Date Completed] etc.

I set up my public folder as a 'moderated folder' so that when someone posts to it, the folder forwards items to me. They show up like an IPM.Post -sort of- the message icon looks like a post would, but the message itself looks like a task, which is the form on which my custom form is based. Anyway... Problem: the message doesn't show any of the custom information at all. None of my customized form pages, let alone the fields, show up. As a result, if I try to move the item into the public folder, any of the information entered by the user on my custom form pages gets lost. Any thougts on how to fix? I am stumped.

Sure, when you created you custom form you based it upon which form class? IPM.NOTE or ?

Your custom fields are stored in the Item or the folder? When you move it out of the folder and loos the custom fields is probably because the custom fields are stored in the folder and not the item iteslf. You wil need to store the fields in the Item istelf to fix it so the cutom fields will "travel" with the form.

I am using the IPM.Task form class. How do I get the custom items to store in the item?

By saving the form as an .otf I figured out a work-around of sorts. If I open the .otf and then publich it to my Inbox, everything works like it is supposed to work. The drawback is if I make any changes to the form in the public folder, I have to make sure to re-save it to my inbox.

Ah so now I see that the custom form wasnt published and associated with the destination folder. See if you were to move or email any items the receipient must also have your same one-off form. This is how its able to know you have custom fields on the form and to read in the item properties into the fields.

Sorry for the delay, ben a busy morning. :)

Would publishing the form to the Organization Forms Library take care of problems system-wide? In other words, for anybody who opens the form from their inbox, will Outlook automaticaly look in that library for the form?

I havent had the chance to play with Forms in an Exchange environment but possibly as long as they are in the Organization and company network I believe it should pull in the custom form. Just make sure your not overwritting a default public form or everyone will use your new form as default for the Task items. ;)

Hi,
I realize this is now quite an old thread and reading through this i thought was a brilliant guide, i am currently running office 2003 using an exchange server.
I am trying to achieve something like this also, i am trying to export custom "fields" out of a calender in a public folder in outlook.
I tested your first code you posted but have a problem when i get to this point.

Code:

---

Set oTask = oWestern.Items.Item(o)
            oSht.Cells(o + 1, 1).Value = oTask.StartDate
            oSht.Cells(o + 1, 2).Value = oTask.CreationTime

---

I am quite new to this and was wondering if i can change this to pull all the required "fields" from my public calender and export to excel?

Not sure i also may have my Fields and Tasks mixed up. :/

Quote:

---

Let me know if the progressbar and date teim pickers show in your toolbox and on the UserForm. I can help you fix that if they dont.

---

Also i tried to import your userform1 and i don't have the date and time pickers show on my userform that i got from you, can you tell me how to fix it please..

Many many thanks..

Hi,

I got the code working and its exporting the standard fields fine now, i had to change the coding a little bit to get it to work though by using

Code:

---

Dim oObject As Object

---

rather than

Code:

---

Dim oTask As Outlook.TaskItem

---

.
But no matter what i do i get an error trying to export custom fields at this line

Code:

---

oSht.Cells(o + 1, 6).Value = oObject.UserProperties.Item("CustomField").Value

---

Can i can export fields out of more than one calender? Setting the code this way doesn't give error but i'm not sure it exactly works..

Code:

---

Set oWestern = Application.GetNamespace("MAPI").Folders("Public Folders").Folders("All Public Folders").Folders("Calender1")
Set oWestern = Application.GetNamespace("MAPI").Folders("Public Folders").Folders("All Public Folders").Folders("Calender2")

---

Also still have the problem with date pickers not showing on UserForm is there a simple fix for this?

Any help would be greatly appreciated thanks..