Is there a possibilitity to import the email outlook inbox to excel

Question

1.00/5 (1 vote)

See more:

I tried to import email directly from outlook inbox I'm getting an error "Run time error 13" type mismatch

VB

Sub outlooktoimport()

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).Items


i = 1

For Each OutlookMail In Folder.Items

If OutlookMail.ReceivedTime >= Range("email_start_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_Body").Offset(i, 0).Value = OutlookMail.Body

i = i + 1
End If
Next OutlookMail

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

MsgBox "All imported"


End Sub

What I have tried:

I change the retreiving mailbox, yet it is not working

Posted 23-Oct-20 1:24am

dili1234

Updated 25-Oct-20 21:02pm

Richard MacCutchan

v2

Add a Solution

Comments

Richard MacCutchan 23-Oct-20 7:35am

You need to find the line where the error occurs, and see which variables cause the problem.

Sandeep Mewara 23-Oct-20 10:28am

You trying this: https://www.techrepublic.com/blog/microsoft-office/quickly-export-outlook-e-mail-items-to-excel/

have a look and share if not so.

ZurdoDev 23-Oct-20 11:45am

The error means nothing to us unless we know what line of code caused it.

dili1234 25-Oct-20 12:55pm

Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Items

When I debug it error is showing on this line

ZurdoDev 25-Oct-20 13:22pm

A type mismatch means that two things do not match. I do not know this code but from just looking at it you are trying to set the items in a folder to the folder itself. Folder is declared as an MAPI folder but you’re trying to set the items inside of a folder to that variable, which will not work.

dili1234 25-Oct-20 23:40pm

When changed the programme

Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox)

error is coming as Run time 438 and once it debug it is pointing the error

If OutlookMail.ReceivedTime >= Range("email_start_date").Value Then

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

dili1234 · Answer 1 · 2020-10-25T20:50:00

I rectified the problem

Option Explicit
Sub Getinboxcontents()

Dim ol As Outlook.Application

Dim ns As Outlook.Namespace
Dim fol As Outlook.Folder
Dim I As Object
Dim mi As Outlook.MailItem
Dim n As Long
n = 2
Dim rh As Double


rh = Range("A1").RowHeight
Set ol = New Outlook.Application
Set ns = ol.GetNamespace("MAPI")
Set fol = ns.GetDefaultFolder(olFolderInbox)

For Each I In fol.items


If I.Class = olMail Then


n = n + 1

Set mi = I
If mi.ReceivedTime >= Range("B1").Value And mi.ReceivedTime <= Range("C1").Value Then

Cells(n, 1).Value = mi.SenderName
Cells(n, 2).Value = mi.Subject
Cells(n, 3).Value = mi.ReceivedTime
Cells(n, 4).Value = mi.Body

End If

End If
Next I

Range("A1").CurrentRegion.EntireColumn.AutoFit
Range("A1").CurrentRegion.EntireRow.RowHeight = rh
Set fol = Nothing
Set ns = Nothing
Set ol = Nothing


End Sub

dili1234 · Answer 2 · 2020-10-25T21:02:00

I changed the code to this, then the problem solved. But since I Put the criteria for the starting date there is a gap until it reaches the critieria in excel it shows empty rows

Option Explicit
Sub Getinboxcontents()

Dim ol As Outlook.Application

Dim ns As Outlook.Namespace
Dim fol As Outlook.Folder
Dim I As Object
Dim mi As Outlook.MailItem
Dim n As Long
n = 2
Dim rh As Double


rh = Range("A1").RowHeight
Set ol = New Outlook.Application
Set ns = ol.GetNamespace("MAPI")
Set fol = ns.GetDefaultFolder(olFolderInbox)

For Each I In fol.items


If I.Class = olMail Then


n = n + 1

Set mi = I
If mi.ReceivedTime >= Range("B1").Value And mi.ReceivedTime <= Range("C1").Value Then

Cells(n, 1).Value = mi.SenderName
Cells(n, 2).Value = mi.Subject
Cells(n, 3).Value = mi.ReceivedTime
Cells(n, 4).Value = mi.Body

End If

End If
Next I

Range("A1").CurrentRegion.EntireColumn.AutoFit
Range("A1").CurrentRegion.EntireRow.RowHeight = rh
Set fol = Nothing
Set ns = Nothing
Set ol = Nothing


End Sub

Is there a possibilitity to import the email outlook inbox to excel

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Is there a possibilitity to import the email outlook inbox to excel

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us