Exporting from a Custom Form in Outlook 2003 into Excel

[ArbiJames]

I am in WAY over my head. I have very little training in what I've been asked to do... I have designed a form that posts a task to a public folder, and I want to be able to export from that folder into an Excel spreadsheet. I’m told that the reason I can’t show that folder in the Outlook import/export dialog window is because it is a public folder, and the only way to export from a public folder is through VBA. The work-around is to copy that folder down to my own folders, but when I did that, I was able to export... but only the fields defined by Outlook… my custom form fields do not show up.

Another work-around is to look at the folder in a table view, including all the fields of the form, then select all, copy, and paste into Excel. It’s a ‘dirty’ solution, though… text moves all over the place from where it is supposed to be, and I'd rather not have to clean up an Excel file.


HELP!

[RobDog888]

Welcome to the Forums.

Here are a couple of my code examples on accessing a Public folder.

http://vbforums.com/showpost.php?p=2218226&postcount=4
http://vbforums.com/showpost.php?p=1982278&postcount=3

[ArbiJames]

You know, if I was the least bit trained in VBA code, that would probably help me, but I am lost. This is foreign to me... I was barely able to get my form done. So I'm thinking maybe I need to go get a book on VBA? For Excel 2003? Would that be the way to do it? I don't want to waste your time... I mean, I basically need someone to figure out the solution and just do it at this point because this just isn't my area of responsibility. I don't mind trying to figure it out, but I have no idea where to even begin. I'm maybe 1-3 levels above the guy who calls tech support and can't even figure out how to turn the thing on.

[RobDog888]

Its never a waste of time as it would be available for anyone later on that may have the same issue.

Lets break it down a bit.

First, you have your Tasks in a Public folder already?
How did you want to invoke the export?
Last, what version are you running of Outlook and Excel.

[ArbiJames]

I may start a religion dedicated to worshipping you. THANK YOU! I AM NOT WORTHY!

Okay...

First, you have your Tasks in a Public folder already? YES, they're already in the folder
How did you want to invoke the export? Doesn't matter... and maybe the VBA needs to be for Excel. Like maybe I open an Excel file and click the magic button and it all happens?
Last, what version are you running of Outlook and Excel. Outlook is 2003 (11.8010.8028) SP2 and Excel is 2003 (11.8033.8028) SP2

[RobDog888]

Ok, where we invoke this from is not very important but if we call it from Excel we will get a Security dialog prompt and if we call it from Outlook VBA we wont.

What is the complete path of your Public folder that contains the needed exported tasks?

[ArbiJames]

I wondered about that.

Just because I know I am venturing into the area beyond my own knowledge, let me tell you how I am about to answer that question. That way, you know you're getting the full answer.

Okay, so what I did was rt-clk on the folder and went to properties. In the 'Location' area of that window it says: \\Public Folders\All Public Folders\Arbitron\RSS\DTXARB the name of the folder is "Western Div Projects" so I guess the full path would be \\Public Folders\All Public Folders\Arbitron\RSS\DTXARB\Western Div Projects

Did I get that right?

[RobDog888]

Yes, that is correct. The reason we need the full path is because its just like in Windows Explorer. To navigate to a folder somewhere you need to transverse the directory structure.

So in code to "connect" to your desired folder we will use that complete path.

Code example coming up soon. Need to take care of some business first. Be right back.

[ArbiJames]

The suspense is killing me!

[RobDog888]

Just had a couple of minutes so I whipped this example up and hope its close to what you need.
Got a rush project to do so I may not be back online until tomorrow.

Make sure you enable macros to run and restart Outlook.


Click on our newly added custom menu item located under Tools > Export Public Tasks to run the function.

Option Explicit
'Behind ThisOutlookSession
'Add a reference to MS Excel xx.0 Object Library
Public WithEvents oMnuExport As Office.CommandBarButton
 
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 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
 
    '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)
End Sub

[ArbiJames]

I am not entirely sure I did it right because a) again, I barely know what I am doing anyway, and b) I got a compile error "user-defined type is not defined." It got hung up on the line: Dim oApp As Excel.Application

Would you mind sending me a PM when you have a moment tomorrow, and we'll get back to this then?

Thanks so much again for all your help!

[RobDog888]

In Outlooks VBA IDE (press Alt+F11) and click on Project > References... menu and select "MS Excel xx.0 Object Library" > Click OK and that should add the reference to Excel and fix that error.

Ps, the code goes in "ThisOutlookSession" class in the VBA IDE.

[ArbiJames]

Can we pretend I'm an idiot for a sec? I know, not hard to do.

I think I put the code in the right place, but I'm not so sure. What's the click-by-click way to do that?--- by the way, I did figure out how to add the Excel reference.

[RobDog888]

So do you get your new menu item added to the tools menu?

[ArbiJames]

When I enabled macros, exited Outlook, and came back in, I must have done it too quickly for the change to take effect because it didn't work, but SWEET MOTHER OF GOODNESS IT WORKS NOW! WOO HOO!

So... in order to get the other fields in my custom form into the spreadsheet, would I add lines of code for those fields in the 'Setup column headers' and the 'Loop through the Outlook tasks in the folder...' sections of your code?

[RobDog888]

Correct. There are many properties of a TaskItem so its up to you which you want to include.

Glad its working for you on the first try

Note: There are some properties that are not easily accessible as they are collections. We can get them but another step is needed.

Ps, do you also want to add a toolbar button in addition to the menu item?

[ArbiJames]

ooo! Fancy! Yeah... let's do that! Also... could you also tell me how to remove it all, should the need arise?

[RobDog888]

Ok, to remove the menu item you can right click on a toolbar or blank area and you will get a popup menu. Click the customize item. Then a dialog window will appear. Click on the "Menu Bar" item in the toolbars tab and then click the reset button.

Ok, give me a sec.

[RobDog888]

Ok, here here is the new updated code with the toolbar button code added. I added an option of using just an Icon
only for the button or if you want you can comment it out and uncomment the code for using an icon and a caption (text).

Icon Only:



Icon and Caption:

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

[ArbiJames]

Awesome! You ROCK!

Okay... hopefully just one more thing... can you please help me with figuring out how to get my custom fields exported? I already tried adding some code in the manner we talked above, but it gave me "Run Time Error '438': Object doesn't support this property or method." and on the de-bugger it's hung up on the line I added for one of my custom fields.

[RobDog888]

Thanks

Post your new oMnuExport_Click event procedure code and I will look at it..

[RobDog888]

Or just post the fields/properties you want exported and I'll add them to the code.

[ArbiJames]

Because a lot of the fields have the same properties, if there's a 'standard' way to do the code, I don't want to waste your time having you write it all line-by-line... so if you can just give me the skinny, I'm sure you have better things to do.

Field (Property)
AdditionalText (Text)
CallsGroup (Text)
IRS (Yes/No)
IRSSub (Yes/No)
KeyLocalRenewal (Yes/No)
LMR (Yes/No)
LMRSub (Yes/No)
Mappy (Yes/No)
MAPPYSub (Yes/No)
Max (Yes/No)
MAXSub (Yes/No)
PDA (Yes/No)
PDASub (Yes/No)
PPM (Yes/No)
PPMSub (Yes/No)
ProjManager (Text)
Qualitative (Yes/No)
QualSub (Yes/No)
Tapscan (Yes/No)
TAPSub (Yes/No)
Timeline (Text)

[RobDog888]

Ok, since these are user defined fields are they being stored in the item properties or in the folder properties? If your not sure then test by using this format.

oSht.Cells(o + 1, 6).Value = oTask.UserProperties.Item("AdditionalText").Value

[ArbiJames]

That did it! My 'additionaltext' field exported completely.

Does that mean I'm good to go? If so... WOW!

[RobDog888]

Yes, and no. lol.

The other fields that are Yes/No ones will return a boolean value of either 1 and 0 or -1 and 0. Cant remember, but it may also return an actual Yes/No text value too. Its been a while since I did custom fields.

Test out one of them.

[ArbiJames]

They seem to be working fine... the value they export is either "TRUE" or "FALSE." The bottom line is that it's all in an Excel table... it won't be that much work to use Excel to do find/replace on any fields we want to change.

I just thought of one more issue... would it be a huge pain to do a window that asks the user to select the from/to dates of just the tasks they want to export? It's not a huge deal now because they can just delete the ones they don't want from the spreadsheet, but I can see a point down the line when people will be whining about having to delete hundreds of rows.

[RobDog888]

Yes, we can export a date range. Its just a bit of a tweak to the code. Instead of iterating through all items we will filter it based upon the date selections.

We may want to use a small UserForm to prompt them for a start and end date since entering in dates manually via an Inputbox would require more error checking and stuff. Is this an ok approach?

[ArbiJames]

Brilliant! Yes, that works perfectly.

[RobDog888]

Ok, almost done. I made the userform in Outlooks VBA IDE but I'm looking for a way to save it out to a file for you so it wont require you to recreate it manually.

Couple of mins and I'll be done.

[ArbiJames]

Sweet! I'm going to head for the house and pick it up there. I think you have my email address, but I'll send it to you in a PM just in case.

[RobDog888]

Ok, then I'm going to take a short break and finish my Star Bucks caramel Frapacchino before it melts.

Got the pm too.

[RobDog888]

Ok, just about done but for the filter do you want to filter by only the Start Date so its between the two choosen dates? Or perhaps add the Date Complete or Due Date?

[RobDog888]

What do you want to do if the task doesnt have a Start Date? Do you want to filter by the Created Date then instead or combimation of some kind?

[ArbiJames]

Watch out... those frappacchinos are dangerous!

Sorry for the late response... there's been a bit of drama at the 'ranch' involving our newest canine family member and a neighbor's cat. Everybody's okay... thank goodness!

Let's offer two choices: 1) filter on the date complete because the report they'll be needing in the Excel spreadsheet probably 99% of the time will only need to be completed tasks... and 2) for that other 1% of the time, let's do no filter, where it just exports all.

That way if worse comes to worse they can either take care of what they don't need either through deletion or filtering in Excel or import into Access and query out what they need there. The very fact that we're giving them the option will be plenty. Do I sound like a programmer yet?

[RobDog888]

Yes, your starting to sound just like a programmer lol.

Ok, I'll add the Completed option to the date selection form and will be done tonight.
so the Start Date and End Date will be based upon the Completed Date property as one option and export all will be the other option.

Yes, we all have our days. I just had a disappointing discussion with my neighbor about our block wall we went in 50% to have replaced last year. Sucker had been putting off paying and now that I got firm about it he said "Oh I never wanted the wall and I cant aford it because my teenage daighter got prego". If that was true then he should not have agreed at the time and his daighters baby was already born at that time too. Arrrrg! Filing a lawsuit Monday morning

[ArbiJames]

Well good luck with that, sir. Can't imagine the frustration of that one!

I have nothing against cats... like them a lot, actually... but when you own one and allow it free-range outside, you accept a certain level of risk where interraction with other animals is concerned. Animals behave like.... umm... animals, and while I wish they didn't sometimes, one can't expect a healthy canine to not behave a bit rudely towards an unexpected and unwelcome feline.

C'est la vie, I guess.

[RobDog888]

Not when it comes to my cat and dog. The dog is large (75lbs) but he is afraid of the kitten.

I got side tracked but am working on it again now. Outlook is my favorite program of all time and I can do almost anything with it.

I can post an update in a few. i just find myself adding little extras and stuff. Hard to stop lol.

[ArbiJames]

Seriously, though... I may have to start the church of RobDog.

[RobDog888]

Well there is a meeting this month, all month long - http://vbforums.com/showthread.php?t=352866

If its too much to read then check out the last few pages.