?
Solved

Get Contact from Outlook and enter into User Form

Posted on 2010-01-13
7
Medium Priority
?
1,297 Views
Last Modified: 2012-05-08
Hi there.  I am only new to coding Excel.  

I have some code in Word that is attached to a command button. When I click the command button it opens up Outlook and I can select my contact and the name and address gets added to text boxes in my user form.

I tried putting this code directly into Excel, but I get an error at the following line.

straddress = Application.GetAddress(AddressProperties:=strCode, _
            UseAutoText:=False, DisplaySelectDialog:=1, _
            RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
   
Is there a workaround with this code, or does it have to be a complete revamp to work in with Excel? If it is a revamp can someone help me out with an example or guide me to an existing example?

Private Sub CommandButton1_Click()
Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myAddressList = myNameSpace.AddressLists("Business Contacts")
Set myAddressEntries = myAddressList.AddressEntries





    Dim strCode As String, straddress As String
    Dim iDoubleCR As Integer
    Dim vAddressLines
    Dim strNewLines() As String
    Dim i As Integer
    Dim j As Integer

    
    'Set up the formatting codes in strCode
    strCode = "<PR_COMPANY_NAME>" & vbCrLf & _
            "<PR_GIVEN_NAME>" & " <PR_SURNAME>" & vbCrLf & _
            "<PR_POSTAL_ADDRESS>"

    'Display the 'Select Name' dialog, which lets the user choose
    'a name from their Outlook address book
    straddress = Application.GetAddress(AddressProperties:=strCode, _
            UseAutoText:=False, DisplaySelectDialog:=1, _
            RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
    
    'If user cancelled out of  'Select Name' dialog, quit
    If straddress = "" Then Exit Sub
    
    vAddressLines = Split(straddress, vbCrLf) 'create an arry of lines
    For i = 0 To UBound(vAddressLines)
        If Trim$(vAddressLines(i)) <> "" Then 'ignore blank lines
            ReDim Preserve strNewLines(j)
            strNewLines(j) = Trim$(vAddressLines(i))
            Do While InStr(strNewLines(j), "  ") > 0 'eliminate all double spacing
                strNewLines(j) = Replace(strNewLines(j), "  ", " ")
            Loop
            j = j + 1
        End If
    Next i
    
   ' straddress = Join(strNewLines, vbCrLf) 'recreate tidied string
    'Insert the modified address at the current insertion point
    
    

    Dim temp
    Dim tmp1
    Dim tmp2
    Dim tmp3
    Dim tmp4
    Dim tmp5

    temp = straddress

    tmp1 = Trim(Mid(temp, 1, InStr(1, temp, vbCrLf) - 1))
    tmp2 = Trim(Mid(temp, InStr(1, temp, vbCrLf) + 2))
    tmp3 = Trim(Mid(tmp2, InStr(1, tmp2, vbCrLf) + 2))
    
    Me.txtClient = tmp1
    Me.txtAddress = tmp3

Open in new window

0
Comment
Question by:Fi69
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26302986
Hello Fi69,

If you are running this from Excel, then the Application object refers to Excel.

Try:

straddress = myOlApp.GetAddress(AddressProperties:=strCode, _
            UseAutoText:=False, DisplaySelectDialog:=1, _
            RecentAddressesChoice:=True, UpdateRecentAddresses:=True)

Regards,

Patrick
0
 

Author Comment

by:Fi69
ID: 26303023
Hmmmm...still errors.
0
 
LVL 17

Expert Comment

by:wobbled
ID: 26303044
The problem is with the Application.GetAddress  This is not a recognised command in VBA for outlook
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:wobbled
ID: 26303057
sorry ignore above - the VBA IDE can't recognise that command but the MSDN site shows it???

http://msdn.microsoft.com/en-us/library/aa172199%28office.11%29.aspx
0
 
LVL 17

Expert Comment

by:wobbled
ID: 26303130
I have taken both the GetAddress and AddAddress as shown on the MSDN sites and even running within Outlook 2007 VBA I can not get these to work.  I'm wondering if this is a security control on outlook 07 to prevent this.
0
 
LVL 5

Accepted Solution

by:
trungk43 earned 750 total points
ID: 26367981
You cannot use Application.GetAddress in Excel VBA. There is another way to get Contacts of Outlook.

Please refer to this link

http://www.codeforexcelandoutlook.com/blog/2008/09/export-outlook-contacts-to-excel/
0
 

Author Closing Comment

by:Fi69
ID: 31676617
This answer pointed me in the right direction,but I still haven't mastered what I need to do.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question