Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Importing Outlook 2013 Email data to Excel 2013

Posted on 2014-03-23
3
Medium Priority
?
549 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
regmigrant earned 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 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