Results 1 to 3 of 3
  1. #1
    Martin Stradling

    Exporting Outlook forms to Access

    Scenario -
    Using Outlook 2000 (version - & Access 2000 (version 9.0.2720) with Windows 2000 (Build 2195).

    I am trying to set up a very small support desk call logging system using the Outlook Appointment form. I created a new page (p2) with the appointment & attendee availability pages hidden. The form has a number of
    standard fields in it with three custom designed fields (two text fields & one check box - True or False). The idea is to be able to enter information onto the form & save it into a specific folder within Outlook & also to send
    the details to another user if necessary. So far, so good - this part is working exactly as I need it to. At some point, be it the end of the day, or week, or every couple of hours etc. I want to be able to copy the information saved in the Outlook folder over to an Access database (or
    something similar). Reason being I want to be able to query a number of fields in the form & report on both the overall information as well as specific information. An example of this would be all calls received & still outstanding in a specified period of time. Outlook does not appear to be able to produce the kind of information I am looking for, hence the reason for copying the data elsewhere.

    Problem -
    The best way I can think of that my abilities will be able to both handle and administer if necessary is to export the folder to a database and manipulate the data from there. The "export to a file" facility in Outlook appears to be woefully inadequate for what I need. The list of fields
    available to be exported omit a great deal of the fields I am using and nowhere can I find a way of picking up the custom fields I have created. I have checked the export options using other forms, but they are equally limited. This appears to be the problem no matter where the data is being exported to. As far as I can tell, there are no ODBC drivers available for Outlook and I cannot find any suitable way of examining the PST file & stripping the data from there.

    Question -
    Is there a way (preferably an easy one) to transfer all the fields in a form (custom or otherwise) to a database? The ideal answer would be to schedule a task that will do the transfer without any user intervention. I have considered creating a new database in Access with all the fields I need
    there, but my knowledge & skills with Access, VB and Macros are not sufficient to make the input form fool-proof enough.

    Thanks very much in advance for any help given.


    Martin Stradling

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 93 Times in 89 Posts

    Re: Exporting Outlook forms to Access

    This sounds like a lot of fun - if you have time for it!

    > I want to be able to query a number of fields in the form & report on both the overall information as well as specific information.

    Perhaps you could start with something less ambitious than Access, which would be to pump the information out into a file you could import into Excel. Certainly Excel is not a full-featured as Access when it comes to querying and reporting, but if you are looking for a way to quickly "see" the data and calculate progress and outstanding problems for your own reference, it may be a useful first step. You will learn what you really want before diving into the database stuff.

    On the Outlook side, you could use a VBA procedure that appends the desired fields as a new row at the end of a text file. You could make this fancier by writing out only selected messages, or by keeping track of which messages have been sent out and only writing out the new ones.

    If you are interested in getting your feet wet with VBA, take a look at this code and tailor it to your needs:

    Sub AppendData()
    'Appends data from all items in the folder to a text file
    'If active window is not a folder, message user to make folder active
    If TypeName(Outlook.ActiveWindow) <> "Explorer" Then
    MsgBox "Please open the appointments folder you wish to export and try again", _
    vbCritical + vbOKOnly
    Exit Sub
    End If
    'check to see that it is the right kind of folder and not empty
    If ActiveExplorer.CurrentFolder.Items.Count < 1 Then
    MsgBox "There are no items in this folder to export", vbCritical + vbOKOnly
    Exit Sub
    ElseIf ActiveExplorer.CurrentFolder.DefaultItemType <> olAppointmentItem Then
    MsgBox "Please open the appointments folder you wish to export and try again", _
    vbCritical + vbOKOnly
    Exit Sub
    End If
    'confirm user's desire to export current folder
    If MsgBox("Append data from all items in this folder?", _
    vbQuestion + vbYesNo) = vbNo Then Exit Sub
    'append tab-delimited data to
    Dim myAppt As AppointmentItem, intFileNo As Integer
    intFileNo = FreeFile
    Open "" For Append As #intFileNo
    For Each myAppt In ActiveExplorer.CurrentFolder.Items
    Print #intFileNo, myAppt.Start & vbTab & myAppt.Subject & vbTab & _
    myAppt.Importance & vbTab & myAppt.Location
    Close #intFileNo
    MsgBox "Done!"
    End Sub

  3. #3
    Martin Stradling

    Re: Exporting Outlook forms to Access

    Thanks very much for the advise. The VBScript works absolutely fine, but as one would expect, answer one question and along come a whole load more. I am now coming across a few other problems. This could, I admit, be due to my lack of any formal VB training or experience and this may not be the place to discuss this.

    However, I'm hoping there may be an easy way around my problems - short of totally re-designing the form I've created.

    The problems I now have are these:-
    Due to both a need for certain fields and a desire to see certain information, I have put fields into the form that are not part of the appointments form, but are part of the "Frequently-Used Fields". In particular, the Received and To fields from the Mail fields. In my naivety, I was hoping to be able to pick up the same fields in the list of
    all the various items.
    There appears to be a few fields that are showing in the "Field Chooser" box in the Form design that are not appearing in the Object Browser in VB. My biggest problem with this is the "Contacts" field. It appears in just about every list in the field chooser, but nowhere in the Object Browser.
    Whilst I am vaguely familiar with some programming languages, as mentioned earlier, I have no formal VB experience. It appears to me that VB does not like
    nested "For" loops - my attempt at a solution to including fields from other items without having to run the same procedure a number of times looking trying to update
    the same file each time.
    Finally I come back to the dreaded user-defined fields. Is there an easy way of either adding all the fields in the form to the list of items in the Object Browser, or else, directing the script to look at the form to pick up
    the extra fields?

    Thanks very much again.

    Martin Stradling.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts