Can we copy emails from shared inbox in outlook to excel

Amitbiswakarma

New Member
Joined
Apr 14, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

kennypete

Board Regular
Joined
Apr 19, 2008
Messages
235
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:
VBA Code:
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:
1587026084293.png

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

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

kennypete

Board Regular
Joined
Apr 19, 2008
Messages
235
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:
VBA Code:
Set varItems = objFolder.Items.Restrict("[Received] >= '" & Format(Split(Now(), " ")(0) & " 12:00am", "ddddd h:nn AMPM") & "' AND [MessageClass] = 'IPM.Note'") ' Today, emails only
 
Joined
Aug 5, 2020
Messages
2
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Apr 19, 2008
Messages
235
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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?
 
Joined
Aug 5, 2020
Messages
2
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 14, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,651
Messages
5,549,204
Members
410,905
Latest member
Extjel
Top