Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Lounger
    Join Date
    Oct 2005
    Location
    Kansas City, Missouri
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Outlook to Access (Oulook 2003 / Access 2000)

    Hello All,

    I posted this in the Access forum, but thought I should post here as well.

    Im sure this has been asked before, but I my search didnt find exaclty what I was looking for, so I ask hoping to not offend anyone.

    I would like to be able to export the currently selected contact from a shared Outlook Contact address book (shared by all users and stored in a public folder on our Small Business Server 2003) into an Acess database I created to hold our Company Contact information. Does anyone know any sample code or a way that I can do this? Is it even possible? Any help would be much appreciated!

    Thanks!

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    There's a wizard you need to check out in Access. From the general Access Window, select File | Get External Data | Import - in the "files of type" dropdown select "Exchange()" or "Outlook()", and you should end up in a dialog like the attached. HTH.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    Oct 2005
    Location
    Kansas City, Missouri
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    Hi,

    Yes I did try this wizard before, but no luck. It doesnt show me the Public folders of the Exchange server, where the Compay Contacts are located, and also when I click on Exchange Server it gives me an error that it is unavailable. However I was able to create a linked table to the company contacts, which I deleted from my database, and tried to re-link, and now I get an error message (see below).

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    I can't help you much except hat you may be subject to Permission issues with Public Folders - you will have to take that up with Server Admin. Restarting Access seems to fix some problems with accessing Exchange folders. As you can see from the graphic I posted, I have access to PF though the Wizard, but because of permissions I'm not able to import PF Contacts folders into Access. You might also try the Address Book tree of the import wizard dialog, to see if the Contact list you need is in there - in my company I can get to the Global Address List (GAL) and import it. (Which doesn't make sense, but I guess that not all the doors have been locked.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    John, the original question was how to import the currently selected contact into Access. Do you know how to get at the currently selected contact using Outlook VBA? I can never get my head around Inspectors and Explorers <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Perhaps if you could provide that part, I could add some Access code...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    Thanks, John, that is very useful!

    Shirley, you could use the attached version of John's code from Access. You need to set two references to make it work:
    - Select Tools | References... in the Visual Basic Editor.
    - Tick the check boxes for Microsoft DAO 3.6 Object Library and for Microsoft Outlook n.0 Object Library
    - n = 9 for Outlook 2000, n = 10 for Outlook 2002 and n = 11 for Outlook 2003.
    - Click OK.

    The code assuems that you have created a table tblContacts with text fields FirstName, LastName and EMail1Address. You can change this to your needs - add or remove fields as needed.

    Note: Outlook security will nag you each time you run the code, you may want to install ClickYes to suppress this.

  7. #7
    Lounger
    Join Date
    Oct 2005
    Location
    Kansas City, Missouri
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    Thanks to both John and Hans.
    Ok, just to make sure I understand this...I add this code to my database (I assume I create a button with the event procedure to run the code) and it will check to see what contact is selected in Outlook (open or not) and pull that information over to my designated table in Access?

    I appologize in advance for my lack of knowlege of these things. I am trying to learn tho!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    Yes, the code I posted is meant to be called from Access, for example from the On Click event procedure of a command button.
    The user should have Outlook open to the relevant contacts folder, with either one or more contacts selected in the folder, or with a contact open in its own window. The user then switches to Access and clicks the button.

    It would also be possible to create a different version of the code that is meant to be run from Outlook, for example from a custom toolbar button.

  9. #9
    Lounger
    Join Date
    Oct 2005
    Location
    Kansas City, Missouri
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    Thanks alot Hans. I just needed to verify in my brain where to apply the code.

    <<<Note: Outlook security will nag you each time you run the code, you may want to install ClickYes to suppress this.>>>
    Will each user on the network need this installed localy on their computer?

    <<<It would also be possible to create a different version of the code that is meant to be run from Outlook, for example from a custom toolbar button.
    >>>
    Given the option I know my boss will want to be able to do this from Outlook vrs Alt + tabing to Access. How would I modify the code to run directly from Outlook?

    Thanks a ton...again!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    ClickYes would have to be installed on each user's PC, but there are alternatives. See <post:=488,173>post 488,173</post:> for the available options.

    I have attached a version of the code to be run from Outlook. You need to set a reference to the Microsoft DAO 3.6 Object Library in Tools | References in Outlook's Visual Basic Editor.
    Don't forget to substitute the correct path and filename of the database.
    You can assign the macro to a custom toolbar button in Outlook.

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    Oh! Shirley, I'm sorry, I misinterpreted the original question. Hans, this code loops through every contact selected in an Explorer View, or if one is open, just that Contact. (Hans, in my simple world-view of Outlook, Explorers go with Folder Views, and Inspectors go with Items.) I know you'll be able to get the myriad Properties from there. I put it together a bit quick-&-dirty.

    Sub GetSelectedContactProperties()
    Dim itm As Object
    Dim lngC As Long
    ' ContactItem - to click on and browse Properties
    If TypeName(Application.ActiveWindow) = "Explorer" Then ' looking at Folder contents
    For lngC = 1 To ActiveExplorer.Selection.Count ' loop through all selected Contacts in the Folder
    On Error Resume Next
    Set itm = ActiveExplorer.Selection(lngC)
    If Not itm Is Nothing Then
    With itm
    If .Class = olContact Then ' skip Distribution Lists, they have very different Properties
    Debug.Print itm.FileAs ' testing purposes
    ' suck up the properties here
    End If
    End With
    End If
    Next lngC
    Else
    If Inspectors.Count Then Set itm = ActiveInspector.CurrentItem ' examine the most recently opened item
    If Not itm Is Nothing Then
    With itm
    If .Class = olContact Then ' make sure it's a Contact Item
    Debug.Print itm.FileAs ' testing purposes
    ' suck up the properties here
    End If
    End With
    End If
    End If
    Set itm = Nothing
    End Sub

    See also <post#=511008>post 511008</post#> and related <post#=510884>post 510884</post#>, which are for an entire Contacts Folder.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Lounger
    Join Date
    Oct 2005
    Location
    Kansas City, Missouri
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    Thanks a ton to Hans and John. So far the code is working fine. Im still in the testing stages, but so far, it is working. The only thing Im having trouble with is trying to match up fields between my table and the default or standard field names in Outlook. For example I have Address1 and Outlook has it as BusinessAddressStreet. Speaking of the business address, is there a way, to break up the business address1 and 2 lines and put each in the correct field in Access? For example: in an outlook contact I have the following for the Business address:

    1234 Cherry Lane (I assume this is BusinessAddressStreet)
    P.O. Box 123 (I assume this is BusinessAddressStreet2)

    when I export it from Outlook to Access it all shows up in the address1 field as one line (1234 Cherry Lane P.O. Box 123).

    Is there a way to seperate the address lines?

    An example of code is as follows:
    rst!Address1 = .BusinessAddressStreet
    rst!Address2 = .BusinessAddressStreet2
    rst!City = .BusinessAddressCity
    rst!State = .BusinessAddressState
    rst!ZipCode = .BusinessAddressPostalCode
    rst!WorkPhone = .BusinessTelephoneNumber
    rst!FaxNumber = .BusinessFaxNumber
    rst!Email = .Email1Address

    Also...is there somewhere I can print or get a list of all the standard field names in Outlook?. It would be much easier for me to match up fields between outlook and my database if I knew exactly how Outlook had them.

    Thanks for all your help on this guys! Its very much appreciated!!

    Shirley

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    <P ID="edit" class=small>(Edited by JohnBF on 22-Feb-06 12:37. Fixed typos, improved links)</P>Regarding the list of Properties (fields) in Outlook: in the Visual Basic Edit (VBE), press 'F2', the Object Browser pops up, then in the Search dropdown next to the "Search Binoculars" enter "ContactItem" - click on the Search Binoculars and your list will appear in the lower right section of the window - all Properties (fields), Events and Methods are listed. You can also drill down here: Outlook 2003 Object reference
    Edit: to ContactItem

    On your first question, there may be a problem - I have learned that although Outlook has some parsing techniques built-in for addresses and phone numbers, they are not exposed. (An example is if you are in the US and enter a US address in a Contact, then click on the Address Type button (Business, Personal, ...) a "check address" dialog pops, and attempts to structure the address to US postal conventions. I haven't figured out how to get at this.) Perhaps another Lounger can help you right away, I'll take a look at it later.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Lounger
    Join Date
    Oct 2005
    Location
    Kansas City, Missouri
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    Thanks John, that is exactly what I needed. I was calling it something else, hence why I coulndt find it before!

    as for the potential problem, I appreciate any help/feedback that anyone could give on this.

    *cookies and Mountain Dew for all!*
    Shirley

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Outlook to Access (Oulook 2003 / Access 2000)

    See if some variation of this quick-&-dirty approach gets you what you need:

    Dim varAddr As Variant
    Dim intL As Integer
    varAddr = Split(itm.BusinessAddress, Chr(13), -1, 1)
    For intL = 0 To UBound(varAddr)
    Debug.Print varAddr(intL)
    Next intL

    'intl' in effect becomes the number of each line of the address. It's giving me extra blank lines, perhaps a better VBA expert than me (and they know who they are <img src=/S/grin.gif border=0 alt=grin width=15 height=15>) can refine it.
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 3 123 LastLast

Posting Permissions

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