Pulling e-mail addresses into excel with VBA from outlook-CodePudding

At work I have two e-mail accounts in outlook. One is an individual e-mail and the other is a general department e-mail.

How would I use VBA to get excel to access the general e-mail and pull the sender of each e-mail into a string? I need to iterate over each e-mail in the inbox ignoring e-mails in any sub folders.

Here's the code I've written so far. Hopefully I'm at least on the right track.

Public Sub test()

Dim emailApp As Outlook.Application, emailNamespace As Outlook.Namespace
Dim oFolder As MAPIFolder, oMail As Outlook.MailItem
Dim iSelect As Outlook.AccountSelector, iBox As Outlook.Account

Dim tEmailAddress As String

Set emailApp = New Outlook.Application
Set emailNamespace = OutlookApp.GetNamespace("MAPI")
Set oFolder = emailNamespace.GetDefaultFolder(olFolderInbox)

'I think im on the right track here.......
Set iBox = iSelect.SelectedAccount

For Each oMail In oFolder.Items
    
    tEmailAddress = oMail.SenderEmailAddress
    'Do other stuff for the project.........

Next

End Sub

EDIT: Posting a completed code sample for the next person who is having this problem.

Public Sub test()

Dim emailApplication As Outlook.Application, emailAccounts As Outlook.Accounts
Dim emailAccount As Outlook.Account, tAccount As Outlook.Account
Dim emailStore As Outlook.Store, emailInbox As Outlook.Folder, tMail As Variant

Set emailApplication = New Outlook.Application
Set emailAccounts = emailApplication.Session.Accounts

For Each tAccount In emailAccounts
    If tAccount.DisplayName = "[email protected]" Then: Set emailAccount = tAccount
Next

Set emailStore = emailAccount.DeliveryStore
Set emailInbox = emailStore.GetDefaultFolder(olFolderInbox)

On Error Resume Next
For Each tMail In emailInbox.Items
    Debug.Print tMail.SenderEmailAddress
Next
Err.Clear
End Sub

CodePudding user response：

The following code is not required:

'I think im on the right track here.......
Set iBox = iSelect.SelectedAccount

Instead, you may just rely on the GetDefaultFolder method which allows retrieving default folders (from the delivery store):

Set oFolder = emailNamespace.GetDefaultFolder(olFolderInbox)

If you need to choose a specific store in the profile you may find the required account by using the Namespace.Accounts property which returns an Accounts collection object that represents all the Account objects in the current profile. The Account.DeliveryStore property returns a Store object that represents the default delivery store for the account. The Store.GetDefaultFolder method returns a Folder object that represents the default folder in the store and that is of the type specified by the FolderType argument. This method is similar to the GetDefaultFolder method of the NameSpace object. The difference is that this method gets the default folder on the delivery store that is associated with the account, whereas NameSpace.GetDefaultFolder returns the default folder on the default store for the current profile.

I need to iterate over each e-mail in the inbox ignoring e-mails in any sub folders.

The current folder is processed only when you deal with Folder.Items collection.