Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 1 / August 2005

Tip: Looking for answers? Try searching our database.

Open the Outlook Addressbook in VBA

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
lauren quantrell - 31 Aug 2005 04:15 GMT
Is there a way to open the MS Outlook address book using VBA and then
be able to do something with the return value?
I want users to click an icon to open the Outlook address book then
when an address is selected, populate an Access field with the address.
Is this remotely possible?
Thanks,
lq
Bob Alston - 31 Aug 2005 04:30 GMT
> Is there a way to open the MS Outlook address book using VBA and then
> be able to do something with the return value?
[quoted text clipped - 3 lines]
> Thanks,
> lq

Yes I believe such is doable.  However, I have only used Access/VBA to
extract messages from Outlook.  YOu want to search out information on
Microsoft Office Interoperability.  I have a guide published by Microsoft:
"Microsoft Office 2000 Automation" - "Your guide to Microsoft Office
2000 Interoperability".

I suggest you search on those keywords.

Also, the following links may help:

http://www.programmingmsaccess.com/Samples/VBAProcs/VBAProcsToManageOutlookConta
ctsFromAccess.htm


http://support.microsoft.com/default.aspx?scid=kb;en-us;Q253794

http://www.slipstick.com/addins/mail.htm

http://www.slovaktech.com/code_samples.htm#StripAttachments

http://support.microsoft.com/?kbid=220595

Have fun.

Bob
Br@dley - 31 Aug 2005 04:45 GMT
> Is there a way to open the MS Outlook address book using VBA and then
> be able to do something with the return value?
[quoted text clipped - 3 lines]
> Thanks,
> lq

I have programmed a routine that works the other way and creates an
Outlook Address Book from Access data... so I'm sure you can do the
opposite.

Below is my code which may be hepful?

Function CreateOutlookContacts()
 On Error GoTo CreateOutlookContacts_err

 'prompt user
 Dim r As Long, myMsg As String
 r = MsgBox("BIGCare will create a 'BIGCare Contacts' folder in
Microsoft Outlook. Continue?", vbQuestion + vbOKCancel +
vbDefaultButton1, "Outlook Contacts")
 If r <> vbOK Then Exit Function

 Dim rsPerson As DAO.Recordset
 Dim CountTotal As Long, CountRec As Long, OutputForm As String, i As
Long
 Dim myOlApp  As New Outlook.Application
 Dim myNameSpace As Outlook.NameSpace
 Dim myFolder As Outlook.MAPIFolder
 Dim myContactFolder As Outlook.MAPIFolder
 Dim myItem   As Outlook.ContactItem
 Dim myOlBar  As Outlook.OutlookBarPane
 Dim myOlGroup As Outlook.OutlookBarGroup
 Dim myOlBarShortcut As Outlook.OutlookBarShortcut
 Dim myExplorer As Outlook.Explorer
 Set rsPerson = CurrentDb.OpenRecordset("qryOutlookExport",
DB_OPEN_SNAPSHOT)
 Set myOlApp = CreateObject("Outlook.Application")
 Set myNameSpace = myOlApp.GetNamespace("MAPI")
 Set myFolder = myNameSpace.GetDefaultFolder(olFolderContacts)

 'Check some people records exist
 If rsPerson.RecordCount = 0 Then Exit Function
 rsPerson.MoveLast
 CountTotal = rsPerson.RecordCount
 CountRec = 0
 OutputForm = "frmOutlookProgress"
 DoCmd.OpenForm OutputForm
 GV_CANCEL = False

 'Initial progress message
 Forms(OutputForm)![lblStatus].Caption = "Deleting BIGCare Contacts
folder..."
 Forms(OutputForm).Repaint

 'Contact Folder

 'Delete contacts folder
 For i = 1 To myFolder.Folders.Count
   If myFolder.Folders.Item(i).Name = "BIGCare Contacts" Then
     myFolder.Folders.Remove (i)
     Exit For
   End If
 Next

 'Create folder
 Set myContactFolder = myFolder.Folders.Add("BIGCare Contacts",
olFolderContacts)

 'Shortcut
 Set myExplorer = myOlApp.ActiveExplorer
 If TypeName(myExplorer) = "Nothing" Then  'test if Outlook open
already
   Set myExplorer = myFolder.GetExplorer
 End If
 Set myOlBar = myExplorer.Panes.Item("OutlookBar")
 Set myOlGroup = myOlBar.Contents.Groups.Item(1)

 'check if shortcut alrady exists and delete
 For i = 1 To myOlGroup.Shortcuts.Count
   If myOlGroup.Shortcuts.Item(i).Name = "BIGCare Contacts" Then
     myOlGroup.Shortcuts.Remove (i)
     Exit For
   End If
 Next

 'Create shortcut
 Set myOlBarShortcut = myOlGroup.Shortcuts.Add(myContactFolder,
"BIGCare Contacts")

 On Error GoTo CreateOutlookContacts_err
 'Create contacts from people records
 rsPerson.MoveFirst
 Do Until rsPerson.EOF Or GV_CANCEL
   If apiGetAsyncKeyState(VK_ESCAPE) Then GV_CANCEL = True  'give user
option to cancel
   Set myItem = myContactFolder.Items.Add(olContactItem)
   If Len(rsPerson![GivenName]) > 0 Then myItem.FirstName =
rsPerson![GivenName]
   If Len(rsPerson![SurnameSCR]) > 0 Then myItem.LastName =
rsPerson![SurnameSCR]
   If Len(rsPerson![Email]) > 0 Then myItem.Email1Address =
rsPerson![Email]
   If Len(rsPerson![HomePhone]) > 0 Then myItem.HomeTelephoneNumber =
rsPerson![HomePhone]
   If Len(rsPerson![WorkPhone]) > 0 Then myItem.BusinessTelephoneNumber
= rsPerson![WorkPhone]
   If Len(rsPerson![MobilePhone]) > 0 Then myItem.MobileTelephoneNumber
= rsPerson![MobilePhone]
   If Len(rsPerson![HomeFax]) > 0 Then myItem.HomeFaxNumber =
rsPerson![HomeFax]
   If Len(rsPerson![WorkFax]) > 0 Then myItem.BusinessFaxNumber =
rsPerson![WorkFax]
   If Len(rsPerson![Address1]) > 0 Then myItem.HomeAddressStreet =
rsPerson![Address1]
   If Len(rsPerson![Address2]) > 0 Then myItem.HomeAddressStreet =
rsPerson![Address2]
   If Len(rsPerson![Suburb]) > 0 Then myItem.HomeAddressCity =
rsPerson![Suburb]
   If Len(rsPerson![Country]) > 0 Then myItem.HomeAddressCountry =
rsPerson![Country]
   If Len(rsPerson![PostCode]) > 0 Then myItem.HomeAddressPostalCode =
rsPerson![PostCode]
   If Len(rsPerson![PostalAddress1]) > 0 Then
myItem.MailingAddressStreet = rsPerson![PostalAddress1]
   If Len(rsPerson![PostalAddress2]) > 0 Then
myItem.MailingAddressStreet = rsPerson![PostalAddress2]
   If Len(rsPerson![PostalSuburb]) > 0 Then myItem.MailingAddressCity =
rsPerson![PostalSuburb]
   If Len(rsPerson![PostalCountry]) > 0 Then
myItem.MailingAddressCountry = rsPerson![PostalCountry]
   If Len(rsPerson![PostalPostCode]) > 0 Then
myItem.MailingAddressPostalCode = rsPerson![PostalPostCode]
   myItem.Save
   'Update progress indicator
   CountRec = CountRec + 1
   Forms(OutputForm)![lblStatus].Caption = CountRec & " of " &
CountTotal
   Forms(OutputForm)![bxProgress].Width = (8 / CountTotal) * CountRec *
567
   Forms(OutputForm).Repaint
   rsPerson.MoveNext
 Loop

 If GV_CANCEL Then
   Forms(OutputForm)!lblEscape.Caption = "Cancelled"
 Else
   Forms(OutputForm)!lblEscape.Caption = "Done"
 End If
 Forms(OutputForm)![btnOK].Visible = True

 myMsg = "To make the Contact Folder appear in your Address Book please
do the following: " & Chr(10)
 myMsg = myMsg & Chr(10) & "- Right-click on the 'BIGCare Contacts'
shortcut in Outlook"
 myMsg = myMsg & Chr(10) & "- Select 'Properties' from the menu"
 myMsg = myMsg & Chr(10) & "- Under the 'Outlook Address Book' tab tick
the box"
 myMsg = myMsg & Chr(10) & "  labelled 'Show this folder as an e-mail
Address Book'"
 myMsg = myMsg & Chr(10) & "- Click 'OK'"
 MsgBox myMsg, vbInformation + vbOKOnly, "Outlook Contacts"

CreateOutlookContacts_exit:

 'Clean up
 Set myExplorer = Nothing
 Set myOlBarShortcut = Nothing
 Set myOlGroup = Nothing
 Set myOlBar = Nothing
 Set myItem = Nothing
 Set myContactFolder = Nothing
 Set myFolder = Nothing
 Set myNameSpace = Nothing
 Set myOlApp = Nothing
 Set rsPerson = Nothing

 Exit Function

CreateOutlookContacts_err:
 MsgBox err.Description, 48, "Error in CreateOutlookContacts()"
 Resume CreateOutlookContacts_exit
End Function

Signature

regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

pietlinden@hotmail.com - 31 Aug 2005 04:45 GMT
One thing you could do-
link to the Address book (or I guess use IN in your query...) and then
use the Address Book in a combobox or something...   something like:

SELECT olkContacts.Last & ", " & olkContacts.First AS Contact FROM
olkContacts ORDER BY olkContacts.Last & ", " & olkContacts.First;

If you put all the information you want in various columns of your
combobox, you can refer to the columns using
[some text field] = cboOLKContact.Columns(0)

or some such thing.

Hope it helps some.
Pieter
lauren quantrell - 31 Aug 2005 21:56 GMT
Pieter,
That seems like a good solution with an MDB but I'm using an ADP with a
SQL server backend. As far as I know I can't link to a user's Outlook
Address Book...
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.