Counting Emails in Subfolder automatically in Excel

Question

I am trying to get a cell in excel to display a total count of emails in a given subfolder from a specific outlook account (which is a shared mailbox).

Two questions: How to specify which Outlook email to look in, and how to resolve the compile error, "Next without For"

I have this so far:

Sub CountPBI()

  Dim olApp As Object
  Dim olNS As Object
  Dim olFolder As Object
  Dim olMail As Object
  Dim itemCount As Long
  Dim i As Long

  ' Create an Outlook application object
  Set olApp = CreateObject("Outlook.Application")

  ' Get the Outlook namespace
  Set olNS = olApp.GetNamespace("MAPI")

  ' Specify the folder (e.g., Inbox)
    Set Inbox = NS.GetDefaultFolder(olFolderInbox)
    Set SubFolder = Inbox.Folders("Projects").Folders("Natural Gas").Folders("Williams/Transco").Folders("NESE").Folders("Public Comments").Folders("Individuals")

      ' Check if it's a mail item (and potentially other criteria)
      If TypeName(olMail) = "MailItem" Then
          ' Add your counting logic here, e.g., check subject, date, etc.
          itemCount = itemCount + 1
      End If
  Next i

  ' Display the count in a cell (e.g., cell A1)
  ThisWorkbook.Sheets("Sheet1").Range("B4").Value = itemCount

  ' Clean up objects
  Set olMail = Nothing
  Set olFolder = Nothing
  Set olNS = Nothing
  Set olApp = Nothing

End Sub

You have Next i but no opening For and you do not set olMail anywhere in the code. Seems like a chunk is missing. And for accessing a shared mailbox: stackoverflow.com/questions/71413953/… — Tim Williams
– Tim Williams, Commented Jul 8 at 17:36
You could adapt the code on this link. davetallett26.github.io/excel-moveitems.html — MT1
– MT1, Commented Jul 8 at 19:18
Thanks! Unfortunately, I am unsure how to rewrite this to adapt it. By new to VBA, I mean very new with little to no coding experience outside of very basic automatic in Python. — Cheryl Sandrow
– Cheryl Sandrow, Commented Jul 9 at 17:28

Michal · Accepted Answer · 2025-07-10 03:17:51Z

0

Not sure if it's going to work with shared mailboxes as I have no way to test it but it allows for easy folder selection:

Sub CountPBI()

    Dim olApp As Object
    Dim olNS As Object
    Dim olFolder As Object
    Dim olMail As Object
    Dim itemCount As Long
    Dim i As Long

    ' Create an Outlook application object
    Set olApp = CreateObject("Outlook.Application")

    ' Get the Outlook namespace
    Set olNS = olApp.GetNamespace("MAPI")

    ' Prompt user to select a folder
    Set olFolder = olApp.Session.PickFolder

    ' Exit if no folder is selected
    If olFolder Is Nothing Then
        MsgBox "No folder selected.", vbExclamation
        Exit Sub
    End If

    ' Count mail items
    For i = 1 To olFolder.Items.Count
        Set olMail = olFolder.Items(i)
        If TypeName(olMail) = "MailItem" Then
            itemCount = itemCount + 1
        End If
    Next i

    ' Display the count in a cell
    ThisWorkbook.Sheets("Sheet1").Range("B4").Value = itemCount

    ' Clean up
    Set olMail = Nothing
    Set olFolder = Nothing
    Set olNS = Nothing
    Set olApp = Nothing

End Sub

answered Jul 10 at 3:17

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cheryl Sandrow Jul 10 at 15:16

Michael, that works! I can select the right folder to pull it in. I am just not sure how to run the Macro from the spreadsheet itself, i.e., I have to run the module from Macro window itself. =CountPBI() in a cell returns an error.

Tim Williams Jul 16 at 16:45

"=CountPBI() in a cell returns an error" - if you want to call a macro from a worksheet cell the called method needs to be a Function, not a Sub, and it should return the value to the calling cell. A Function called from a worksheet formula can't directly update the worksheet - that is one of the limitations of calling a Function that way.

Collectives™ on Stack Overflow

Counting Emails in Subfolder automatically in Excel

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related