PDA

View Full Version : Problems in Outlook.Application object in VBA



j990345
03-11-2011, 11:17 AM
Hi experts:

I have been googling for a solution for a while but still cannot solve my problems so I registered to hopefully get some helps from professionals.

Below is my code

Private Sub CommandButton1_Click()
Dim olApp As Object, oNS As Object, oAL As Object, oAE As Object
Set olApp = GetObject(, "Outlook.Application")
Dim i As Long
Dim n As Integer
Dim m As Integer
Dim r As Integer
Dim tmp1 As String
On Error GoTo XIT
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If
Set oNS = olApp.GetNamespace("MAPI")
oNS.Logon
oNS.Logon , , False, False ' Profile, Password, ShowDialog, NewSession
Set oAL = oNS.AddressLists("Global Address List")
Dim SH As Worksheet
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Dim destRng As Range
Dim destRng1 As Range
Set SH = ThisWorkbook.Sheets("Sheet1")
Set SH1 = ThisWorkbook.Sheets("Sheet2")
Set SH2 = ThisWorkbook.Sheets("Sheet3")
n = 1
r = SH2.Range("C9") ' r is where I specify the number of entries I want
m = Int((1 + r) / 2)
For i = 1 To m
SH.Range("A" & i ) = oAL.AddressEntries.Item(i).Name
tmp1 = oAL.AddressEntries.Item(i).Address
If IsNumeric(Right(tmp1, 7)) And Right(tmp1, 7) > 1000000 Then
SH.Range("B" & i) = Right(tmp1, 7)
Else
SH.Range("B" & i ) = Right(tmp1, Len(tmp1) - InStrRev(tmp1, "="))
End If
Next i
For i = m + 1 To r
SH1.Range("A" & (i - m)) = oAL.AddressEntries.Item(i).Name
tmp1 = oAL.AddressEntries.Item(i).Address
If IsNumeric(Right(tmp1, 7)) And Right(tmp1, 7) > 1000000 Then
SH1.Range("B" & (i - m)) = Right(tmp1, 7)
Else
SH1.Range("B" & (i - m)) = Right(tmp1, Len(tmp1) - InStrRev(tmp1, "="))
End If
Next i
Set olApp = Nothing
Set oNS = Nothing
Set oAL = Nothing
Set oAE = Nothing
Set SH2 = Nothing
Set SH = Nothing
Set SH1 = Nothing
Set destRng = Nothing
Set destRng1 = Nothing
End Sub
This piece of code draws Name field and Address field from Global Address List from outlook. However, I have got a problem where I can only pull a maximum of 30,000 entries (so variable r in the code is at most 30000) and I have a total of 110918 entries in GAL.

Could the experts here help to give me some hints to solve my problems please? Thanks so much!

Kenneth Hobs
03-11-2011, 06:40 PM
Dim r As Long

j990345
03-12-2011, 03:14 AM
Hi Ken, thanks so much for your helps! I totally forgot to check my var types :bow: