How can I extract the email address for a store?

Question

In my company we have a number of different email addresses which go to "Shared Inbox" accounts that are managed by a few people.

We want to keep track of how many emails are in each Inbox.

A user might look after several different shared inboxes, and no one user has access to the same set of inboxes.

The VBA code I have loops through all of the logged-in user's shared inboxes and counts how many emails are in each inbox folder which isn't their own "personal" inbox and logs that data to a table using a separate procedure:

Sub WorkPosition()

    Dim ThisUser As String
    Dim Mailbox As Object
    Dim MailBoxName As String
    Dim oStore As Outlook.Store
    Dim olFolder As Outlook.Folder

    ThisUser = UCase(Environ("UserName"))

    For x = Application.Session.Stores.Count To 1 Step -1
        Set Mailbox = Nothing
        On Error Resume Next
        Set Mailbox = Application.Session.Stores(x)
        On Error GoTo 0
        If Mailbox Is Nothing Then GoTo SkipMailbox
        MailBoxName = Mailbox
        If InStr(UCase(Mailbox), ThisUser) = 0 Then
            Set olFolder = Mailbox.GetDefaultFolder(olFolderInbox)
            Set objItems = olFolder.Items
            MailCount = objItems.Count
            LogFolder MailBoxName, MailCount, ThisUser 'Run the "Logfolder" sub which logs the data
        End If
    SkipMailbox:
    Next x

End Sub

This all works, except users don't name their shared inbox accounts very well. Several users have the same name for their shared inbox, but they're associated to different email addresses.

For example:

Charles might have [email protected] as an Outlook store named "Customer Questions"
David might have [email protected] as an Outlook store also named "Customer Questions"

When Charles and David log their mail counts using my VBA code, they are both being logged with the MailBoxName as "Customer Questions".

I've come to the conclusion that I don't want to log the mailbox name. I want to log the email address instead, which they can't change and will be unique to that inbox.

I am able to do this if there is an email in the inbox, since I can just take the "to" address from the first mail item in the folder, but not if there are 0 emails in the folder.

How can I extract the incoming email address(es) which are associated to a user's Outlook store into a VBA string, so that I can pass it to my "LogFolder" procedure?

Dmitry Streblechenko Dmitry Streblechenko · Accepted Answer · 2021-05-14T17:21:00

Outlook Object Model does not expose anything like that.

If using Redemption is an option (I am its author), it exposes RDOExchangeMailbox.Owner property (returns RDOAddressEntry object):

  skPrimaryExchangeMailbox = 3
  skDelegateExchangeMailbox = 4
  set Session = CreateObject("Redemption.RDOSession")
  Session.MAPIOBJECT = Application.Session.MAPIOBJECT
  for each Store in Session.Stores
    If (Store.StoreKind = skPrimaryExchangeMailbox) or (Store.StoreKind = skDelegateExchangeMailbox)Then
      Debug.Print Store.Name & " : " & Store.Owner.SMTPAddress
    End If
  next

How can I extract the email address for a store?

1 Answers