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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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