Solved

Importing Outlook 2013 Email data to Excel 2013

Posted on 2014-03-23
3
525 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
exchange out of office 8 40
Help with Excel formula 6 38
Error in VBA for exact date format as in source file 9 26
Help Updated Qtr 2 10
Outlook Free & Paid Tools
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now