Consulting

Results 1 to 20 of 20

Thread: Getting the TO-address in Outlook using VBA

  1. #1
    VBAX Regular
    Joined
    Sep 2012
    Posts
    11
    Location

    Question Getting the TO-address in Outlook using VBA

    Hi

    How can I get the email address when Outlook has not recognized the address as an email?

    I use the following code:
    Set olObj = Application.ActiveInspector.CurrentItem
    HisMail = olObj.Recipients.Item(1).Address

    But it only works, when Outlook has recognized the address as an email (i.e. when it is underlined in Outlook). Not when it's just normal text - then I get an error (Run-Time error '440').

    Can anybody help me?

  2. #2
    VBAX Regular
    Joined
    Sep 2012
    Posts
    11
    Location
    This is still very relevant for me. Can anybody help me?

  3. #3
    Maybe if you posted complete code rather than a snippet, it would be easier to help you.

  4. #4
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    [vba]olObj.recipients(1).AddressEntry.GetExchangeUser.PrimarySmtpAddress[/vba]
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  5. #5
    VBAX Expert JP2112's Avatar
    Joined
    Oct 2008
    Location
    Astoria, NY
    Posts
    590
    Location
    Quote Originally Posted by BrianMH
    [vba]olObj.recipients(1).AddressEntry.GetExchangeUser.PrimarySmtpAddress[/vba]
    As long as OP is using Outlook 2007+.
    Regards,
    JP

    Read the FAQ
    Getting free help on the web
    My website
    Please use [vba][/vba] tags when posting code

  6. #6
    VBAX Regular
    Joined
    Sep 2012
    Posts
    11
    Location
    Quote Originally Posted by Crocus Crow
    Maybe if you posted complete code rather than a snippet, it would be easier to help you.
    It is more or less all my code:

    [VBA]Sub CheckMail()

    Dim olObj As Object
    'Note: Must have set reference to:
    'Microsoft Excel 14.0 Object Library
    'This is accessed in the VBE Editor
    'Under Tools -> References...

    Set olObj = Application.ActiveInspector.CurrentItem
    HisMail = olObj.Recipients.Item(1).Address
    Set olObj = Nothing

    MsgBox HisMail

    'I plan to do more here...

    End Sub
    [/VBA]

    BrianMH's solution didn't work for me. I'm running Outlook 2010 (Professional Plus)

  7. #7
    VBAX Regular
    Joined
    Sep 2012
    Posts
    11
    Location
    Any suggestions?

  8. #8
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    edited my code slightly. Try below. This is assuming the mail item is open. If you are talking about just a selected mail you need to use a different method. I googled GetExchangeUser.PrimarySmtpAddress and it is available in office 2010 as far as I can see.

    [VBA]Sub CheckMail()
    Dim hismail As String
    Dim olObj As MailItem
    'Note: Must have set reference to:
    'Microsoft Excel 14.0 Object Library
    'This is accessed in the VBE Editor
    'Under Tools -> References...

    Set olObj = Application.ActiveInspector.CurrentItem
    hismail = olObj.Recipients.Item(1).AddressEntry.GetExchangeUser.PrimarySmtpAddress
    Set olObj = Nothing

    MsgBox hismail

    'I plan to do more here...

    End Sub[/VBA]
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  9. #9
    VBAX Regular
    Joined
    Sep 2012
    Posts
    11
    Location
    Hi BrianMH

    It still doesn't work for me. I have tried to illustrate my problem in this image:

    I really can't figure out how to solve this :-/

  10. #10
    VBAX Regular
    Joined
    Sep 2012
    Posts
    11
    Location
    Here's the image:

  11. #11
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    You need to resolve the address first. Try this.

    [VBA]
    Sub CheckMail()

    Dim olObj As Object
    'Note: Must have set reference to:
    'Microsoft Excel 14.0 Object Library
    'This is accessed in the VBE Editor
    'Under Tools -> References...

    Set olObj = Application.ActiveInspector.CurrentItem
    olObj.Recipients.Item(1).Resolve
    HisMail = olObj.Recipients.Item(1).Address
    Set olObj = Nothing

    MsgBox HisMail

    'I plan to do more here...

    End Sub
    [/VBA]
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  12. #12
    VBAX Regular
    Joined
    Sep 2012
    Posts
    11
    Location
    It still does not work :-( I get an run-time error '438'

    I have also tried ResolveAll
    olObj.Recipients.ResolveAll

    But that will delete the email address if it's not in my address book...

  13. #13
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    If you click debug where does it show you getting runtime error 438?

    I can't seem to duplicate your error. When I run the code it automatically resolves the address. Perhaps that is because I'm on an exchange server, are you not?

    Maybe there is a workaround. Is there code that precedes this to create the email? What is it you are trying to do and what do you need to do with the email address?
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  14. #14
    VBAX Regular
    Joined
    Sep 2012
    Posts
    11
    Location
    I get the error when running the line "olObj.Recipients.Item(1).Resolve" (see below).

    [VBA]Sub CheckMail()

    Dim olObj As Object
    'Note: Must have set reference to:
    'Microsoft Excel 14.0 Object Library
    'This is accessed in the VBE Editor
    'Under Tools -> References...

    Set olObj = Application.ActiveInspector.CurrentItem
    olObj.Recipients.Item(1).Resolve '<- I GET THE ERROR HERE
    HisMail = olObj.Recipients.Item(1).Address
    Set olObj = Nothing

    MsgBox HisMail

    'I plan to do more here...

    End Sub [/VBA]

  15. #15
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    Are you manually typing the address in? If so what happens when you click the check names button?
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  16. #16
    VBAX Regular
    Joined
    Sep 2012
    Posts
    11
    Location
    Yes. I manually type the address.

    The reason I make the code is that I have a data base with email-addresses we have contacted on earlier occasions. I want to lookup the current email that I'm about to write to to see if any of my colleagues has contacted that person and/or organization before.

    If I click "Check names" it immediately resolve/underline the address i entered.

  17. #17
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    Well since you are manually typing the name anyway just hit check names and it will resolve for you and then you shouldn't get the error.
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  18. #18
    VBAX Regular
    Joined
    Sep 2012
    Posts
    11
    Location
    Yes, but that is an extra step for my colleagues who are using this macro in the end. And they are not to technical minded, so keeping it to a minimum would be good.

    But most importantly - it really bothers me that I can't solve this! ;-)

  19. #19
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    Ok. I have been able to reproduce the issue but only if I work fast enough to beat autoresolve (I think you must have this turned off). It is because when you type an email address it doesn't seem to become a recipient right away. Assuming they will only have one email address and this is part of a specific process you could have them enter the address they are sending to in a message box instead of into the to box. As below

    [vba]Sub CheckMail()

    Dim olObj As MailItem
    Dim HisMail As String
    'Note: Must have set reference to:
    'Microsoft Excel 14.0 Object Library
    'This is accessed in the VBE Editor
    'Under Tools -> References...


    Set olObj = Application.ActiveInspector.CurrentItem
    olObj.Recipients.Add (InputBox("Enter an email address"))
    olObj.Recipients(1).Resolve
    HisMail = olObj.Recipients(1).Address



    Set olObj = Nothing

    MsgBox HisMail

    'I plan to do more here...

    End Sub
    [/vba]
    There seems to be no way of activating check names programmatically so your options are either do as above or have people click check names. By the way it was really driving me nuts too.
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  20. #20
    VBAX Regular
    Joined
    Sep 2012
    Posts
    11
    Location
    I SOLVED IT! :-) Or actually - I posted the same question here.

    The solution is to save the mail first. Just add
    [VBA]olObj.Save[/VBA] before getting the email address. That is:
    [VBA]Sub CheckMail()

    Dim olObj As Object
    'Note: Must have set reference to:
    'Microsoft Excel 14.0 Object Library
    'This is accessed in the VBE Editor
    'Under Tools -> References...

    Set olObj = Application.ActiveInspector.CurrentItem
    olObj.Save
    HisMail = olObj.Recipients.Item(1).Address
    Set olObj = Nothing

    MsgBox HisMail

    'I plan to do more here...

    End Sub [/VBA]

    YES :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •