Extract Emails From Outlook Excel VBA Macro

We saw before how to send emails from Excelvia Outlook. We can also extract emails from Outlook into Excel. For that, we need to target the Outlook application object and specify the folder where emails are located (e.g., Inbox). Then we can choose to get all the emails in that folder, or have some conditions to filter for example only unread emails, or emails from a certain period, etc. The macro example below extracts all emails from the Inbox received since 01/01/2024.

Macro code:

 
  Sub ExtractMailsFromOutlook()
 
  Dim OutApp As New Outlook.Application 'early binding
  Dim OutNamespace As Namespace, OutFolder As MAPIFolder, OutItem As Object
  Dim r As Integer, cutDate As Date
 
  Set OutNamespace = OutApp.GetNamespace("MAPI")
  Set OutFolder = OutNamespace.GetDefaultFolder(olFolderInbox)
  cutDate = "01/01/2024"
 
  On Error Resume Next
  For Each OutItem In OutFolder.Items
      If OutItem.ReceivedTime >= cutDate Then
          r = r + 1
          Range("A" & r + 1).Value = OutItem.ReceivedTime
          Range("B" & r + 1).Value = OutItem.SenderName
          Range("C" & r + 1).Value = OutItem.Subject
          Range("D" & r + 1).Value = OutItem.Body
      End If
  Next OutItem
  On Error GoTo 0
 
  Set OutFolder = Nothing
  Set OutNamespace = Nothing
  Set OutApp = Nothing
 
  End Sub
 

Macro explained:

• First, we declare object variables to represent the Outlook application and Outlook item objects respectively, as well as name space and folder objects. We also have a numerical variable to store row number and a date variable used to target emails from a certain period.
• We need to specify the target folder containing the emails we attempt to extract or import. In the example above, we are targeting the Inbox folder. We can target any other specific sub-folder using the Folder property as indicated below.

 Set OutFolder = OutNamespace.GetDefaultFolder(olFolderInbox).Folders("Other")

• Now we loop through each mail item in that folder. Since OutMail has been declared as an object, this will actually get any item in the folder. This could for example get a mail delivery message or a meet invite too, which are not considered mail items. We can target only emails by filtering on item type (e.g. MailItem).

 If TypeName(OutItem) = "MailItem" Then …

• In the example above, there is a condition to target emails received since 1^st of January 2024. The filter date has previously been added to a variable (cutDate). When that condition is met, a numerical variable increments its value by one and is used as counter and row index.
• Finally, for each item, we target relevant email properties to extract the desired information and write it to each row in the worksheet. The example above gets the received time, sender name, subject, and body, but we could extract any other information associated with a property of the mail object.
• Similarly, we can target other items such as calendar appointments, contacts, tasks, for example. We can basically get all the information that we see in Outlook into Excel using the same approach.

IMPORTANT: If you do not have Outlook installed and setup in your machine, the macro will not work.

This is how we extract Outlook emails from Outlook using VBA macros.

  

Other examples:

• How to Add Conditional Formatting For Another Cell with Excel VBA macros
• How to Send Google Mail from Excel with VBA macros
• How to Export Range As Picture with Excel VBA macros