Get Data from Outlook into Excel worksheet

How to get data from Outlook emails into an Excel worksheet with VBA. Many a times we get reports from different people on different dates via email in Microsoft Outlook. If we wish to create a new report based on dates or the sender name, it is quite cumbersome to do a copy and paste from Outlook into an Excel worksheet. Automation helps solve the report creation quickly and easily using VBA. Watch the video below:

 

Watch this video on YouTube.

Here’s the complete VBA 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(“impMail”)

i = 1

For Each OutlookMail In Folder.Items
If OutlookMail.ReceivedTime >= Range(“email_Receipt_Date”).Value And Date <= 28 – mar – 2018 Then
Range(“email_Subject”).Offset(i, 0).Value = OutlookMail.Subject
Range(“email_Subject”).Offset(i, 0).Columns.AutoFit
Range(“email_Subject”).Offset(i, 0).VerticalAlignment = xlTop
Range(“email_Date”).Offset(i, 0).Value = OutlookMail.ReceivedTime
Range(“email_Date”).Offset(i, 0).Columns.AutoFit
Range(“email_Date”).Offset(i, 0).VerticalAlignment = xlTop
Range(“email_Sender”).Offset(i, 0).Value = OutlookMail.SenderName
Range(“email_Sender”).Offset(i, 0).Columns.AutoFit
Range(“email_Sender”).Offset(i, 0).VerticalAlignment = xlTop
Range(“email_Body”).Offset(i, 0).Value = OutlookMail.Body
Range(“email_Body”).Offset(i, 0).Columns.AutoFit
Range(“email_Body”).Offset(i, 0).VerticalAlignment = xlTop

i = i + 1
End If
Next OutlookMail

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

End Sub

Further reading:

Import Data from Microsoft Outlook to Microsoft Excel

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

3 thoughts on “Get Data from Outlook into Excel worksheet”

  1. Hi
    I tried to use your code but i have a error that “Run-time error’5′ invalid procedure call or argument” can you help me about that because i need these codes. Have a nice day
    Best regards

  2. Hello Sir,

    my Name ist Anket from Germany, I used your code and it run perfectly. Thank you very much for this tutorial! I want to add more features e.g. I want to connect my attachment from my E-Mail into my Excel Chart. How can I realize it?

    Thank you very much for your help

    best regard.

    Anket

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.