Solved

Importing Outlook 2013 Email data to Excel 2013

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

To Patch or not to Patch? That is the question!

Don't get caught out like thousands of others around the world in the recent Ransomware Fiasco!
Discuss..
- Why it's not a good idea to wait before Patching
- Sensible approaches to Patching discussed
- Add your feedback, comments and suggestions

Question has a verified solution.

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

Suggested Solutions

In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

739 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