March 12, 2018

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

49 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

  3. Hi Dinesh,
    Thank you very much for your post. I have followed your tutorial ‘How to get data from outlook emails…’, used your script and it did work…. once. However, the second time that I have tried to use it I got the following error; Run-time error ‘430’. Class does not support Automation or does not support expected interface. Since than I have spent hours surfing the web trying a bunch of things but I did not get it to work a second time. Can you please advise?
    Thanks, Barbaros

  4. I also have a Run-time error 5 when i get to Set outlookNamespace = OutlookApp.GetNamespace (“MAPI”). How do I fix it?

      1. I found my error 🙂
        I had copy and pasted some of the code from the web – and quotation marks were pasted as curly quotes rather than straight quotes – a simple but hard to find fault. Replace your quotes with ‘straight’ quotes and it should work.

  5. Hi, this video is amazing, it was easy to understand and could do 95% of my task with it. One last question, how can i add the attachment of the email on the table created? Let’s say in column E

  6. Hi I ran your code but I got an error message. Im not sure if it is because I’m working on a Mac. The code reads:

    Compile Error:
    User-defined type not defined

    I’m not sure what to do?

    1. VBA -> Tools -> References -> Activate “Microsoft Outlook 16.0 Object Library”. He said it in the video. I checked, now it works.

  7. Hi Dinesh ,
    Easy video to follow and I have an issue running the code. It’s returning the following error and need your assistance resolving.
    Compile error:
    User-defined type not defined.

    1. VBA -> Tools -> References -> Activate “Microsoft Outlook 16.0 Object Library”. He said it in the video. I checked, now it works.

  8. Hi Dinesh sir,

    I’m getting an error message as out of memory because body of the email has too much data. Is there a way or code to get rid of this error?

    If possible can you please provide a code to skip the particular Download of the body if data is too much in it?

    Thanks in advance sir

  9. Hi Dinesh ,
    Easy video to follow and I have an issue running the code. It’s returning the following error and need your assistance resolving.
    Compile error:
    User-defined type not defined.

    1. VBA -> Tools -> References -> Activate “Microsoft Outlook 16.0 Object Library”. He said it in the video. I checked, now it works.

  10. Hi Dinesh ,
    Easy video to follow and I have an issue running the code. It’s returning the following error and need your assistance resolving.
    Compile error:
    User-defined type not defined.

    1. VBA -> Tools -> References -> Activate “Microsoft Outlook 16.0 Object Library”. He said it in the video. I checked, now it works.

  11. It’s returning the following error and need your assistance resolving.
    Compile error:
    User-defined type not defined.

  12. Hi Dinesh

    I have used many of your videos in my dayly tasks. Thanks for going through the code in every video. I had the same problem Compile error: User-defined type not defined.

    I have problem to solve this fault meassage. Did you have a solution for this.

  13. Hi,
    Debung then highlight
    Dim OutlookApp As Outlook.Application
    Error shown
    User-defined type not defined

    thanks
    Kashif

  14. I am getting the same error – User-defined type not defined As Kasif and Loia above. Can someone please assist me .

  15. What code can i use if i want to get emails from my main account and not a subfolder?

  16. Using the debug, if you are getting error at this line,

    OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders(“impMail”)

    1. In Outlook, check again the Folder Name.
    **Ensure there is NO spacing at the end of the Folder Name

    Tag: User-defined type not defined, Run-time error’5′

    1. I have problem like you. But I don’t know how to handle it.
      What did you do to solve your problem?

  17. Hi Dinesh,
    I’ve tried to modify your exceptional macro in order to exctract the exact email address of the sender and of the receiver of the email. By using “OutlookMail.SenderEmailAddress” it works for the sender. I’ve tried many ways in order to obtain also the recipient’s email address, but with no results. I can obtain only the name by using ReceivedByName, but I need the entire email address.

    Could you please tell me what is the exact function that I’ve to use?

    Range(“email_Sender”).Offset(i, 0).Value = OutlookMail.SenderEmailAddress

    Range(“email_Receiver”).Offset(i, 0).Value = OutlookMail.?

    It’s important to me because otherwise I’ve to read and copy and paste all the email addresses of hundreds of emails one by one.

    Thanks in advance
    Kind regards

  18. Hi Dinesh,
    I’ve tried to modify your exceptional macro in order to exctract the exact email address of the sender and of the receiver of the email. By using “OutlookMail.SenderEmailAddress” it works for the sender. I’ve tried many ways in order to obtain also the recipient’s email address, but with no results. I can obtain only the name by using ReceivedByName, but I need the entire email address.

    Could you please tell me what is the exact function that I’ve to use?

    Range(“email_Sender”).Offset(i, 0).Value = OutlookMail.SenderEmailAddress

    Range(“email_Receiver”).Offset(i, 0).Value = OutlookMail.?

    It’s important to me because otherwise I’ve to read and copy and paste all the email addresses of hundreds of emails one by one.

    Thanks in advance
    Kind regards

  19. Sir,

    How to count cells with red fonts in a range across multiple sheets and display total in a master sheet?
    Thanks.

  20. Hello Sir,

    I have earned alot of knowledge with your videos and I thank you so much for your detailed explanation.

    Can you please help me with vba code for below criteria.

    1) I have an employee details in the body of outlook (details are in a FORM format)
    2) I want the employee details (Name, type of job, joining date etc) to be copied from outlook email body to excel based on the criteria (example: Name in column A , type of job in column B etc)

    3) All the request that we receive are with a same generic email (default email address and the macro should pick up data from that email)

    4) The code should automatically export data from all the emails (looping)

    can you please help me with VBA code for this as there are lots of email coming in and I am tracking it manually. I really appreciate if you could help me with the code.

  21. Hi,
    I got a error as Runtime “438”

    It is pointing to
    If OutlookMail.ReceivedTime >= Range(“email_start_date”).Value Then

    * email_start_date is varible that I defined

  22. Hello everyone.I am continously getting Run-time error ‘440’ here saying Array index out of bounds.Can anyone help me in this?Is it becoz I’m using multiple Outlook accounts??

  23. Dear Sir,

    I have run this code bate face some error – please help

    Run-time error’-2147024809 (80070057)

  24. Dear Sir,

    Run this code in excel sheet but i face some error –
    “Run-time error’-2147024809 (80070057)
    I have request you to please help-

  25. thanks for this idea….

    I have a problem with the first line:
    Dim OutlookApp As Outlook.Application

    Messagebox: “Error while compilation…. userdefined type not defined” – (translated from my native language)

    I’m using Office 2010. Do you / does anybody know if a newer version is needed?

  26. oh, … I have forgotten another question…

    You use “impmail” in
    Set Folder = OutlookNamespace.getdefaultfolder(olFolderInbox).Folders(“impMail”)
    as your examined folder.
    1. Can you give additional information about folders which are sub-sub-sub-folders of any folder in first hirarchy?

    2. What does Outlook do if there are multiple folders with the same name, like “newsletter”? Should the used folder has a unique name?

    2b. does it matter in which part of the structure the folder is placed?
    example:
    main pst-file
    ==> company_1 => newsletter
    ==> company_2 => newsletter
    other pst-file
    ==> company_99 => newsletter

    How do I access to these identical named folders?

    3. Does it matter if the sub-sub-sub-folder is not in the main pst file, but in another pst-file?

    thank you

  27. 1. correction…

    I payed attention to “Microsoft xx.Object Library” but it has to be “Microsoft Outlook …” and it’s standing in the middle of the whole list.

  28. Hi,
    I am getting run time error of “Object doesn’t support this property or method” for the line
    “If OutlookMail.ReceivedTime >= Range(“From_Date”).Value Then”

    Please advise.
    Regards,

  29. Great Video, thank you. I had a lot of errors and each time it was a typo except for this one.
    I am correctly getting the Subject, the Received Time and other elements but not SenderName or Body.

    I put a watch on OutlookMail as a mailItem. I can see that in my case, body, HTMLbody and Sender and many other properties of the OutlookMail MailItem are simply (ie empty) whereas other stuff is populated. Any reason why the OutlookMail item would be able to get the some data from the mail message in outlook but not all of them ? (Ie why is Body and SenderMail blank parameters?

  30. How to filter mails with specific Subject, or mails within an specific folder?

    cant make it work with

    If OMAIL.Subject == “Specific Subject I’m looking 4” then

    it shows Expression expected XD Thanks!

Comments are closed.