Download email attachments based on subject name

[Raychin]

Hello! I need to save attachments on my hard drive from incoming emails , based on subject name that contains both "Wind Power Forecast" and "0906" in the subject name. They are 2 emails, with same Subject name, every single day, and have to download the attachments from both of them. So i have a code, but there is a constant Run-time error 13, Type mismatch in the lines where i point the criteria for mail Subject :

If InStr(outMailItem.Subject, "wind power forecast", vbTextCompare) > 0 And _
                    InStr(outMailItem.Subject, "0906", vbTextCompare) > 0 Then

I can't avoid it, so can you help me solve that, please?

Public Sub Save_Attachments()

    Dim OutlookOpened As Boolean
    Dim outApp As Outlook.Application
    Dim outNs As Outlook.NameSpace
    Dim outFolder As Outlook.MAPIFolder
    Dim outAttachment As Outlook.Attachment
    Dim outItem As Object
    Dim outMailItem As Outlook.mailitem
    Dim inputDate As String, subjectFilter As String
    Dim saveInFolder As String
    
    saveInFolder = "C:\Users\BG-TRADE-005\OneDrive - ****.com\Desktop\Schedule\Mail_Temp\Download\"   
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    
    OutlookOpened = False
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then
        Set outApp = New Outlook.Application
        OutlookOpened = True
    End If
    On Error GoTo 0
    
    If outApp Is Nothing Then
        MsgBox "Cannot start Outlook.", vbExclamation
        Exit Sub
    End If
    
    Set outNs = outApp.GetNamespace("MAPI")
    
    'Set outFolder = outNs.GetDefaultFolder(olFolderInbox).Folders("Test")
    
    Set outFolder = outNs.PickFolder

    If Not outFolder Is Nothing Then
        For Each outItem In outFolder.Items
            If outItem.Class = Outlook.OlObjectClass.olMail Then
                Set outMailItem = outItem
                    If InStr(outMailItem.Subject, "wind power forecast", vbTextCompare) > 0 And _
                        InStr(outMailItem.Subject, "0906", vbTextCompare) > 0 Then
                            For Each outAttachment In outMailItem.Attachments
                                           outMailItem.SaveAs saveInFolder & Format(Now, "yyyymmdd hhnnss") & ".msg", olMSG
                            Next
                    End If
            End If
        Next
    End If
    
End Sub

 

[John_w]

So i have a code, but there is a constant Run-time error 13, Type mismatch in the lines where i point the criteria for mail Subject :

If InStr(outMailItem.Subject, "wind power forecast", vbTextCompare) > 0 And _
InStr(outMailItem.Subject, "0906", vbTextCompare) > 0 Then

When you specify the compare argument you must also specify the start argument, which is actually the first argument:

        If InStr(1, outMailItem.Subject, "wind power forecast", vbTextCompare) > 0 And _
InStr(1, outMailItem.Subject, "0906", vbTextCompare) > 0 Then

 

[Raychin]

When you specify the compare argument you must also specify the start argument, which is actually the first argument:

        If InStr(1, outMailItem.Subject, "wind power forecast", vbTextCompare) > 0 And _
InStr(1, outMailItem.Subject, "0906", vbTextCompare) > 0 Then

Thank you so much, John_w!

 

[Raychin]

Hmm now i have a problem with

outMailItem.SaveAs saveInFolder & Format(Now, "yyyymmdd hhnnss") & ".msg", olMSG

line.
run-time error 2147286788 The operation failed

 

[Raychin]

Hmm now i have a problem with

outMailItem.SaveAs saveInFolder & Format(Now, "yyyymmdd hhnnss") & ".msg", olMSG

line.
run-time error 2147286788 The operation failed

I mean i need to save the attachments inside the mails as they are (.csv) but i think i have set it wrong in the code, but can't see where is the problem

 

[John_w]

The problem is the outMailItem.SaveAs line which is saving the whole email as an Outlook Message file (.msg). Change it to:

outAttachment.SaveAsFile saveInFolder & outAttachment.Filename

 

[Raychin]

The problem is the outMailItem.SaveAs line which is saving the whole email as an Outlook Message file (.msg). Change it to:

outAttachment.SaveAsFile saveInFolder & outAttachment.Filename

Yep, thank you John, i figured it out few min ago
I have a question - is it possible to set a time period at which the mails arrived (let's say between 09:00-10:00 AM) or just the mails from today ( the present day)?

 

[John_w]

have a question - is it possible to set a time period at which the mails arrived (let's say between 09:00-10:00 AM) or just the mails from today ( the present day)?

Change the If statement to one of these:

                If InStr(1, outMailItem.Subject, "wind power forecast", vbTextCompare) > 0 And _
                   InStr(outMailItem.Subject, "0906") > 0 And _
                   DateValue(outMailItem.ReceivedTime) = Date Then
                    'Email received today
                End If
                
                If InStr(1, outMailItem.Subject, "wind power forecast", vbTextCompare) > 0 And _
                   InStr(outMailItem.Subject, "0906") > 0 And _
                   DateValue(outMailItem.ReceivedTime) = Date And (TimeValue(outMailItem.ReceivedTime) >= TimeValue("09:00:00") And TimeValue(outMailItem.ReceivedTime) <= TimeValue("10:00:00")) Then
                    'Email received today between 9am and 10am
                End If

Note - vbTextCompare isn't needed with number strings because numbers don't have upper/lower case versions.