Run-time error - type mismatch

[Damian37]

Hello everyone,
I'm attempting to search through Outlook to find an attachment that is given a unique name with a date. However, there is part of the filename that is the same all of the time. I'm receiving the error on the line where I'm setting the sPath. I've got other code, that should be searching other folders for this attachment. Here is the code:

Sub SaveAttachmentToServer()
    Dim olApp As Outlook.Application, olNs As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder, eFolder As Outlook.Folder, SpfcFolder As Outlook.Folder
    Dim Atmt As Outlook.Attachment, Item As Object
    Dim FileName As String, sPath As String, sFileNm As String
    
    sPath = [CHARTER_REPLEN] & Application.PathSeparator
    sFileNm = [Inbox]
    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    Set olFolder = olNs.GetDefaultFolder(olFolderInbox)

    On Error GoTo CheckInbox
    Set SpfcFolder = olFolder.Folders("Davon's Emails")
     
    For Each Item In SpfcFolder.Items
        For Each Atmt In Item.Attachments
            If Atmt.FileName = sFileNm Then
                FileName = sPath & sFileNm
                Debug.Print FileName
                Atmt.SaveAsFile FileName
                GoTo CleanUp
            End If
        Next Atmt
    Next Item
    
CheckInbox:
    On Error GoTo 0
    For Each Item In olFolder.Items
        For Each Atmt In Item.Attachments
            If Atmt.FileName = sFileNm Then
                FileName = sPath & sFileNm
                Debug.Print FileName
                Atmt.SaveAsFile FileName
                GoTo CleanUp
            End If
        Next Atmt
    Next Item
    For Each eFolder In olFolder.Folders
        For Each Item In eFolder.Items
            For Each Atmt In Item.Attachments
                If Atmt.FileName = sFileNm Then
                    FileName = sPath & sFileNm
                    Debug.Print FileName
                    Atmt.SaveAsFile FileName
                    GoTo CleanUp
                End If
            Next Atmt
        Next Item
    Next eFolder
    MsgBox "An attachment with a file name '" & [CHARTER_REPLEN] & "' was not found", , "File Attachment."
    
CleanUp:
    Set olApp = Nothing
    Set olNs = Nothing
    Set olFolder = Nothing
    Set SpfcFolder = Nothing
End Sub

If anyone has any suggestions or ideas, I would greatly appreciate it. Thank you. Cheers!
D

 

[Damian37]

Hello everyone,
I'm attempting to search through Outlook to find an attachment that is given a unique name with a date. However, there is part of the filename that is the same all of the time. I'm receiving the error on the line where I'm setting the sPath. I've got other code, that should be searching other folders for this attachment. Here is the code:

Sub SaveAttachmentToServer()
    Dim olApp As Outlook.Application, olNs As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder, eFolder As Outlook.Folder, SpfcFolder As Outlook.Folder
    Dim Atmt As Outlook.Attachment, Item As Object
    Dim FileName As String, sPath As String, sFileNm As String
    
    sPath = [CHARTER_REPLEN] & Application.PathSeparator
    sFileNm = [Inbox]
    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    Set olFolder = olNs.GetDefaultFolder(olFolderInbox)

    On Error GoTo CheckInbox
    Set SpfcFolder = olFolder.Folders("Davon's Emails")
     
    For Each Item In SpfcFolder.Items
        For Each Atmt In Item.Attachments
            If Atmt.FileName = sFileNm Then
                FileName = sPath & sFileNm
                Debug.Print FileName
                Atmt.SaveAsFile FileName
                GoTo CleanUp
            End If
        Next Atmt
    Next Item
    
CheckInbox:
    On Error GoTo 0
    For Each Item In olFolder.Items
        For Each Atmt In Item.Attachments
            If Atmt.FileName = sFileNm Then
                FileName = sPath & sFileNm
                Debug.Print FileName
                Atmt.SaveAsFile FileName
                GoTo CleanUp
            End If
        Next Atmt
    Next Item
    For Each eFolder In olFolder.Folders
        For Each Item In eFolder.Items
            For Each Atmt In Item.Attachments
                If Atmt.FileName = sFileNm Then
                    FileName = sPath & sFileNm
                    Debug.Print FileName
                    Atmt.SaveAsFile FileName
                    GoTo CleanUp
                End If
            Next Atmt
        Next Item
    Next eFolder
    MsgBox "An attachment with a file name '" & [CHARTER_REPLEN] & "' was not found", , "File Attachment."
    
CleanUp:
    Set olApp = Nothing
    Set olNs = Nothing
    Set olFolder = Nothing
    Set SpfcFolder = Nothing
End Sub

If anyone has any suggestions or ideas, I would greatly appreciate it. Thank you. Cheers!
D

I also wanted to add that I do not want to save the attachment to a server, I will be saving the attachment to a folder within my computer.

 

[JoeMo]

Try:

sPath = "[CHARTER_REPLEN]" & Application.PathSeparator

 

[Damian37]

Try:

sPath = "[CHARTER_REPLEN]" & Application.PathSeparator

Hello Joe,
That worked out great. Thank you very much. The attachment isn't being recognized, because it has a date prior to the CHARTER_REPLEN portion of the attachment name. Would I use a wildcard in order for the code to recognize the attachment within my email folders? Thank you very much for your assistance. Cheers!
D.

 

[Damian37]

Hello Joe,
That worked out great. Thank you very much. The attachment isn't being recognized, because it has a date prior to the CHARTER_REPLEN portion of the attachment name. Would I use a wildcard in order for the code to recognize the attachment within my email folders? Thank you very much for your assistance. Cheers!
D.

Hi Joe,
I made a modification to the line of code searching for the attachment using what I believe is a wildcard character, but the code is still not finding the attachment. I have an email in my inbox with the attachment, as well as another email within a sub folder that also has the same attachment. Here's the modification I made to the one line of code:

sFileNm = "[ * & CHARTER_REPLEN]"

Thank you for your help. Cheers!
D.

 

[JoeMo]

I didn't look at all your code, just enough to get you past the run time error your post showed. So, not sure what you want.

You might try these variations:

sFileNm = "[*CHARTER_REPLEN]"
sFileNm = "*[CHARTER_REPLEN]"

and also try replacing this line:

If Atmt.FileName = sFileNm Then

with this:

If Atmt.FileName Like sFileNm Then

 

[Damian37]

I didn't look at all your code, just enough to get you past the run time error your post showed. So, not sure what you want.

You might try these variations:

sFileNm = "[*CHARTER_REPLEN]"
sFileNm = "*[CHARTER_REPLEN]"

and also try replacing this line:

If Atmt.FileName = sFileNm Then

with this:

If Atmt.FileName Like sFileNm Then

Hello Joe,
I've tried quite a number of variations to the line of code in order to search for anything with "CHARTER_REPLEN" in its name in order for the file to be opened. The code goes through without erroring out, however, it doesn't identify and open the current email I have with an attachment that has CHARTER_REPLEN in its file name. Here's the latest version of code I have:

sPath = "[Davon's Emails]" & Application.PathSeparator
    sFileNm = "_CHARTER_REPLEN"
    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    Set olFolder = olNs.GetDefaultFolder(olFolderInbox)

    On Error GoTo CheckInbox
    Set SpfcFolder = olFolder.Folders("Inbox")
     
    For Each Item In SpfcFolder.Items
        For Each Atmt In Item.Attachments
            If Atmt.FileName Like sFileNm Then
                FileName = sPath & sFileNm
                Debug.Print FileName
                Atmt.SaveAsFile FileName
                GoTo CleanUp
            End If
        Next Atmt
    Next Item

Thoughts?
D.

 

[JoeMo]

Sorry, I'm really not proficient with code for Outlook. I can only offer the observation that this line:

On Error GoTo CheckInbox

may be keeping you from discovering where the error or errors in your code might be. For test purposes, I would comment out that line and step through the code to see what's happening.