How to Import emails from outlook using Excel VBA

Welcome to Excel Avon

Import emails from OUTLOOK

DOWNLOAD THE USED EXCEL FILE FROM HERE>>

In today’s post, we will show you How to Import emails from outlook using Excel VBA, In this post we will work with Module as we created with Module in previous post.
We explained in previous post How to Create contact in outlook in Excel VBA, this post is totally different from that post, In the previous post, we were taught to save or create a contact, in this we will learn how to import email data received in Outlook, that too in a sheet.

Suppose we are given another project where we have to import the sender name, sender email, email subject of phone numbers and the date or time of the email sent in Outlook, how will we do it or you will get the email of a fixed date. If data is required, then we will know that in this post, let’s see

In this way we can import emails from outlook as you are seeing in the image.

Import-emails-from-outlook

How to Import emails from outlook using Excel VBA

As we taught you how to insert a module when we were working with modules, as we have learned in many posts so far, you can insert modules. Let us teach you how we insert the module. Let’s understand, then we have to go like last time, first go to the Developer Tab, then click on the option of Visual Basic as shown in the image below.

sort-data-on-excel-using-VBA

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

Sort-data-on-excel-using-vba

Go to Tools and then select References as shown in the below screenshot. scroll down in the Reference Object library and select “Microsoft Outlook 16.0 Object Library” to make it available for Excel VBA.

Send Email With Outlook automation1

Once the module is inserted, and the library is activated, we’ll write a subroutine to Sub Import Emails().

Sub ImportEmails()
End Sub

We will Declare the variable Outlook Application.

Sub ImportEmails()
Dim olApp As New Outlook.Application
End Sub

Declare the variable for Name space.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
End Sub

Declare the variable for Outlook Folder.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
End Sub

Declare the variable for Outlook Mail Items.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
Dim eItem As MailItem
End Sub

Now we will set here name space, use (MAPI) to return to outlook name space from application.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
Dim eItem As MailItem
Set nSpace = olApp.GetNamespace("MAPI")
End Sub

Set folder to return a folder object that represents the default folder of the type requested the currents profile.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
Dim eItem As MailItem
Set nSpace = olApp.GetNamespace("MAPI")
Set inFolder = nSpace.GetDefaultFolder(olFolderInbox)
End Sub

Here we are using For Loop, I have to define another variable which will be for last row.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
Dim eItem As MailItem
Dim Lr As Long
Set nSpace = olApp.GetNamespace("MAPI")
Set inFolder = nSpace.GetDefaultFolder(olFolderInbox)
For Each eItem In inFolder.Items
Next eItem
End Sub

Here we have to declare the last row where we will import the data.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
Dim eItem As MailItem
Dim Lr As Long
Set nSpace = olApp.GetNamespace("MAPI")
Set inFolder = nSpace.GetDefaultFolder(olFolderInbox)
For Each eItem In inFolder.Items
    Lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
Next eItem 
End Sub

Here we will declare row where sender name will be imported.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
Dim eItem As MailItem
Dim Lr As Long
Set nSpace = olApp.GetNamespace("MAPI")
Set inFolder = nSpace.GetDefaultFolder(olFolderInbox)
For Each eItem In inFolder.Items
    Lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheet1.Range("A" & Lr).Value = eItem.SenderName

Next eItem 
End Sub

Here we will declare the row where the email of the email sender or where the email will be imported.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
Dim eItem As MailItem
Dim Lr As Long
Set nSpace = olApp.GetNamespace("MAPI")
Set inFolder = nSpace.GetDefaultFolder(olFolderInbox)
For Each eItem In inFolder.Items
    Lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheet1.Range("A" & Lr).Value = eItem.SenderName
    Sheet1.Range("B" & Lr).Value = eItem.SenderEmailAddress
Next eItem 
End Sub

Here we will declare the row where the subject of the email will be imported.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
Dim eItem As MailItem
Dim Lr As Long
Set nSpace = olApp.GetNamespace("MAPI")
Set inFolder = nSpace.GetDefaultFolder(olFolderInbox)
For Each eItem In inFolder.Items
    Lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheet1.Range("A" & Lr).Value = eItem.SenderName
    Sheet1.Range("B" & Lr).Value = eItem.SenderEmailAddress
    Sheet1.Range("C" & Lr).Value = eItem.Subject
Next eItem 
End Sub

Now we will declare the row where email received time will be imported.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
Dim eItem As MailItem
Dim Lr As Long
Set nSpace = olApp.GetNamespace("MAPI")
Set inFolder = nSpace.GetDefaultFolder(olFolderInbox)
For Each eItem In inFolder.Items
    Lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheet1.Range("A" & Lr).Value = eItem.SenderName
    Sheet1.Range("B" & Lr).Value = eItem.SenderEmailAddress
    Sheet1.Range("C" & Lr).Value = eItem.Subject
    Sheet1.Range("D" & Lr).Value = eItem.ReceivedTime
Next eItem 
End Sub

Here we will create a conditions to avoid Not Responding problem for the Application

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
Dim eItem As MailItem
Dim Lr As Long
Set nSpace = olApp.GetNamespace("MAPI")
Set inFolder = nSpace.GetDefaultFolder(olFolderInbox)
For Each eItem In inFolder.Items
    Lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheet1.Range("A" & Lr).Value = eItem.SenderName
    Sheet1.Range("B" & Lr).Value = eItem.SenderEmailAddress
    Sheet1.Range("C" & Lr).Value = eItem.Subject
    Sheet1.Range("D" & Lr).Value = eItem.ReceivedTime
    If Lr Mod 100 = 0 Then
    End If
Next eItem 
End Sub

Do Events make a bit pause for every 100 Rows.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
Dim eItem As MailItem
Dim Lr As Long
Set nSpace = olApp.GetNamespace("MAPI")
Set inFolder = nSpace.GetDefaultFolder(olFolderInbox)
For Each eItem In inFolder.Items
    Lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheet1.Range("A" & Lr).Value = eItem.SenderName
    Sheet1.Range("B" & Lr).Value = eItem.SenderEmailAddress
    Sheet1.Range("C" & Lr).Value = eItem.Subject
    Sheet1.Range("D" & Lr).Value = eItem.ReceivedTime
    If Lr Mod 100 = 0 Then
        DoEvents
    End If
Next eItem 
End Sub

Click on Run button

Import-emails-from-outlook1

As you can see, the data of the email has been imported in the required sheet, the data will be visible to you, as shown in the screen, we have shown you more data below. Now we want the emails to be imported only for a specific date, for this we will have to go to VBE and apply a condition.

Import-emails-from-outlook3

The condition will apply if the email item’s received time is (1, 4, 2023) or more than that, then the item will be imported, here we have used the date serial fanction, also using cdate function. Delete the data that was imported and run the code.

Sub ImportEmails()
Dim olApp As New Outlook.Application
Dim nSpace As Namespace
Dim inFolder As Outlook.Folder
Dim eItem As MailItem
Dim Lr As Long
Set nSpace = olApp.GetNamespace("MAPI")
Set inFolder = nSpace.GetDefaultFolder(olFolderInbox)
For Each eItem In inFolder.Items
If eItem.ReceivedTime >= CDate(DateSerial(2023, 4, 1)) Then
    Lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheet1.Range("A" & Lr).Value = eItem.SenderName
    Sheet1.Range("B" & Lr).Value = eItem.SenderEmailAddress
    Sheet1.Range("C" & Lr).Value = eItem.Subject
    Sheet1.Range("D" & Lr).Value = eItem.ReceivedTime
    If Lr Mod 100 = 0 Then
        DoEvents
    End If
End If
Next eItem 
End Sub

Click on Run button

Import-emails-from-outlook1

As you can see, the data present in the email which is on or after 1st April 2023 is imported into the sheet, you will see the data as shown in the screen below. We have shown you more data.

Import-emails-from-outlook2 (2)

Therefore, I hope that you have understood How to Import emails from outlook using Excel VBA, maybe if you do not understand some options, then you can comment us, which we will answer soon and for more information, you can follow us on Twitter, Instagram, LinkedIn and you can also follow on YouTube.

LEARN MORE TOPIC IN VBA HERE

DOWNLOAD THE USED EXCEL FILE FROM HERE>>

You can also see well-explained video here about How to Import emails from outlook using Excel VBA