How to retrieve emails from Outlook using Excel VBA?

Question

I want to retrieve emails from Outlook based on certain conditions. I denote a certain folder in my code. In the example below the folder is "PRE Customer". I would like to retrieve emails from Inbox or better from all Outlook folders. My inbox consists of many subfolders. I may not know all the subfolders names as there are many users and someone can have the emails in Personal Folders. The problem line is marked with a comment. ``` Sub GetFromInbox() Dim olApp As Outlook.Application Dim olNs As Namespace Dim Fldr As MAPIFolder Dim olMail As Variant Dim i As Integer Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") 'Below is the line I have problem with Set Fldr = olNs.GetDefaultFolder(olFolderInbox).Folders("PRE Customer") i = 1 x = Date For Each olMail In Fldr.Items If InStr(olMail.Subject, "transactions") > 0 _ And InStr(olMail.ReceivedTime, x) > 0 Then ActiveSheet.Cells(i, 1).Value = olMail.Subject ActiveSheet.Cells(i, 2).Value = olMail.ReceivedTime Active…

Accepted Answer

Just loop through all the folders in `Inbox`. Something like this would work.

Edit1: This will avoid blank rows.

```
Sub test()
    Dim olApp As Outlook.Application, olNs As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder, olMail As Outlook.MailItem
    Dim eFolder As Outlook.Folder '~~> additional declaration
    Dim i As Long
    Dim x As Date, ws As Worksheet '~~> declare WS variable instead
    Dim lrow As Long '~~> additional declaration

Set ws = Activesheet '~~> or you can be more explicit using the next line
    'Set ws = Thisworkbook.Sheets("YourTargetSheet")
    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    x = Date

For Each eFolder In olNs.GetDefaultFolder(olFolderInbox).Folders
        'Debug.Print eFolder.Name
        Set olFolder = olNs.GetDefaultFolder(olFolderInbox).Folders(eFolder.Name)
        For i = olFolder.Items.Count To 1 Step -1
            If TypeOf olFolder.Items(i) Is MailItem Then
                Set olMail = olFolder.Items(i)
                If InStr(olMail.Subject, "transactions") > 0 _
                And InStr(olMail.ReceivedTime, x) > 0 Then
                    With ws
                       lrow = .Range("A" & .Rows.Count).End(xlup).Row
                       .Range("A" & lrow).Offset(1,0).value = olMail.Subject
                       .Range("A" & lrow).Offset(1,1).Value = olMail.ReceivedTime
                       .Range("A" & lrow).Offset(1,2).Value = olMail.SenderName
                    End With
                End If
            End If
        Next i
        Set olFolder = Nothing
    Next eFolder
End Sub
```

Above takes care of all subfolders in `Inbox`. Is this what you're trying?

How to retrieve emails from Outlook using Excel VBA?

Solution

Problem

Related problems