UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Import Outlook Contacts And Accmdaddfromoutlook, Office 2013    
 
   
lex
post Jul 11 2017, 06:18 AM
Post#1



Posts: 671
Joined: 20-October 05



I'm on a corporate network, want to run VBA comparing an email address to the corporate contact list and am not sure how to do it. Using the Contacts template, I found acCmdAddFromOutlook which opens dialog with the data I'd like to access, but can't seem to figure out how to access those lists.

I found the following

Visit My Website

which references olFolderContacts, but am *pretty* sure that this is My Contacts, not the corporate list.

Does anyone know how to access the Global Address List?

Thanks
Go to the top of the page
 
ADezii
post Jul 11 2017, 12:31 PM
Post#2



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I created a Function that returns the Global Address List in a 2-Dimensional Array with the 1st Dimension containing the User's Name and the 2nd Dimension the E-Mail Address.
  2. The Function Definition is listed below:
    CODE
    Public Function fProcessGlobalAddressList() As Variant
    Dim appOut As Object                        'Outlook Application Object
    Dim oGAL As Object                          'Global Address List Reference
    Dim oContact As Object                      'Contacts in Global Address List
    Dim oUser As Object
    Dim arrUsers(1 To 65000, 1 To 2) As String  '2-Dimensional Array, 1st Dimension
                                                'holds the Name, 2nd SMTP Address
    Dim lngIndex As Long                        'Len(<Last Name>) in GAL > 0
    Dim intCtr As Long                          'ALL Items in Global Address List
    Const conEXCHANGE_USER As Byte = 0          'Exchange User in same Forest

    Set appOut = CreateObject("Outlook.Application")
    Set oGAL = appOut.GetNameSpace("MAPI").AddressLists("Global Address List").AddressEntries

    For intCtr = 1 To oGAL.Count
      Set oContact = oGAL.Item(intCtr)
        If oContact.AddressEntryUserType = conEXCHANGE_USER Then
          Set oUser = oContact.GetExchangeUser
            If Len(oUser.LastName) > 0 Then
              lngIndex = lngIndex + 1
              Cells(lngIndex, "A").Value = oUser.Name
              'Debug.Print Format$(lngIndex, "000"), oUser.Name, oUser.PrimarySMTPAddress
                With oUser
                  arrUsers(lngIndex, 1) = .Name
                  arrUsers(lngIndex, 2) = .PrimarySMTPAddress
                End With
            End If
        End If
    Next intCtr

    appOut.Quit

    fProcessGlobalAddressList = arrUsers()

    Set appOut = Nothing
    Set oGAL = Nothing
    Set oContact = Nothing
    Set oUser = Nothing
    Erase arrUsers
    End Function
  3. To process the Return Array, you would use the following Code:
    CODE
    Dim varGAL As Variant
    Dim lngCtr As Long
    Dim lngIdx As Long
    varGAL = fProcessGlobalAddressList()

    For lngCtr = LBound(varGAL, 1) To UBound(varGAL, 1)
      If Len(varGAL(lngCtr, 1)) > 0 Then
        lngIdx = lngIdx + 1
        Debug.Print Format$(lngIdx, "000"); Tab(10); varGAL(lngCtr, 1); Tab(35); varGAL(lngCtr, 2)
      End If
    Next
  4. I typically list the OUTPUT with Code demos but in this case, privacy prevails. I will list what the OUTPUT would look like for only 3 entries in the Gal:
    CODE
    001      Fred Flintstone          Flintstone@Dino.com
    002      Barney Rubble            Rubble@Dino.com
    003      Bam-Bam                  Bam-Bam@Dino.com
Go to the top of the page
 
lex
post Jul 11 2017, 03:32 PM
Post#3



Posts: 671
Joined: 20-October 05



As always- you're fab! Thanks

Attached File  fred_flintstone.jpg ( 139.21K )Number of downloads: 1

Attached File(s)
Attached File  fred_flintstone.jpg ( 139.21K )Number of downloads: 0
 
Go to the top of the page
 
ADezii
post Jul 11 2017, 05:52 PM
Post#4



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif Good Luck with your Project!
Go to the top of the page
 
lex
post Jul 17 2017, 08:36 PM
Post#5



Posts: 671
Joined: 20-October 05



Hello again, ADezii.

So I was planning on updating a local contact table with this code when processing an email ie when I read an email into my system, use the senders email address to go into the global address book, see if it exists, and then offer user to store data locally. Unfortunately, my global address book is huge (ie 10min and the intCtr was 32k until it found my email address).

I simply ran an "If strSearchFor = .PrimarySmtpAddress then" with the strSearchFor being my email ...and it took forever.

Do you have any thoughts? Did you (or your friend) actually copy out the entire GAB?

The other thing is you're testing on LastName>0, is it a required field?

Thanks for your time.
Lex
Go to the top of the page
 
lex
post Jul 18 2017, 06:49 AM
Post#6



Posts: 671
Joined: 20-October 05



One other question...

I'm using

and for some emails SenderEmailAddress is returning something like

/O=EXCHANGELABS/OU=EXCHANGE ADMINISTRATIVE GROUP (FYDIBOHF23SPDLT)/CN=RECIPIENTS/CN=0830287153554C00898FA4EAB4FEEBF6-

Would you know why?

Thanks
Go to the top of the page
 
ADezii
post Jul 18 2017, 09:18 AM
Post#7



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


I'm not exactly sure how large your Global Address List is, but there is one thing that you can try. Declare the Array (arrUsers) as Dynamic, then Redimension it to the actual size of your GAL. I am referring to Code Lines 5 and 15 below:
CODE
Public Function fProcessGlobalAddressList() As Variant
Dim appOut As Object                        'Outlook Application Object
Dim oGAL As Object                          'Global Address List Reference
Dim oContact As Object                      'Contacts in Global Address List
Dim oUser As Object
Dim arrUsers() As String                    '2-Dimensional Array, 1st Dimension
                                            'holds the Name, 2nd SMTP Address
Dim lngIndex As Long                        'Len(<Last Name>) in GAL > 0
Dim intCtr As Long                          'ALL Items in Global Address List
Const conEXCHANGE_USER As Byte = 0          'Exchange User in same Forest

Set appOut = CreateObject("Outlook.Application")
Set oGAL = appOut.GetNameSpace("MAPI").AddressLists("Global Address List").AddressEntries

'Redimension the Array here to the actual Global Address List Count
ReDim arrUsers(1 To oGAL.Count, 1 To 2) As String

For intCtr = 1 To oGAL.Count
  Set oContact = oGAL.Item(intCtr)
    If oContact.AddressEntryUserType = conEXCHANGE_USER Then
      Set oUser = oContact.GetExchangeUser
        If Len(oUser.LastName) > 0 Then
          lngIndex = lngIndex + 1
          Cells(lngIndex, "A").Value = oUser.Name
          'Debug.Print Format$(lngIndex, "000"), oUser.Name, oUser.PrimarySMTPAddress
            With oUser
              arrUsers(lngIndex, 1) = .Name
              arrUsers(lngIndex, 2) = .PrimarySMTPAddress
            End With
        End If
    End If
Next intCtr

appOut.Quit

fProcessGlobalAddressList = arrUsers()

Set appOut = Nothing
Set oGAL = Nothing
Set oContact = Nothing
Set oUser = Nothing
Erase arrUsers
End Function
Go to the top of the page
 
lex
post Jul 19 2017, 07:24 PM
Post#8



Posts: 671
Joined: 20-October 05



I hate to look a gift horse in the mouth, but (unless I'm misunderstanding) the below doesn't answer my (maybe unclear) question. The problem that I'm having is that it takes on the order of 15 minutes to run the code to get the GAL. The one thing I just thought of, is that I'm using a wifi right now. I don't know if the GAL is local or on the cloud - or if the following helps - but I tried Google Speedtest and got 25.14 Mbps.

Do you know how I could possibly speed it up?

Thanks again
Go to the top of the page
 
ADezii
post Jul 20 2017, 06:12 AM
Post#9



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
Do you know how I could possibly speed it up?

Not really sure but there is one approach that could speed things up very quickly, assuming your GAL is relatively 'static'. Even if the GAL isn't predominately static, you could add new entries to the Table directly afterwards. The idea is to Load the entire GAL into a Table (Username & E-Mail Address) which will take a significant amount of time. Once this has been completed, Index the [Username] and [EMail] Fields as Unique with No Duplicates. Searching these Indexed Fields should be very efficient and fast. From this point on, reference this Table and not the GAL directly. If you need help with the Coding, can I can assist.

P.S. - Should you decide to take this approach, you would simply access the GAL then populate a Table with the Username and EMail Address within the Loop. You could eliminate the use of the 2-Dimensional Array also, execute the Code directly, and remove the Function Call and its Definition.
Go to the top of the page
 
lex
post Jul 24 2017, 08:26 PM
Post#10



Posts: 671
Joined: 20-October 05



Hey there, ADezii. Thanks again for your help. I have a few comments/questions

The odd thing here (with speed of searching the GAL) is that I can use the canned macro AddContactFromOutlook, it opens the below dialog, I'm able to search, and its very fast. I'm not sure why the below code runs slow? Maybe the code for the Outlook dialog is running on the server?

Attached File  2017_07_24_21_19_17.jpg ( 200.11K )Number of downloads: 4


If I assume the GAL is local, it shouldn't need updating very often. I think the answer to the question "when do I update the local GAL with corporate GAL updates and minimize user-speed issues?" is that I should update the GAL with my machine periodically ... unless you have a better solution.

Also, I'm assuming I should maintain both my local Contacts table and the local GAL separately. What do you think?

Couple considerations
1.not every contact within the GAL is in the business area with which I'm working
2.there would have to be some mapping between them and there may be business differences between them.

Note that I'm creating a system which imports emails from the users Outlook as "comments". Also, I think I should save locally all contact info (not just name/email) from GAL.

Assuming the following (GAL, GAL_local, tblContacts), the routines needed
1. check concurrancy between GAL and GAL_local. when changes (based on your suggested key) are found, I should add records to the GAL_local.
2. check for differences (ie phone #'s, etc) between existing GAL and GAL_local, assume anything changed is new and replace record?
2. on adding of an email as a comment, and a new name-email key is not found in tblContacts, but is found within GAL_local add to tblContacts.
3. on adding of an email as a comment, and a new name-email key is not found in tblContacts, and is not found within GAL_local, Open AddNewContact form.

I appreciate you helping me with the code. I really want to try to do as much as I can myself (I don't learn it if I don't). At this point, I'd just appreciate your commenting on the above - to give me a reality check.

Thank you so much.
Lex
Go to the top of the page
 
ADezii
post Jul 25 2017, 09:05 AM
Post#11



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


I hesitate to offer any further comments at this point. I simply fell that down-and-dirty testing among different approaches is what is needed from now on. From what you are saying, the 'canned Macro' appears to be very fast and efficient. Without a large GAL to test against, it is very difficult to offer any opinions.
Go to the top of the page
 
lex
post Jul 25 2017, 03:06 PM
Post#12



Posts: 671
Joined: 20-October 05



Thanks for the advice you have given!! I really appreciate it.

Have a great day.

Lex
Go to the top of the page
 
ADezii
post Jul 25 2017, 04:29 PM
Post#13



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 08:38 PM