Getting all my emails from Outlook Sent Items folder

[hyperchondriac]

I'm trying to do some operations on the emails in my Sent Items folder, so I'm using the following code (copied from elsewhere) to get the emails in the folder.

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set olFolder = olNs.GetDefaultFolder(olFolderSentMail)

For i = olFolder.Items.Count To 1 Step -1
'Do stuff
Next i

However, when I run the code it only picks up the emails that are stored on my computer. I have several more years of emails stored on the Exchange server. Is there any way to get VBA to access every email on the Exchange server?

I'd eventually like to do it on my Inbox and sub-folders too, so a generic solution would be great. Thank you!

 

[hyperchondriac]

Well I solved this by just getting Outlook to download all my emails, although I'd be interested to know if there's a way of doing it in VBA.

 

[JEC]

Like this via VBA:
But you probably need to change some cache settings (under "account settings")to get access to emails which are not stored locally yet.

Sub jec()
 Dim it, x As Long
 ReDim ar(30000, 2) As Variant

 For Each it In CreateObject("outlook.application").GetNamespace("MAPI").Getdefaultfolder(5).items
    ar(x, 0) = x + 1
    ar(x, 1) = it.Subject
    ar(x, 2) = it.body
    x = x + 1
 Next
 
 Sheets(1).Cells(1, 1).Resize(x, 3) = ar
End Sub

Better would be to use power query if you are able to use the exchange connector: get data --> from other sources --> from microsoft exchange

 

[hyperchondriac]

Thanks for the reply, though my problem isn't the code so much as the fact I can't access the emails on the Exchange server that aren't on my machine. As I said, I did just change my settings to download all my historic emails.

Didn't think about using Power Query, that's an interesting idea. I've got a lot of folder though, so will need to look at how easy it is to pull them all out in one query.