Get Contact from Outlook and enter into User Form

Posted on 2010-01-13
Medium Priority
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 & _

    '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), "  ", " ")
            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

Question by:Fi69
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.


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



Author Comment

ID: 26303023
Hmmmm...still errors.
LVL 17

Expert Comment

ID: 26303044
The problem is with the Application.GetAddress  This is not a recognised command in VBA for outlook
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

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

LVL 17

Expert Comment

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.

Accepted Solution

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


Author Closing Comment

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

621 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