Scraping Outlook E-mails using VBA - type mismatch

Question

I'm currently working on a macro to scrape Outlook e-mails and attachments. Having a minor type mismatch error, hoping that someone can point out what data type I need.

So I'm aware there's a large practicality hurdle that I'll need to face at some point. Right now I'm focusing on making things work, then optimizing.

Sub FetchEmailData()
    Dim appOutlook As Object
    Dim olNs As Object
    Dim olFolder As Object
    Dim olItem As Object
    Dim iRow As Integer
    
    ' Get/create Outlook Application
    On Error Resume Next
    Set appOutlook = GetObject(, "Outlook.Application")
    If appOutlook Is Nothing Then
        Set appOutlook = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
    
    Set olNs = appOutlook.GetNamespace("MAPI")
    Set olFolder = olNs.GetDefaultFolder(6) ' 6 == Inbox for some reason
    
    For iRow = 1 To olFolder.Items.Count
        'Check if we care about the e-mail
        Call SaveEmailAttachment(olFolder.Items.Item(iRow))
        'Go onto the next one if we don't
        
        'ThisWorkbook.Sheets("Test").Cells(iRow + 1, 1) = olFolder.Items.Item(iRow).SenderEmailAddress
        'ThisWorkbook.Sheets("Test").Cells(iRow + 1, 2) = olFolder.Items.Item(iRow).Subject
        'ThisWorkbook.Sheets("Test").Cells(iRow + 1, 3) = olFolder.Items.Item(iRow).To
        'ThisWorkbook.Sheets("Test").Cells(iRow + 1, 3) = olFolder.Items.Item(iRow).Size        
    Next iRow
End Sub
    
Sub SaveEmailAttachment(itm As Outlook.MailItem)
    Dim objAtt As Outlook.Attachment
    Dim saveFolder As String
    Dim dateFormat As String
     
    saveFolder = ThisWorkbook.Names("EmailAttachmentSavePath").RefersToRange.Value2
    dateFormat = Format(itm.ReceivedTime, "yyyy-mm-dd Hmm ")

    For Each objAtt In itm.Attachments
        objAtt.SaveAsFile saveFolder & "\" & dateFormat & objAtt.DisplayName
    Next
End Sub

So the data type mismatch is because I'm sending an Outlook folder item, but the receiving macro is expecting an Outlook MailItem. I know I need to get both of them to the same item type, probably via a dim, but I'm not quite sure which one is better to use, and how I'd need to adapt the SaveEmailAttachment code to properly compensate.

Bonus question: Is there a way to scrape a subfolder by name?

I apologize - Call SaveEmailAttachment(olFolder.Items.Item(iRow)) - it's when I try to pass the e-mail to the save-down macro. — Selkie, Apr 24 '18 at 20:41
I'm not getting any error- also are trying to loop through only items with attachment or all items? — 0m3r, Apr 24 '18 at 21:57
see if this helps on subfolders https://stackoverflow.com/a/49683587/4539709 — 0m3r, Apr 24 '18 at 22:00
Not all e-mails have attachments, but I thought that was handled with the "for each objatt" line — Selkie, Apr 24 '18 at 22:01

score 1 · Answer 1 · edited Apr 24 '18 at 21:50

1

test for the TypeOf item and if it is a mailItem pass it.

If TypeOf olFolder.Items.Item(iRow) Is MailItem Then
    Call SaveEmailAttachment(olFolder.Items.Item(iRow))
end if

edited Apr 24 '18 at 21:50

0m3r

12,286
15
35
71

answered Apr 24 '18 at 20:56

Sorceri

7,870
1
29
38

Scraping Outlook E-mails using VBA - type mismatch

1 Answers1