Solved

How to get users email default address in Access 2003?

Posted on 2015-02-23
23
130 Views
Last Modified: 2016-02-11
Hi Experts,
I am trying to retreive users default email address with the following code, however I'm getting a popup security warning, how can I avoid it?
MsgBox CreateObject("Outlook.Application").GetNamespace("MAPI").CurrentUser.Address

Open in new window

0
Comment
Question by:bfuchs
  • 11
  • 11
23 Comments
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 458 total points
ID: 40626642
You cannot avoid it.
You are accessing the Address Book, which will ALWAYS alert the user in the version you are using.
You may be able to install third-party shims such as ClickYes to defeat this behavior, or upgrade to Office 2013, which for the first time since Office 2000 SP3 permits this to be turned off.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 42 total points
ID: 40626657
I've been using vbMAPI (www.everythingaccess.com) for working with Outlook. It's simple and easy to use, requires no external deployment, and takes the headache out of dealing with Outlook. For the price, it's hard to beat.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40627000
Hi Experts,

I'm looking to save all users emails (along with other info like username ipaddress etc..) in a table, this would be done as they log in to access app.

Therefore the options you're suggesting would be little complicate, as these would require setting up in each users pc.

Is there something I can deploy on my pc that will check every pc that's on the network and give me this info?
(The way I have something called advance ip scanner, which gives me all ipaddresses on the network, that actually also includes pc name mac address manufacture etc...)

If I can get something like that, then it would't batter me if I have to click OK for the security message.

Thanks,
Ben
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 458 total points
ID: 40627007
If you are in a domain situation and run Exchange then you probably can get that done without need for Access or Outlook.  Those things are LDAP queries against Active Directory.  And given that's what they are, why are you looking to pull them out?

Look here nevertheless
http://community.spiceworks.com/scripts/show/76-list-all-ad-email-addresses-including-aliases
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 458 total points
ID: 40627058
Change the output away from the root of c:\ to c:\temp and throw in the lines for objUser.SAMAccountName and it works pretty kick-arse, with a few tweaks.  Save it into a .vbs file and look for the result in c:\temp.
Enjoy.

'==================================================================================================
'
' VBScript Source File 
'
' NAME: LISTPROXYADDRESSES.VBS
' VERSION: 0.9.1
' AUTHOR: Bharat Suneja , Nick Phipps
' CREATE DATE  : 05/06/2004
' LAST MODIFIED : 19/04/2011
 '==================================================================================================
' COMMENT: 
'   
'==================================================================================================

'Set up constant for deleting values from multivalued attribute memberOf

Const ADS_PROPERTY_NOT_FOUND  = &h8000500D
Const ADS_UF_ACCOUNTDISABLE = 2                       'For UserAccountControl
Const strX400Search = "X400"
'______________________________________________________

'### Change the following line to change the output file path and name
strOutput = "C:\temp\email_addresses.txt"

'### Change the following line to "True" to change output to a dialog instead of a TXT file
boolTestMode = false

'Set RootDSE
Set objRootDSE = GetObject("LDAP://rootDSE")
strDomain = objRootDSE.Get("defaultNamingContext")
strADPath = "LDAP://" & strDomain
'wscript.Echo strADPath
Set objDomain = GetObject(strADPath)
'wscript.echo "objDomain: " & objDomain.distinguishedName

'Setup ADODB connection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

'Execute search command to look for Contacts & Groups
    objCommand.CommandText = _
      "<" & strADPath & ">" & ";(&(|(objectClass=contact)(objectClass=group))(mail=*))" & ";distinguishedName,displayName,mail,proxyAddresses;subtree"

    'Execute search to get Recordset
    Set objRecordSet = objCommand.Execute
    
        
        'Start procedure
        
    strResult = strResult & VbCrLf & "Domain: " & strDomain

    strResult = strResult & VbCrlf &  "#Total Records Found (other accounts): " & objRecordSet.RecordCount & VbCrlf
    AddressCount = 0

       While Not objRecordSet.EOF 'Iterate through the search results
 
            strUserDN = objRecordSet.Fields("distinguishedName")     'Get User's distinguished name from Recordset into a string
            set objUser= GetObject("LDAP://"& strUserDN & "")         'Use string to bind to user object
            


                       strResult = strResult & VbCrlf &  "cn: " & objUser.cn
                       strResult = strResult & VbCrlf &  "Username: " & objUser.SAMAccountNAme
                       strResult = strResult & VbCrlf &  "mail: " & objUser.mail
                       arrProxyAddresses = objRecordSet.Fields("proxyAddresses")
                       If IsArray(objRecordSet.Fields("proxyAddresses")) Then
                       strResult = strResult & VbCrLf & "Proxy Addresses" 
               
                          For Each ProxyAddress in arrProxyAddresses
                          
                            'Sub: Check X400 
                             If InStr(ProxyAddress, strX400Search) <> 0 Then 
                        'Wscript.Echo "#This was an x400"
                     Else
                                     strResult = strResult & VbCrlf &  proxyAddress
                              End If   'Ends loop for X400 address
                Next

            Else
                strResult = strResult & VbCrlf &  "#Object does not have proxy addresses"
            End If
                strResult = strResult &  VbCrLf

     objRecordSet.MoveNext
Wend

'*************************************
'Begin second query for users
varDisabledCounter = 0                  

'Execute search command to look for user
    objCommand.CommandText = _
      "<" & strADPath & ">" & ";(&(objectClass=user)(mail=*))" & ";distinguishedName,displayName,mail,proxyAddresses;subtree"

    'Execute search to get Recordset
    Set objRecordSet = objCommand.Execute
    
    strResult = strResult & vbCrlf &  "#Users"
    strResult = strResult & VbCrlf &  "#Total Records Found (users): " & objRecordSet.RecordCount & VbCrlf
    


       While Not objRecordSet.EOF 'Iterate through the search results
            strUserDN = objRecordSet.Fields("distinguishedName")     'Get User's distinguished name from Recordset into a string
            set objUser= GetObject("LDAP://"& strUserDN & "")         'Use string to bind to user object
            
            
            If objUser.AccountDisabled = TRUE Then                    'If User account disabled, then skip proxy address enum
               varDisabledCounter = varDisabledCounter + 1
               strResult2 = strResult2 & VbCrLf & varDisabledCounter & " " & objUser.displayName & VbCrLf
               
               strResult2 = strResult2 & "cn: " & objUser.cn
                       strResult2 = strResult2 &  "Username: " & objUser.SAMAccountNAme
                       strResult2 = strResult2 & VbCrlf &  "mail: " & objUser.mail
                       arrProxyAddresses = objRecordSet.Fields("proxyAddresses")
                       If IsArray(objRecordSet.Fields("proxyAddresses")) Then
                       strResult2 = strResult2 & VbCrLf & "Proxy Addresses" 
                       
               
                          For Each ProxyAddress in arrProxyAddresses
                            'Sub: Check X400
                             If InStr(ProxyAddress, strX400Search) <> 0 Then 
                        'Wscript.Echo "#This was an x400"
                     Else
                         strResult2 = strResult2 & VbCrlf &  proxyAddress
                                 AddressCount = AddressCount + 1
                              End If   'Ends loop for X400 address
                          Next
                              Else
                                  strResult2 = strResult2 & VbCrLf &  "#Object does not have proxy addresses"
                          End If
                              strResult2 = strResult2 &  VbCrLf
               
               
               
               
               
            Else
            


                       strResult = strResult & VbCrlf &  "cn: " & objUser.cn
		       strResult = strResult & VbCrlf &  "cn: " & objUser.SAMAccountName
                       strResult = strResult & VbCrlf &  "mail: " & objUser.mail
                       arrProxyAddresses = objRecordSet.Fields("proxyAddresses")
                       If IsArray(objRecordSet.Fields("proxyAddresses")) Then
                       strResult = strResult & VbCrLf & "Proxy Addresses" 
                          
                          For Each ProxyAddress in arrProxyAddresses
                            'Sub: Check X400
                             If InStr(ProxyAddress, strX400Search) <> 0 Then 
                        'Wscript.Echo "#This was an x400"
                     Else
                         strResult = strResult & VbCrlf &  proxyAddress
                                 AddressCount = AddressCount + 1
                              End If   'Ends loop for X400 address
                          Next
                              Else
                                  strResult = strResult & VbCrLf &  "#Object does not have proxy addresses"
                          End If
                              strResult = strResult &  VbCrLf
                
          End If   'End check for disabled user 
            
     objRecordSet.MoveNext 
Wend  'End second query for users

              
strResult = "Users, Groups & Contacts" & VbCrLf & "-------------------------" & VbCrLf & strResult
strResult = strResult & VbCrLf & "Disabled Users" & VbCrLf & "-------------------------" & VbCrLf & strResult2

'Output results
if boolTestMode then
     WScript.Echo strResult
 else 
    'Output to a text file
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    Set objOutputFile = objFileSystem.CreateTextFile(strOutput)
    objOutputFile.Write strResult
end if

WScript.Echo "Done."

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
ID: 40629171
@Nick,
why are you looking to pull them out?
The manager is putting a list together of all employees info, and email is one of the info he wants to have.
FYI- see link below.
http://www.experts-exchange.com/Database/MS_Access/Q_28620668.html#a40620458

While trying your suggestion I got attached.
Untitled.png
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 458 total points
ID: 40629243
The suggestion is dependent on
1) An Active Directory, and
2) Exchange onsite

The error gets tossed at
Set objRootDSE = GetObject("LDAP://rootDSE")

If you don't have Exchange onsite, then that is expected.
Without Exchange, you likely have to hit each Outlook user as they are logged on to get what you want, as without Exchange, the only place email will be stored is in the Outlook profiles of each user
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40629606
Hi Nick,

I spoke to our IT guy and he said our server is neither Exchange nor Active directory, just regular Pop3 server.
I guess this will not work, do you have another solution up your sleeves?

Thanks,
Ben
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 458 total points
ID: 40629665
I have crafted code in the past that walked down all the items in a person's Inbox and harvested email addresses.
The Outlook Contacts folder can also be traversed.
Do you have a presently accurate contact for everyone?

Code could be crafted to send emails to everyone in your contacts based upon a criteria with a subject of 'confirm address'  Your inbox could then be walked for those messages and Outlook details harvested from that.
Whoever you didn't get a message from would then be someone you knew needed updating.

It could then be iterative, sending to anyone who hadn't sent a reply and based against an Excel sheet, until a valid reply had been received from all.

Not simple, but doable.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40629766
Well, this sounds like something that will have to be manually done & maintained.
Since we already have a list of all users with their IP Addresses, I would like something that would associate all ip addresses with their email.

Just thinking, perhaps the original code I posted could somehow be handled by adding a send keys {"Enter"} after the security pops up, the question would be how do we have the code wait for that popup to appear?
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 458 total points
ID: 40629893
LOL.
If code that CAUSES a security warning could DISMISS it, it wouldn't be much of a warning, would it?

Unlesd your IT guy has REALLY locked down the network, there will not be a rock-solid corellation between users and IP addresses.  DHCP is usually used to assign IPs.  The 'D' is for dynamic, and 'H' is for Host--meaning computer.  Without disallowing users from logging on to only a single computer and ensuring that THATcomputer will always have the same IP, thrre's no correlation
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 458 total points
ID: 40629896
And VBA + WIN7+ SendKeys = NumLock toggling on and off.  Makes users insane --avoid.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40629912
Hi,
it wouldn't be much of a warning, would it?
Yes, you right with this...

BTW, the security warning was removed in 2010 already , and maybe even earlier I just dont have a copy of 2007 to verify.

Wondering if Microsoft didn't came up with a patch to fix that annoying feature..

I saw on link below someone suggesting something to be installed, did you ever tried that, I just tried the code posted there and had the same issue.

http://www.vbaexpress.com/forum/showthread.php?9957-Solved-How-do-you-get-the-current-user-s-default-email-address

Re IP Address, we do ensure each user have always the same IP address (I am the one in charge of assigning an IP for every new user).
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 458 total points
ID: 40631178
BTW, the security warning was removed in 2010 already
Certainly not.
It's not removed in 2013, either, but that's the first version I have seen with options -- if anti-virus is installed -- that permits you to disable them.  I'll have to check if that was possible in 2010 and I missed it.  I doubt it, but I could be wrong.

Wondering if Microsoft didn't came up with a patch to fix that annoying feature..
Microsoft created that annoying feature.  It was Office 2000 SP3.
It was expressly created to throw a wrench into any coding that tried to use Outlook as a mass-mailer.
Programs like ClickYes and the MapiLabs offering were created to overcome it, which I noted way back at the beginning
(You may be able to install third-party shims such as ClickYes to defeat this behavior)
Here's what MS has to say about the warnings
https://msdn.microsoft.com/en-us/library/office/aa168346(v=office.11).aspx

So--where do we stand in regard to your original question?
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 458 total points
ID: 40631234
It looks like it could be disabled with valid ant-virus installed as far back as Outlook 2007
https://support.office.com/en-ca/article/I-get-warnings-about-a-program-accessing-e-mail-address-information-or-sending-e-mail-on-my-behalf-df007135-c632-4ae4-8577-dd4ba26750a2?CorrelationId=a6e98066-0959-454d-b7fa-a5f5b85fb8d8&ui=en-US&rs=en-CA&ad=CA

But the settings exist, and are on by default in 2007, 2010 and 2013.

2003, well, you are out of luck or in third-party land
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40632044
@Nick,
Certainly not.
I had used code for mass email that worked fine in 2000, after a while we had to move it due to space limitation of outlook 2k, and since we had this security issue in 2003,  I had to deploy that code in the single pc in our office that has 2010, and as far I know it worked without problems, will see when I have a chance to test that again there.

Regardless of that, by using a version of outlook that does not have the security issue, is it possible to have code that checks emails addresses of all users on the network?
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 458 total points
ID: 40632056
and as far I know it worked without problems
You can disable the messages in O2007+
I didn't realize those setting were disable-able that far back.
They exist but can be shut off.
Clearly that was done on your 2010 machine.

is it possible to have code that checks emails addresses of all users on the network?

You are not running Exchange you've said.
You are using a POP3, presumably third-party offsite, you've said.
So there is no accurate central repository of email addressing information to query.

That leaves hacking through the registry of each machine, of each user in HKEYUsers, and looking for email addresses and matching the addresses and GUIDs back to machine and username.

Likely more trouble than it is worth.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40632185
@Nick,

Just one more thing before finalize.
Did you looked at the site i posted above (ID: 40629912)?
I see they're mentioning about installing something optional that comes with office 2003 package, if I understood well, that looks like a suitable solution w/o involving third parties, just wonder where do I find that option to install it?

Thanks,
Ben
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 458 total points
ID: 40632213
No, they are discussing three different third-party options.
ClickYes is a little shim that dismisses on the security boxes as they are generated
The MapiLabs item is much more granular, but is the same idea
Redemption is a pay-for-it item that replaces Outlook in mailing code, thereby obviating the security dialogs.

None of them change the fact you don't have a good datasource to pull this data from.
It's tied up in each individual's Outlook profile.
Installing ClickYes and the security hole it opens is a LOT of overhead to get everyone's email address.
And everyone would need to open the database with the code with ClickYes running to activate any code you write.
And how often do you want this to update?

You create the ipaddress lists.
It would be much simpler to administratively have everyone send you an email on a set schedule with boilerplate text in it, and crawl the InBox on your machine.

I doubt others will have a better idea, but your next question should be "How can I automatically collect all the email addresses in my network if we DON'T use Exchange?"
Maybe someone will have a better idea.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40632269
I've requested that this question be closed as follows:

Accepted answer: 42 points for Nick67's comment #a40632213
Assisted answer: 42 points for Nick67's comment #a40626642
Assisted answer: 42 points for Scott McDaniel (Microsoft Access MVP - EE MVE )'s comment #a40626657
Assisted answer: 42 points for Nick67's comment #a40627007
Assisted answer: 42 points for Nick67's comment #a40627058
Assisted answer: 42 points for Nick67's comment #a40629243
Assisted answer: 42 points for Nick67's comment #a40629665
Assisted answer: 42 points for Nick67's comment #a40629893
Assisted answer: 41 points for Nick67's comment #a40629896
Assisted answer: 41 points for Nick67's comment #a40631178
Assisted answer: 41 points for Nick67's comment #a40631234
Assisted answer: 0 points for bfuchs's comment #a40632044
Assisted answer: 41 points for Nick67's comment #a40632056

for the following reason:

I guess all your answers are correct..
Thank you!
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40632268
Hi,
I guess close question was pressed by mistake as I really meant to accept those answers selected, please have this set as accepted.
Thank you.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40632270
Hi,
I guess close question was pressed by mistake as I really meant to accept those answers selected, please have this set as accepted.
Thank you.
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 40632271
Thank You!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now