Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2135
  • Last Modified:

connect outlook and/or exchange contacts folder odbc data source

I have a database that holds customer name, addresses, phone numbers, etc. I want to make this data
available, readonly, to all users in an outlook contacts folder (currently outlook 2000, but could become 2003)
We also have exchange server 2003.

It's similiar to this question
http://www.experts-exchange.com/Applications/MS_Office/Outlook/Q_21046180.html
but should run offline rather than when outlook is started up.

There used to be a product called orbis intelliware that did this but it's no longer available
0
NickUpson
Asked:
NickUpson
  • 6
  • 5
  • 3
1 Solution
 
David LeeCommented:
Hi NickUpson,
> run offline
What do you mean by this?  Do you envision the processing running like a scheduled task?

Cheers!
0
 
NickUpsonSenior Network EngineerAuthor Commented:
We could accept it running every night = offline or, ideally, completely syncronised, getting the data whenever it's accessed
0
 
travalCommented:
Sounds like you want to use a public folder.  Have you considered that option, and if so is there a reason it doesn't suit your needs?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
NickUpsonSenior Network EngineerAuthor Commented:
a public folder is fine, the problem is how to connect a public folder to an odbc datasource
0
 
travalCommented:
Any custom fields in the Outlook contacts?

Is this importing from Access?
0
 
NickUpsonSenior Network EngineerAuthor Commented:
standard default outlook contacts fields but no it's not access (actually firebird) therefore only accessible via ODBC
0
 
travalCommented:
Are you looking to create a custom solution yourself?  

If not there are probably utilities which will suit your needs.  See this:
http://www.mapistore.com/Outlook/Contacts/Review_00766_index.html (free download)
or
http://www.hidownload.com/products/6/34/6-34-666.htm (free trial $65 to purchase)

Several other solutions are listed at this site:
http://www.slipstick.com/addins/groupcontacts.htm
0
 
David LeeCommented:
>getting the data whenever it's accessed
Depending on the size of the database that doesn't seem very practical and I don't know how you'd go about trapping an event indicating that someone was accessing the contacts.  It'd make more sense to have an external process that was kicked off whenever the database changed, syncing Outlook to it.  I don't know anything about Firebird, in fact I've never heard of it before.  If it's programmable, then maybe something could be written into it that'd run the syncing process immediately after any changes were made to the data.  An alternative would be to write an update routine in VB or VBScript and schedule it to ran however often you deem necessary using the Windows Task Scheduler.
0
 
NickUpsonSenior Network EngineerAuthor Commented:
Ideally when someone accesses the folder, an sql statement would run to populate it so no problem with events
0
 
David LeeCommented:
I don't believe that's going to be possible.  That's what I was mentioning, there's no event I know of that's triggered when a person accesses a folder.  
0
 
NickUpsonSenior Network EngineerAuthor Commented:
a process that ran every night to reload them would be acceptable
0
 
David LeeCommented:
Do you use all the contact fields or only a subset?  If the latter, which ones?  Also, do the database fields have the same names?
0
 
NickUpsonSenior Network EngineerAuthor Commented:
not all of them, there must be 100. just basic ones like name, company, business phone, busiesss fax, etc.
the database fields don't have the same names but I could probably put a view in between if necessary
0
 
David LeeCommented:
Here's the code.  To use it:
1.  Open Notepad
2.  Copy the code and paste it into Notepad
3.  Edit the code as required, making sure to make the changes per the comments in the code
4.  Save the file.  You can name it anything you want so long as it ends with .vbs
5.  Run the script as a test
6.  Create a scheduled task that'll run the script each night

The script relys on Outlook so it must run on a computer that has Outlooked installed.  I built and tested this on a computer using Outlook 2002 and it worked without a hitch.

'Code begins here
'Open the database
Set adoCon = CreateObject("ADODB.Connection")
'Change the connection string to the one required for your database
adoCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\eeTesting\Contacts.Mdb;Persist Security Info=False"
adoCon.CursorLocation = 2
adoCon.Open
Set adoRS = CreateObject("ADODB.RecordSet")
'Change Contacts to the name of the table in your database
adoRS.Open "Contacts", adoCon, 0, 1
'Open Outlook
Set olApp = CreateObject("Outlook.Application")
Set olNS = olApp.GetNamespace("MAPI")
'Change Outlook to the name of your Outlook profile
olNS.Logon "Outlook"
'Change the folder path to that of your shared contact folder
Set olContactFolder = OpenMAPIFolder("\Lee, David\eeTesting\Contacts")
'Delete all the existing contacts (it's simpler to re-add them all)
For intCounter = olContactFolder.Items.Count To 1 Step -1
    olContactFolder.Items(intCounter).Delete
Next
With adoRS
    'Read the database records
    While Not .EOF
        'Create a contact
        Set olContact = olApp.CreateItem(2)
        'Fill in the contact's fields
        'Edit the list of fields as needed
        olContact.BusinessTelephoneNumber = .Fields("BusinessPhone")
        olContact.CompanyName = .Fields("Company")
        olContact.Email1Address = .Fields("Email")
        olContact.FirstName = .Fields("Firstname")
        olContact.LastName = .Fields("Lastname")
        olContact.MailingAddress = .Fields("Mailing")
        olContact.Title = .Fields("Title")
        'Save the contact to the default contact folder
        olContact.Save
        'Move the contact to the proper folder
        olContact.Move olContactFolder
        .MoveNext
    Wend
End With
'Clean up
adoRS.Close
adoCon.Close
olNS.Logoff
Set adoRS = Nothing
Set adoCon = Nothing
Set olContact = Nothing
Set olContactFolder = Nothing
Set olNS = Nothing
Set olApp = Nothing

'Credit where credit is due.
'The code below is not mine.  I found it somewhere on the internet but do
'not remember where or who the author is.  The original author(s) deserves all
'the credit for these functions.
Function OpenMAPIFolder(szPath)
    Dim app, ns, flr, szDir, i
    Set flr = Nothing
    Set app = CreateObject("Outlook.Application")
    If Left(szPath, Len("\")) = "\" Then
        szPath = Mid(szPath, Len("\") + 1)
    Else
        Set flr = app.ActiveExplorer.CurrentFolder
    End If
    While szPath <> ""
        i = InStr(szPath, "\")
        If i Then
            szDir = Left(szPath, i - 1)
            szPath = Mid(szPath, i + Len("\"))
        Else
            szDir = szPath
            szPath = ""
        End If
        If IsNothing(flr) Then
            Set ns = app.GetNamespace("MAPI")
            Set flr = ns.Folders(szDir)
        Else
            Set flr = flr.Folders(szDir)
        End If
    Wend
    Set OpenMAPIFolder = flr
End Function

Function IsNothing(Obj)
  If TypeName(Obj) = "Nothing" Then
    IsNothing = True
  Else
    IsNothing = False
  End If
End Function
'Code ends here
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now