Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Count Emails in Outlook and export to excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Count Emails in Outlook and export to excel

    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?

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,203
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Count Emails in Outlook and export to excel

    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("disks")
        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("Backup")
        Set subfolder = olFolder.Folders("disks")
        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
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Emails in Outlook and export to excel

    Thank You!!! Slightly different than mine so it should work

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,203
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Count Emails in Outlook and export to excel

    Try and tell me
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Emails in Outlook and export to excel

    Yep! It worked. Thank you.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,203
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Count Emails in Outlook and export to excel

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  7. #7
    New Member
    Join Date
    Apr 2019
    Location
    Texas
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Emails in Outlook and export to excel

    DanteAmor,

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

    Thank you.

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,203
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Count Emails in Outlook and export to excel

    Quote Originally Posted by JACOBB9900 View Post
    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.
    Regards Dante Amor

  9. #9
    New Member
    Join Date
    Apr 2019
    Location
    Texas
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Emails in Outlook and export to excel

    I'd like for the macro to pull from Outlook a few times a day if possible.

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,203
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Count Emails in Outlook and export to excel

    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.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •