Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Anybody know a quick and easy way to find the email address of a sender? I'm trying to get to this without outlook giving me any security messages. I'm currently using Windows XP, outlook 2003 and excel 2003.


    I have and excel user form that people can submit. Instead of asking the user to provide their own email address. I'd rather see if there is a way I can find it on their pc or in their outlook and prefill it. When the user form is submitted, it will be sending and email via CDO. I'm not sure if the settings at my work allow for the MAPI send message configurations. I've tried a few different code examples and keep getting error messages that I'm not able to figure out. So any suggestions to find this would be very much appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Many users don't have a single e-mail address - I have four different ones. I wouldn't want a program to decide which of those to use, if it could find them...

  3. #3
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='775933' date='19-May-2009 14:20']Many users don't have a single e-mail address - I have four different ones. I wouldn't want a program to decide which of those to use, if it could find them...[/quote]


    Hans,

    I do agree with that statement especially if I were using a program from my home pc. I wouldn't want it to choose and email address for me. However at my work everybody has a single email address and I'm looking for a way to identify that with out generating and outlook security prompt. More or less trying to reduce the number of inputs from the user of the form to make the form more streamline and quicker to fill out.

    Thanks!!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There's probably a way to get the e-mail address from Active Directory, but I can't help you with that. Hopefully someone else will know.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='b0bito' post='775944' date='19-May-2009 12:48']at my work everybody has a single email address and I'm looking for a way to identify that with out generating and outlook security prompt.[/quote]
    Doesn't CDO give you pretty easy access to the GAL? You may be able to hook a drop down into it, so the user selects their address from a list. Or if you want to instance Access in your code, through Access 2003 there is an exchange wizard which lets you access the GAL - see Post 262918.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='JohnBF' post='775965' date='19-May-2009 15:33']Doesn't CDO give you pretty easy access to the GAL? You may be able to hook a drop down into it, so the user selects their address from a list. Or if you want to instance Access in your code, through Access 2003 there is an exchange wizard which lets you access the GAL - see Post 262918.[/quote]


    Forgive me for sounding stupid but what is the GAL?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    GAL = Global Address List (kept on the Exchange Server)

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I must be missing something - if they are emailing the form to you, why does your code need to find their email address? It will send from their address by default.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Never mind - half asleep and missed the CDO part!
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could try a function like this - pass it the login id:
    Code:
    Function GetEmailAddy(strUser) As String
    
       On Error Resume Next
       Dim objConnection, objCommand, objRecordset, objRoot, strDomain
       Const ADS_SCOPE_SUBTREE = 2
       Set objRoot = GetObject("LDAP://rootDSE")
       'Work in the default domain
       strDomain = objRoot.Get("defaultNamingContext")
       
       Set objConnection = CreateObject("ADODB.Connection")
       Set objCommand = CreateObject("ADODB.Command")
       objConnection.Provider = "ADsDSOObject"
       objConnection.Open "Active Directory Provider"
       Set objCommand.ActiveConnection = objConnection
       
       objCommand.Properties("Page Size") = 1000
       objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
       
       objCommand.CommandText = _
    	  "SELECT mail, ADsPath FROM 'LDAP://" & strDomain & _
    		  "' WHERE Name='" & strUser & "'"
       Set objRecordset = objCommand.Execute
       Debug.Print objRecordset.RecordCount
       objRecordset.MoveFirst
       GetEmailAddy = objRecordset("mail")
       objRecordset.Close
       objConnection.Close
    End Function
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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