Can we copy emails from shared inbox in outlook to excel

[Amitbiswakarma]

Helo

I would like to know if we can copy
Emails from outlook mailbox to excel. Actually, my daily job involves processing emails from shared inbox. I need to produce report on a daily basis at star of the day and end of the day. I need to copy email sender name, subject, size, received date, and categories from different shared inboxes to produce start of the day report. I copy it manually from outlook going into the shared inbox and paste it in excel and apply networkday formulae to calculate the age of the email. This takes time as outlook reponds very slowly. I wanted to check if we can automate this process, if we can use vba codes to automate this task. Please help me. Thank you for your kind cooperation.

 

[kennypete]

If you want that metadata only then this code allows you to select any folder you have rights to and produce a report on today's emails:

Option Explicit

' Microsoft Outlook 16.0 Object Library (or poss. equiv.) is required.
' Ensure Tools>References>Microsoft Outlook 16.0 Object Library is checked.
Public Sub ReadOutlookEmails()
    
    Dim objFolder As Outlook.Folder
    Dim objNamespace As Outlook.Namespace
    Dim objMail As Outlook.MailItem
    
    Dim lngCounter As Long
    
    Set objNamespace = Outlook.GetNamespace("MAPI")
    Set objFolder = objNamespace.PickFolder
    
    If TypeName(objFolder) = "Nothing" Then
        Exit Sub
    End If
    
    Dim varItems As Variant
    Set varItems = objFolder.Items.Restrict("[Received] >= '" & Format(Split(Now(), " ")(0) & " 12:00am", "ddddd h:nn AMPM") & "'") ' Today
    
    ' Read restricted emails from Outlook folder and update details in sheet with code name shMailReport
    For lngCounter = 1 To varItems.Count
        Set objMail = varItems(lngCounter)
        shMailReport.Range("A" & lngCounter + 1).Value = objMail.SenderName
        shMailReport.Range("B" & lngCounter + 1).Value = objMail.Subject
        shMailReport.Range("C" & lngCounter + 1).Value = objMail.Size
        shMailReport.Range("D" & lngCounter + 1).Value = objMail.ReceivedTime
        shMailReport.Range("E" & lngCounter + 1).Value = objMail.Categories
    Next
    
    MsgBox "Finished", vbInformation

End Sub

Output run on my inbox:

Note the Outlook Reference needs to be checked as commented in the code, i.e.:

And also note I have the Sheet "MailReport" with sheet code name shMailReport:

 

[kennypete]

Also you may want to restrict it to email messages only or otherwise handle non-email message types (e.g. meeting declines, etc.) in the inbox or it will/may error. If the former restriction is okay, just change this line to:

Set varItems = objFolder.Items.Restrict("[Received] >= '" & Format(Split(Now(), " ")(0) & " 12:00am", "ddddd h:nn AMPM") & "' AND [MessageClass] = 'IPM.Note'") ' Today, emails only

 

[Karthikeyan Parthiban]

Hello there, can we do the same to copy mails from choosen folder (pop up) and move to it hard drive? All from excel?

 

[kennypete]

Hello there, can we do the same to copy mails from choosen folder (pop up) and move to it hard drive? All from excel?

Sorry, you're going to have to be more specific - do you mean: a) move or copy, b) what to the "hard drive" - emails or the report or something else?

 

[Karthikeyan Parthiban]

Well, I have some shared mailboxes in outlook which have subfolder named after the day's date. Eg: 07-08-20. Mails are moved to the subfolders everyday once read and are moved to the drive under the same name (date) the next day to have it as a backup. I need a vba code to run it from Excel to move the entire subfolder/mails in the subfolders to the local drive under the same folder name. Paths can be referenced from cells in excel. It would be nice to have pick folder pop up for both outlook and path to move those folders to.

 

[Amitbiswakarma]

Thank you for your response. However, I don't want to include any specific date in the VBA code. I just need the email meta data from the specific folder on a present date irrespective of the received date of the email. I am working on office 2016. Could you please help me.

 

[sryan429]

Hi kennypete, thank you for taking the time to write and share this code. I'm receiving a compile error, of variable not defined. I know the Option Explicit will prompt the variable definition, but I can't figure out the issue. I have a sheet named 'MailReport', but get the error starting at line shMailReport.Range("A" & lngCounter + 1).Value = objMail.SenderName. If I remove the explicit clause it will say object not defined. Any advise would be greatly appreciated.

If you want that metadata only then this code allows you to select any folder you have rights to and produce a report on today's emails:

Option Explicit

' Microsoft Outlook 16.0 Object Library (or poss. equiv.) is required.
' Ensure Tools>References>Microsoft Outlook 16.0 Object Library is checked.
Public Sub ReadOutlookEmails()
   
    Dim objFolder As Outlook.Folder
    Dim objNamespace As Outlook.Namespace
    Dim objMail As Outlook.MailItem
   
    Dim lngCounter As Long
   
    Set objNamespace = Outlook.GetNamespace("MAPI")
    Set objFolder = objNamespace.PickFolder
   
    If TypeName(objFolder) = "Nothing" Then
        Exit Sub
    End If
   
    Dim varItems As Variant
    Set varItems = objFolder.Items.Restrict("[Received] >= '" & Format(Split(Now(), " ")(0) & " 12:00am", "ddddd h:nn AMPM") & "'") ' Today
   
    ' Read restricted emails from Outlook folder and update details in sheet with code name shMailReport
    For lngCounter = 1 To varItems.Count
        Set objMail = varItems(lngCounter)
        shMailReport.Range("A" & lngCounter + 1).Value = objMail.SenderName
        shMailReport.Range("B" & lngCounter + 1).Value = objMail.Subject
        shMailReport.Range("C" & lngCounter + 1).Value = objMail.Size
        shMailReport.Range("D" & lngCounter + 1).Value = objMail.ReceivedTime
        shMailReport.Range("E" & lngCounter + 1).Value = objMail.Categories
    Next
   
    MsgBox "Finished", vbInformation

End Sub

Output run on my inbox:

View attachment 11445​

Note the Outlook Reference needs to be checked as commented in the code, i.e.:

View attachment 11446​

And also note I have the Sheet "MailReport" with sheet code name shMailReport:

View attachment 11447​

 

[kennypete]

You will get "not defined" if the sheet code name is not precisely like this:

The visible worksheet name is irrelevant - it can be anything.