Solved

Filter email based on a serie of numbers contained in the email address

Posted on 2016-07-19
7
29 Views
Last Modified: 2016-07-19
Hello,
I am trying to find a way to automatically junk email which contain a series of numbers (I am receiving quite a lot these last weeks - all spams or worse) for instance mike.457@adomain.subdomain.com.
I would like to create a rule allowing the detect the numbers (ex. 457) and then move the email the a "junk" folder.
I have tried the MS Outlook "rules" but I did not succeed to establish a rule allowing this type of detection.
It is probably possible in VBA but do not know exactly how to manipulate the outlook object
thanks in advance
0
Comment
Question by:micmoo56
  • 4
  • 3
7 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 41719016
You can't do that with rules, you need VBA code triggered by receiving mails. Triggering can be done by rule or VBA event handlers, but both have to act on all emails, there is no pre-filtering option.
0
 

Author Comment

by:micmoo56
ID: 41719024
Thanks but have you an VBA example I could use.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41719052
Have been about to prepare one for you:
Option Explicit
Option Compare Text		' case insensitive - in case of using alphanumeric characters

Public WithEvents AdminInbox As Outlook.Items

Public Sub Application_Startup()
  ' The default inbox
  Set AdminInbox = Outlook.Session.GetDefaultFolder(olFolderInbox).Items
  ' if this is a secondary account, not your main one, with a different Inbox
  ' Set AdminInbox = Session.Folders("Administrator@domain.com").Folders("Inbox").Items
End Sub

Private Sub AdminInbox_ItemAdd(ByVal item as Object)
Dim str As String
Dim i As Integer
Dim noFrom As Variant, noTo As Variant, noSubject As Variant, noBody As Variant

  ' case insensitive. Available Patterns: ?*#[a-z][!a-z]

  noFrom = Array( _
            "*.co.ua", "*.biz.ua", "*girl_name*", "*@*.xyz", "*.#*@*" _
          )
  
  
  noTo = Array( _
            "*.#*@*" _
         )
  

  noSubject = Array( _
            "*help you save*", "*fw* order *", "*lottery*", "*breakthrough*", "*healthy*" _
          ) 
            
  noBody = Array( _
            "*proof of delivery * enclosed *", "* lux *", "*luxury*", "* gift *", "* gifts *", "* loan *", _
            "* mortgage *", "* deposit *" _
        )
        
  On Error Resume Next
  
  str = LCase(item.SenderEmailAddress)
  For i = 0 To UBound(noFrom)
    If str Like noFrom(i) Then item.Delete: Debug.Print: Debug.Print "  ### From: " & noFrom(i);: Exit Sub
  Next
  
  str = LCase(item.To)
  For i = 0 To UBound(noTo)
    If str Like noTo(i) Then item.Delete: Debug.Print: Debug.Print "  ### To: " & noTo(i);: Exit Sub
  Next
  
  str = Replace(str, "emailing: ", "")
  For i = 0 To UBound(noSubject)
    If str Like noSubject(i) Then item.Delete: Debug.Print: Debug.Print "  ### Subject: " & noSubject(i);: Exit Sub
  Next
  
  str = LCase(item.Body)
  For i = 0 To UBound(noBody)
    If str Like noBody(i) Then item.Delete: Debug.Print: Debug.Print "  ### Body: " & noBody(i);: Exit Sub
  Next
  
End Sub

Open in new window

This is derived from what I run, and contains some examples. It is able to scan FROM, TO, SUBJECT and BODY with the respective patterns.
The pattern character for a single digit is #, a ? is a single arbitrary character, * any amount (including none) of arbitrary characters. [a-z] are all (lowercase) letters, [!a-z]  everything but (lowercase) letters. Because I've set Option Compare Text, lower and upper case are treated the same.

The example code works as soon as an email is dropped into the default inbox. The commented example shows how you can use a different folder, e.g. for test.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:micmoo56
ID: 41719127
not sure I understand how to set this up. I have made an Alt+F11 and copy and paste you code in a new module but it seems it should be a class. could you explain me how you make it work  ? thanks in advance
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 41719225
As is, the above needs to be in the application main module, ThisOutlookSession.
Application_Startup is called only if you start Outlook. If you need to run it after having Outlook opened, position cursor inside of the sub and press F5 to execute.
0
 

Author Comment

by:micmoo56
ID: 41719243
Great it works - I have slightly modified the program and added a function to detect series of number (with the condition that the Clng conversion is above 10 - this is just arbitrary and allows some email with one digit to "pass the filter"
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41719253
The pro with that VBA solution is that you are flexible, the con that you are flexible and have to code a lot :D.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now