Get The Completed Workbook

If you get a lot of emails and you need to analyse the data in them, then importing your emails from Outlook into Excel with VBA will save you a lot of time. In this post we’re going to explore a way to import all emails in a given folder that were received after a certain date. Obviously, you will need to have Microsoft Outlook installed on your computer for this to work.

 

If you’re unfamiliar with VBA, then it’s probably a good idea to read this post about How To Use The VBA Code You Find Online before continuing reading and working with the completed workbook.

 

Step-000-How-To-Import-Your-Outlook-Emails-Into-Excel-With-VBA How To Import Your Outlook Emails Into Excel With VBA

 

In this example I’m going to be importing from an Outlook folder called Inbox/Net Sales Report/Sales. If you wanted to import from a subfolder of Sales then you would need to append another .Folders(“Subfolder Name”) onto the Set Folder = line of code.

 

I have added named ranges to the workbook as referring to a cell with a named range in VBA rather than the generic cell address such as Range(“A1”) means you can move things around in your workbook without worrying about breaking your code. These are the named ranges the code will use.

  • From_date – This cell will allow the user to input a From Date so that only emails received (and in our Sales folder) after this date are returned.
  • eMail_subject – This cell contains the Subject column heading. Subjects from emails will be imported just below this cell.
  • eMail_date – This cell contains the Date column heading. Date received from emails will be imported just below this cell.
  • eMail_sender – This cell contains the Sender column heading. Sender information from emails will be imported just below this cell.
  • eMail_text – This cell contains the Email Text column heading. The main text body from emails will be imported just below this cell.

 

Here is the code.

 

Sub GetFromOutlook()

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Net Sales Report").Folders("Sales")

i = 1

For Each OutlookMail In Folder.Items
    If OutlookMail.ReceivedTime >= Range("From_date").Value Then
        Range("eMail_subject").Offset(i, 0).Value = OutlookMail.Subject
        Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
        Range("eMail_text").Offset(i, 0).Value = OutlookMail.Body
        
        i = i + 1
    End If
Next OutlookMail

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing

End Sub

 

Step-001-How-To-Import-Your-Outlook-Emails-Into-Excel-With-VBA How To Import Your Outlook Emails Into Excel With VBA

 

It’s a pretty simple VBA procedure, but it can be very useful if you have to deal with a lot of similar emails.