Count Emails in Outlook and export to excel

bmeye4

New Member
Joined
May 14, 2019
Messages
5
I am trying to count the amount of emails, in real time, in a subfolder in Outlook 2013 and automatically export the data to excel. I don't mind opening the excel document and refreshing the data daily, if needed.
The subfolder is called "disks" - it is in a group mailbox subfolder. Any ideas?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
I really do not know where your folder is, but try this

Code:
Sub GetEmail_1()
'Fuente: http://stackoverflow.com/questions/8322432/using-visual-basic-to-access-subfolder-in-inbox
'fuente: http://www.snb-vba.eu/VBA_Outlook_external_en.html
'fuente: https://support.microsoft.com/en-us/kb/208520
    Dim olApp As Outlook.Application
    Dim objNS As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim msg As Outlook.MailItem
    '
    Application.ScreenUpdating = False
    Set olApp = Outlook.Application
    Set objNS = olApp.GetNamespace("MAPI")
    '
    Set olFolder = objNS.Folders("[B][COLOR=#ff0000]disks[/COLOR][/B]")
    Set MyItems = olFolder.Items
    i = 2
    Columns("B:C").Clear
    NumItems = olFolder.Items.Count
    f = 1
    On Error Resume Next
    For n = 1 To NumItems
        Cells(f, "A") = MyItems(n).SenderName
        Cells(f, "B") = MyItems(n).Subject
        Cells(f, "C") = MyItems(n).body
        f = f + 1
    Next
    Columns("B:C").WrapText = False
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub

-------
or if your subfolder is inside another folder, for example a backup, try this

Code:
Sub GetEmail_2()
'Fuente: http://stackoverflow.com/questions/8322432/using-visual-basic-to-access-subfolder-in-inbox
'fuente: http://www.snb-vba.eu/VBA_Outlook_external_en.html
'fuente: https://support.microsoft.com/en-us/kb/208520
    Dim olApp As Outlook.Application
    Dim objNS As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim msg As Outlook.MailItem
    '
    Application.ScreenUpdating = False
    Set olApp = Outlook.Application
    Set objNS = olApp.GetNamespace("MAPI")
    '
    Set olFolder = objNS.Folders("[B][COLOR=#ff0000]Backup[/COLOR][/B]")
    Set subfolder = olFolder.Folders("[B][COLOR=#ff0000]disks[/COLOR][/B]")
    Set MyItems = subfolder.Items
    i = 2
    Columns("A:C").Clear
    NumItems = subfolder.Items.Count
    f = 1
    On Error Resume Next
    For n = 1 To NumItems
        Cells(f, "A") = MyItems(n).SenderName
        Cells(f, "B") = MyItems(n).Subject
        Cells(f, "C") = MyItems(n).body
        f = f + 1
    Next
    Columns("B:C").WrapText = False
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
DanteAmor,

Is there a to automate the code so it is constantly pulling from Outlook?

Thank you.

How automatic do you need it?
It may be when you open your file, then put the macro in the Open event of your file.
 
Upvote 0
That would be my recommendation. Run the macro. Or put it in the open event.
There is the alternative of putting a macro that is "latent" and runs every time interval, but I do not recommend it. But if you want to, then inquire about the Application.Ontime method.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top