Outlook vba match a pattern when attchement is found

[hartyshow]

I'm trying to match a pattern when a spreadsheet attachment is found in outlook. I can trigger the process for a spreadsheet attachment but don't know how to invoke the search through the attachment.

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
    Dim objAtt As Outlook.Attachment
    Dim saveFolder As String
    saveFolder = "C:\form"

     For Each objAtt In itm.Attachments
     
          
     If InStr(objAtt.DisplayName, ".xls") Then

        
          objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
          
          
          Set objAtt = Nothing
        End If
          
          
          
     Next
     End Sub

thanks for your help

 

[ZVI]

Hi and welcome to the MrExcel board!
Try this modification of your code:

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
 
  Dim objAtt As Outlook.Attachment
  Dim saveFolder As String, sFileName As String
  saveFolder = "C:\form"
 
  If Not TypeName(itm) = "MailItem" Then Exit Sub
  If Dir(saveFolder, vbDirectory) = "" Then MkDir saveFolder
 
  For Each objAtt In itm.Attachments
    sFileName = LCase(objAtt.Filename)
    If sFileName Like "*.xls" Or sFileName Like "*.xls?" Then
      objAtt.SaveAsFile saveFolder & "\" & objAtt.Filename
    End If
  Next
 
End Sub

Code for the testing:

Sub Test()
  ' Open the mail item with xls attachment(s) and run this code
  saveAttachtoDisk Application.ActiveInspector.CurrentItem
End Sub

In case you are asking for the searching content of attachment then Excel can be involving to open the saved attachment

 

[hartyshow]

Thanks ZVI for your reply, I can save a spreadsheet if attachement is .xls but I wanted to search for a Regex like Olus in attachement if .xls file is found then save file in C:\form folder.


If InStr(objAtt.DisplayName, ".xls") if .xls is found Then

search for Regex=Olus and

save file in folder objAtt.SaveAsFile saveFolder & "" & objAtt.DisplayName

the problem now I don't know to how to search through attachement for regex

Any Idea

 

[ZVI]

Do you want to search in file names of the attachments of inside their cells?

 

[ZVI]

To search if "Olus" is present in a file name of the attachment you may use this:

    If sFileName Like "*.xls" Or sFileName Like "*.xls?" Then
      If InStr(1, sFileName, "Olus", vbTextCompare) > 0 Then
        objAtt.SaveAsFile saveFolder & "\" & objAtt.Filename
      End If
    End If

 

[hartyshow]

thanksZVI, really appreciate your help. Unfortunatly Olus is inside the spreadsheet, I know I'm a real headache lol!!

We do receive between 30 and 50 spreadsheets sometimes 3 or 4 times a week, having to open each of them starting to be unbearable.

https://www.mrexcel.com/forum/members/zvi.html

 

[ZVI]

Just for the case you realy need RegEx:

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
 
  Static RegEx As Object
  Dim objAtt As Outlook.Attachment
  Dim saveFolder As String, sFileName As String
  saveFolder = "C:\form"
 
  If Not TypeName(itm) = "MailItem" Then Exit Sub
  If Dir(saveFolder, vbDirectory) = "" Then MkDir saveFolder
 
  If RegEx Is Nothing Then
    Set RegEx = CreateObject("vbscript.regexp")
    RegEx.Global = True
    RegEx.IgnoreCase = True
    RegEx.Pattern = "Olus"  '<-- use any valid RegExp pattern here
  End If
 
  For Each objAtt In itm.Attachments
    sFileName = LCase(objAtt.Filename)
    If sFileName Like "*.xls" Or sFileName Like "*.xls?" Then
      If RegEx.Test(sFileName) Then
        objAtt.SaveAsFile saveFolder & "\" & objAtt.Filename
      End If
    End If
  Next
 
End Sub

Read about patterns and RegEx usage here - How to use Regular Expressions

 

[ZVI]

Olus is inside the spreadsheet

If what sheet and cell?

 

[hartyshow]

we do not have a specific cell, since we receive all spreadsheets from multiple senders. but I'm looking for some specific words each time we receive a spreadsheet, then if the word is found then the spreadsheet need to be archive on the computer.

 

[ZVI]

In what sheet that word(s) is? Code is not a human and requires the strict definitions even if it is "find that word in all sheets"