I have a macro that pulls in every name in my work's Global address list into Excel. When it runs, when it tries to connect to the Global Address List, it gives a pop-up asking users permission to connect to the list. How do I stop this pop-up from happening?
See Code Below:
Sub GetOutlookAddressBook()
' Need to add reference to Outlook
'(In VBA editor Tools References MS Outlook #.# Library)
' Adds addresses to existing Sheet called Address and
' defines name Addresses containing this list
' For use with data Validation ListBox (Source as =Addresses)
On Error GoTo error
Dim objOutlook As Outlook.Application
Dim objAddressList As Outlook.AddressList
Dim objAddressEntry As Outlook.AddressEntry
Dim intCounter As Integer
Application.ScreenUpdating = False
' Setup connection to Outlook application
Set objOutlook = CreateObject("Outlook.Application")
Set objAddressList = objOutlook.Session.AddressLists("Global Address List")
Application.EnableEvents = False
' Clear existing list
Sheets("Address").Range("A:A").Clear
'Step through each contact and list each that has an email address
For Each objAddressEntry In objAddressList.AddressEntries
If objAddressEntry.Address <> "" Then
intCounter = intCounter + 1
Application.StatusBar = "Address no. " & intCounter & " ... " & objAddressEntry.Address
Sheets("Address").Cells(intCounter, 1) = objAddressEntry.Name
DoEvents
End If
Next objAddressEntry
' Define range called "Addresses" to the list of emails
Sheets("Address").Cells(1, 1).Resize(intCounter, 1).Name = "Addresses"
error:
Set objOutlook = Nothing
Application.StatusBar = False
Application.EnableEvents = False
End Sub
See Code Below:
Sub GetOutlookAddressBook()
' Need to add reference to Outlook
'(In VBA editor Tools References MS Outlook #.# Library)
' Adds addresses to existing Sheet called Address and
' defines name Addresses containing this list
' For use with data Validation ListBox (Source as =Addresses)
On Error GoTo error
Dim objOutlook As Outlook.Application
Dim objAddressList As Outlook.AddressList
Dim objAddressEntry As Outlook.AddressEntry
Dim intCounter As Integer
Application.ScreenUpdating = False
' Setup connection to Outlook application
Set objOutlook = CreateObject("Outlook.Application")
Set objAddressList = objOutlook.Session.AddressLists("Global Address List")
Application.EnableEvents = False
' Clear existing list
Sheets("Address").Range("A:A").Clear
'Step through each contact and list each that has an email address
For Each objAddressEntry In objAddressList.AddressEntries
If objAddressEntry.Address <> "" Then
intCounter = intCounter + 1
Application.StatusBar = "Address no. " & intCounter & " ... " & objAddressEntry.Address
Sheets("Address").Cells(intCounter, 1) = objAddressEntry.Name
DoEvents
End If
Next objAddressEntry
' Define range called "Addresses" to the list of emails
Sheets("Address").Cells(1, 1).Resize(intCounter, 1).Name = "Addresses"
error:
Set objOutlook = Nothing
Application.StatusBar = False
Application.EnableEvents = False
End Sub