VBA: Copy mail from shared outlook mailbox to excel

[Kruijf]

Hi all,

I have question, im looking for a macro that copies mail from a shared mailbox to excel. I do understand a little bit of VBA but im not good with writing my own macro's (yet). On the internet i found a macro that copies mail from specific folder in my default mail account, but that one does not work with shared mailboxes.

We need mails from specific folders in outlook in Excel so we can use the data for some reports that we make daily. Because it takes a while to copy those mails we would like to automate this with a macro. I did search on the internet but on a few occasions i found that people noted that it was not possible. We're using Office 2013.

So my questions are.
- Is it possible to do this with VBA?
- If so, does anybody have a macro that i can use for my situation?


I hope it's clear what we need, hopefully it's possible and somebody can send me in the right direction.
Thanks in advance.

Kind regards,
Tom

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I think it should work.

See if you can get this macro to work. It runs in Excel and you need to set up the reference to the Outlook library as described in the comment.
You will need to change the "Smith, John" string for the name of the email account you want to read.

The macro finds all the emails with today's date and lists the subject lines in the Immediate Window in the VB Editor. OK, that is not what you want but it will show whether the process is working or not. If it works we can then think about extracting the values you need into a worksheet.

' Requires: Tools-->References-->Microsoft Outlook 15.0 Object Library
Sub getEmails()

    Dim olApp As Outlook.Application
    Dim olNS As Outlook.Namespace
    Dim olFldr As Outlook.MAPIFolder
    Dim olItem As Object
    Dim olMailItem As Outlook.MailItem
    
    Set olApp = New Outlook.Application
    Set olNS = olApp.GetNamespace("MAPI")
    Set olFldr = objNS.Folders("Smith, John")
    Set olFldr = olFldr.Folders("Inbox")
    
    For Each olItem In olFldr.Items
        If olItem.Class = olMail Then
            Set olMailItem = olItem
            If olMailItem.ReceivedTime > Date Then Debug.Print olMailItem.Subject
        End If
    Next olItem

End Sub

 

[Kruijf]

Hi RickXL,

Thanks for your response, i will try that code today and let you know what happens.

 

[Kruijf]

Hi RickXL,

Unfortunately the code does not work, it crashed with the error Run-time error 424: Object required. If i debug the code it says that the problem is with: Set olFldr = objNS.Folders("Smith, John")

I checked a microsoft site where they said that you should also select the "Microsoft DAO 3.5 Object Library" just like the Microsoft Outlook 15.0 object library. But after that it still crashed on that line of code. I used the name of the mailbox which is displayed on the left in outlook's navigation panel. I also tried it with my default mailbox but that also did not make any difference.

Not sure if this info is needed. But i got my outlook setup that my personal mailbox is being cached but the for the shared folders not.

Am i filling in the wrong values? I changed Smith, John to the name of the mailbox i wanted, and Inbox to the name of the folder.

 

[RickXL]

Hi,

Apologies, I just tried the macro again and what I posted does not work for me either. It worked when I tested it - honestly

I think I must have pasted in the John Smith line from somewhere else afterwards. The correct object name is olNS and not objNS. I am sorry about that.

You will need to navigate to the folder you want to read, though. For instance, I have a folder called Keep which is inside my MrExcel folder which is inside my Inbox. To get to that I would use:

' Requires Tools-->References-->Microsoft Outlook 15.0 Object Library
Sub getEmails()

    Dim olApp As Outlook.Application
    Dim olNS As Outlook.Namespace
    Dim olFldr As Outlook.MAPIFolder
    Dim olItem As Object
    Dim olMailItem As Outlook.MailItem
    
    Set olApp = New Outlook.Application
    Set olNS = olApp.GetNamespace("MAPI")
    Set olFldr = olNS.Folders("RickXL@xxx.com")
    Set olFldr = olFldr.Folders("Inbox")
    Set olFldr = olFldr.Folders("MrExcel")
    Set olFldr = olFldr.Folders("Keep")
    
    For Each olItem In olFldr.Items
        If olItem.Class = olMail Then
            Set olMailItem = olItem
            If olMailItem.ReceivedTime > Date Then Debug.Print olMailItem.Subject
        End If
    Next olItem

End Sub

Stick with the Outlook Libraries as that is how it has been written.

In fact the whole thing is a hierarchy if you look:
Outlook.Application-->GetNamespace("MAPI")-->Folders("RickXL@xxx.com")-->Folders("Inbox")-->Folders("MrExcel")-->Folders("Keep")

 

[Kruijf]

Hi,

No problem that can happen.
For me this process is good because i learn to understand this code how it works, so far it all looks very logical.

What i just did:
I copied your code to my test workbook and changed all the names of the folders to the names how it is shown in my outlook. The code runs without errors but i see nothing happening. I first tried it with my own mailbox but after that also with the shared mailbox. When i use the names of the shared mailbox you see the cursor turning a bit longer then when i use the default mailbox. So it looks like it walks through the folders but then it's finished.

First i thought it was because i did not have any new mail in it so therefor i copied some mail from today in it but still nothing happens.

Thanks for your help!

 

[RickXL]

Hi, you could try this.

It displays how many items there are in each folder. Note, it is not looking inside any sub-folders. ALso, it is counting all items and not just mail items. So it will count appointments etc as well.

' Requires Tools-->References-->Microsoft Outlook 15.0 Object Library
Sub countEmails()

    Dim olApp As Outlook.Application
    Dim olNS As Outlook.Namespace
    Dim olFldr As Outlook.MAPIFolder
    Dim olItem As Object
    Dim olMailItem As Outlook.MailItem
    
    Set olApp = New Outlook.Application
    Set olNS = olApp.GetNamespace("MAPI")
    
    Set olFldr = olNS.Folders("RickXL@xxx.com")
    Debug.Print olFldr.Name, olFldr.Items.Count
    
    Set olFldr = olFldr.Folders("Inbox")
    Debug.Print olFldr.Name, olFldr.Items.Count
    
    Set olFldr = olFldr.Folders("MrExcel")
    Debug.Print olFldr.Name, olFldr.Items.Count
    
    Set olFldr = olFldr.Folders("Keep")
    Debug.Print olFldr.Name, olFldr.Items.Count

End Sub

If they all return zero then we have a problem. If some are not zero then we have shown that we can find things in those folders.

 

[Kruijf]

Were should it show the numbers? because i see something happening but then nothing. I forgot to fill in another folder and the code crashed on it, when i changed it to the folder it should read i didnt get any errors.

 

[Kruijf]

Could it be that something is restricted that because of that it does not pop up a window?

 

[RickXL]

Hi,

Sorry, I tend to assume things. You need to be looking at the Immediate Window in the VB Editor. You will find it in the View menu if it is not showing.