Search for text in attachments

Question

I want to search for "string = my_string" in my attachments in outlook Inbox folder. If this "string" exists i want the mails move to another folder. I found a code, i tried to improve it, but it's still not working. Any help would be much appreciated.

EDIT Here is the code :

 Sub test2()
 Const strFindText As String = "Completed"
 Const strFileType As String = "xlsx|xls"
 Const strPath As String = "C:\Users\PC2\Documents\Georg\Attachment\"
Dim vFileType As Variant
 Dim strFilename As String
 Dim strName As String
 Dim olItems As Outlook.Items
 Dim olItem As Outlook.MailItem
 Dim wb As Object
 Dim xlApp As Object
 Dim olAttach As Outlook.Attachment
 Dim strFolder As String
 Dim bStarted As Boolean
 Dim bFound As Boolean
 Dim i As Long, i_V As Long
Dim fdObj As FileSystemObject
    Set fdObj = CreateObject("Scripting.FileSystemObject")

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
        bStarted = True
    End If
    On Error GoTo 0
    xlApp.Visible = True

If Not fdObj.FolderExists(strPath & strFindText) 
 Then fdObj.CreateFolder strPath & strFindText
End If

Set olItems = Session.GetDefaultFolder(olFolderInbox).Items
For i = olItems.Count To 1 Step -1
   Set olItem = olItems(i)
   If olItem.Attachments.Count > 0 Then
   vFileType = Split(strFileType, "|")
   For Each olAttach In olItem.Attachments
   For i_V = 0 To UBound(vFileType)
   If Right(LCase(olAttach.FileName), Len(vFileType(i_V))) = vFileType(i_V)
 Then strFilename = strPath & 
 Format(olItem.ReceivedTime, "yyyymmdd-HHMMSS") & _" " & olAttach.FileNameolAttach.SaveAsFile strFilename

    Set wb = xlApp.Workbooks.Open(strFilename)
    With xlApp.Find(strFilename, xlValues, xlWhole)
        bFound = False
    Do While .Find(strFindText).Activate    '<-I have problem here
        bFound = True
    Loop
    strName = wb.Name
    wb.Close 0
        If bFound Then
        Name strFilename As strPath & strFindText & "\" & strName
        End If
    End With
End If
Next i_V
Next olAttach
End If

Next i
    If bStarted Then xl.App.Quit
    Set wb = Nothing
    Set xlApp = Nothing
    Set olItem = Nothing
    Set olItems = Nothing
End Sub

Answer 1

I had a quick glance at your code and noticed an error so reported it in a comment. I then noticed another error and another and another all of which I reported in the comment. It was only after posting the comment that I wondered how much sense my comment would make to the author of this code. This answer is an enlarged version of the comment. It should help you improve your code but there is too much missing to provide a complete answer.

1. In Set wdApp = GetObject(, "Excel.Application"), wdApp is the sort of name given to a Word application. My guess is you found some code that used Word to do something and adapted it. Set wdDoc = wdApp.Documents.Open(strFilename) is invalid for an Excel application. I suggest using the name xlApp. The workbook open and the search code will have to be rewritten for Excel.

2. You do not include the code for FolderExists and CreateFolders but I would be surprised if they did not use FileSystemObject. Both FileSystemObject and Outlook have a data type Folder. If all this code is within Outlook, Folder is interpreted as Outlook.Folder and FolderExists and CreateFolders may not work. If necessary, you need to replace data type Folder within these routines with Scripting.Folder.

3. Set olItems = Session.GetDefaultFolder(olFolderInbox).Items does not work on my system. I am a home user with two email addresses each with its own store and Inbox. The default Inbox is not used. If you are a corporate user with a single email address then your default Inbox is probably the Inbox you want.

4. You write: “If this "string" exists I want the mails move to another folder.” I interpreted this to mean you wanted to move the Outlook MailItem to a different Outlook folder. This is why I criticised you for using Name which renames a disc file or moves it to a different disc folder with, optionally, a new name. I now wonder if you meant you wanted to move the saved attachment.

5. This answer need a paragraph here explaining how to move a MailItem or moved the saved attachment whichever it is you want. Please add a comment to this answer explaining which you want.

6. In Const strPath As String = " my_root ", I appreciate that “my_root” is just a place holder so you do not have to reveal something that might be confidential. Let us assume the true value is “C:\Users\Georg\Documents”. If this were true, you are creating a path of “C:\Users\Georg\Documentsmy_string”. If the true value is “C:\Users\Georg\Documents\”, you are creating a path of “C:\Users\Georg\Documents\my_string”. Either way, you are assuming the true value of “my_string” does not contain characters that are invalid within a folder name. I do not understand why you need the folder name to include the search string. This is just a temporary folder for use in testing attachments. Why not call it “C:\Users\Georg\Documents\SavedAttachments” or some other temporary folder?

7. Please be careful delimiting folder and file names. You use Chr(32) and Chr(92). I assume Chr(32) is a mistake. Why not write “\” instead of Chr(92) which is so much clearer? I use Chr() when necessary but normally writing the character is clearer. Anyone who has used File Explorer will know about “\” but how many know what Chr(92) means without looking it up.

8. I do not understand the complex filename you give to the saved attachments. If the attachment is wanted to move it to a different folder with a different name. If the attachment is not wanted, you should delete it using Kill. Either way some simple. Fixed filename is all you need.

9. Do you know how to search a workbook for a particular string? All your existing code is for opening and searching a Word document.

I have numbered my paragraphs so you can reference them easily if you need to ask a question.