Need some assitance with pulling data from outlook 2013 into excel

Nei

New Member
Joined
Feb 26, 2009
Messages
20
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

Attachments

  • email data.png
    email data.png
    10.3 KB · Views: 5

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Forum statistics

Threads
1,214,808
Messages
6,121,681
Members
449,048
Latest member
81jamesacct

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top