Trying to pull outlook data into a form in excel. So user enters the user id and selects the button and pulls in the data. The code fails on this line
Set ActivePersonRecipient = DummyEMail.Recipients.Add(ToAddr)
Sub GetGlobalAdressDataOffice2013()
Dim I As Integer
Dim ToAddr As String
Dim ActivePersonVerified As Boolean
Dim ol As Object
Dim objOL As Object
Dim Manager As Object
Dim AliasRange As Range
Set ol = CreateObject("Outlook.Application")
Set DummyEMail = ol.CreateItem(olMailItem)
Dim o, AddressList, AddressEntry
starttime = Now
Lastrow = Cells(Rows.Count, "a").End(xlUp).Row
If Lastrow <= 2 Then
GoTo ExitOutlookEmail
Else
End If
For X = 3 To Lastrow Step 1
ToAddr = Range("C4" & X).Value
Set ActivePersonRecipient = DummyEMail.Recipients.Add(ToAddr)
ActivePersonRecipient.Type = olTo
'Resolve the recipient to ensure it is valid
ActivePersonVerified = ActivePersonRecipient.Resolve
'If valid, use the AddressEntry property of the recipient to return an AddressEntry object
If ActivePersonVerified Then
Set oAE = ActivePersonRecipient.AddressEntry
'Use the GetExchangeUser method of the AddressEntry object to retrieve the ExchangeUser object for the recipient.
Set oExUser = oAE.GetExchangeUser
'Write the properties of the ExchangeUser object to adjacent columns on the worksheet.
If Left(ActivePersonRecipient.Address, 3) = "/o=" Then
Range("c5" & X).Value = oExUser.FirstName
Range("c6" & X).Value = oExUser.LastName
Range("cf" & X).Value = oExUser.Alias
Range("c7" & X).Value = oExUser.Email
Range("c8" & X).Value = oExUser.GetExchageUserManager.Name
End If
End If
Range("f1").Value = X - 2
Next
ExitOutlookEmail:
' End of script, calculate run time and present total time in a message box
endtime = Now
totaltime = Format(endtime - starttime, "HH:MM:SS")
MsgBox ("Total run time " & totaltime)
Set DummyEMail = Nothing
Set ol = Nothing
End Sub
Set ActivePersonRecipient = DummyEMail.Recipients.Add(ToAddr)
Sub GetGlobalAdressDataOffice2013()
Dim I As Integer
Dim ToAddr As String
Dim ActivePersonVerified As Boolean
Dim ol As Object
Dim objOL As Object
Dim Manager As Object
Dim AliasRange As Range
Set ol = CreateObject("Outlook.Application")
Set DummyEMail = ol.CreateItem(olMailItem)
Dim o, AddressList, AddressEntry
starttime = Now
Lastrow = Cells(Rows.Count, "a").End(xlUp).Row
If Lastrow <= 2 Then
GoTo ExitOutlookEmail
Else
End If
For X = 3 To Lastrow Step 1
ToAddr = Range("C4" & X).Value
Set ActivePersonRecipient = DummyEMail.Recipients.Add(ToAddr)
ActivePersonRecipient.Type = olTo
'Resolve the recipient to ensure it is valid
ActivePersonVerified = ActivePersonRecipient.Resolve
'If valid, use the AddressEntry property of the recipient to return an AddressEntry object
If ActivePersonVerified Then
Set oAE = ActivePersonRecipient.AddressEntry
'Use the GetExchangeUser method of the AddressEntry object to retrieve the ExchangeUser object for the recipient.
Set oExUser = oAE.GetExchangeUser
'Write the properties of the ExchangeUser object to adjacent columns on the worksheet.
If Left(ActivePersonRecipient.Address, 3) = "/o=" Then
Range("c5" & X).Value = oExUser.FirstName
Range("c6" & X).Value = oExUser.LastName
Range("cf" & X).Value = oExUser.Alias
Range("c7" & X).Value = oExUser.Email
Range("c8" & X).Value = oExUser.GetExchageUserManager.Name
End If
End If
Range("f1").Value = X - 2
Next
ExitOutlookEmail:
' End of script, calculate run time and present total time in a message box
endtime = Now
totaltime = Format(endtime - starttime, "HH:MM:SS")
MsgBox ("Total run time " & totaltime)
Set DummyEMail = Nothing
Set ol = Nothing
End Sub