Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Importing Outlook 2013 Email data to Excel 2013

Posted on 2014-03-23
3
Medium Priority
?
551 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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Take a look at these 6 Outlook Email management tools which can augment the working and performance of Microsoft Outlook to give you a more rewarding emailing experience.
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.
Suggested Courses

972 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