Solved

Importing Outlook 2013 Email data to Excel 2013

Posted on 2014-03-23
3
534 Views
Last Modified: 2014-03-23
I am trying to import email data from outlook into excel and the code keeps hanging up at:

     For Each olM In olF.Items


with a message that says run-time error '13': Type mismatch

I'm not sure where I have it wrong tried changing
  Dim olM As Outlook.MailItem
to      
 Dim olm As Outlook.Items

but that did not work???


Sub ImportEmails()
     Dim olA As Outlook.Application
     Dim olNS As Outlook.Namespace
     Dim olF As Outlook.MAPIFolder
     Dim olM As Outlook.MailItem
     Dim lrow As Long
     
     Sheets("EmailImport").Select

     Cells.Select
     Selection.ClearContents
     
     Range("A1").Select
     Set olA = New Outlook.Application
     Set olNS = olA.GetNamespace("MAPI")
     
     'Select the folder
     Set olF = olNS.PickFolder
             
     lrow = 1
        With ActiveSheet
            .Cells(lrow, 1) = "SenderEmailAddress"
            .Cells(lrow, 2) = "EntryID"
            .Cells(lrow, 3) = "Recipients"
            .Cells(lrow, 4) = "To"
            .Cells(lrow, 5) = "Subject"
            .Cells(lrow, 6) = "Body"
            lrow = lrow + 1
        End With
   
     lrow = 2
     
     For Each olM In olF.Items
             With ActiveSheet
                 .Cells(lrow, 1) = olM.SenderEmailAddress
                 .Cells(lrow, 2) = olM.EntryID
                 .Cells(lrow, 3) = olM.Recipients
                 .Cells(lrow, 4) = olM.To
                 .Cells(lrow, 5) = olM.Subject
                 .Cells(lrow, 6) = olM.Body
                 lrow = lrow + 1
             End With
     Next
     
     Set olM = Nothing
     Set olF = Nothing
     Set olNS = Nothing
     Set olA = Nothing
 End Sub
0
Comment
Question by:rogerdjr
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
ID: 39949316
There's nothing implicitly wrong with the code but you are assuming that every item in the chosen folder is a MailItem. My guess is that you are picking up something else - eg: an appointment - and trying to assign it to Olm in the loop and that's whats giving the error.

The way around it is to declare Olm as an Object then use Typename(Object) to see if its a MailItem before you try and process it.

Also the MailItem.Recipients  will return a  Class of Recipients not the individual email addresses - you need to extract the items from it individually.
0
 

Author Closing Comment

by:rogerdjr
ID: 39949337
Revised code works perfectly

Thanks

Sub ImportEmails()
     Dim olA As Outlook.Application
     Dim olNS As Outlook.Namespace
     Dim olF As Outlook.MAPIFolder
     Dim olm As Object
     Dim lrow As Long
     
     Sheets("EmailImport").Select

     Cells.Select
     Selection.ClearContents
     
     Range("A1").Select
     Set olA = New Outlook.Application
     Set olNS = olA.GetNamespace("MAPI")
     
     'Select the folder
     Set olF = olNS.PickFolder
             
     lrow = 1
        With ActiveSheet
            .Cells(lrow, 1) = "SenderEmailAddress"
            .Cells(lrow, 2) = "EntryID"
            .Cells(lrow, 3) = "Recipients"
            .Cells(lrow, 4) = "To"
            .Cells(lrow, 5) = "Subject"
            .Cells(lrow, 6) = "Body"
            lrow = lrow + 1
        End With
   
     lrow = 2
     
     For Each olm In olF.Items
        If TypeName(olm) = "MailItem" Then
             With ActiveSheet
                 .Cells(lrow, 1) = olm.SenderEmailAddress
                 .Cells(lrow, 2) = olm.EntryID
                 .Cells(lrow, 3) = olm.To
                 .Cells(lrow, 4) = olm.ReceivedByName
                 .Cells(lrow, 5) = olm.Subject
                 .Cells(lrow, 6) = olm.Body
                 lrow = lrow + 1
             End With
        End If
     Next
     
     Set olm = Nothing
     Set olF = Nothing
     Set olNS = Nothing
     Set olA = Nothing
 End Sub
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39949355
glad I could help :)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
Read this checklist to learn more about the 15 things you should never include in an email signature.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question