Solved

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

Posted on 2016-07-19
7
30 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

914 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

16 Experts available now in Live!

Get 1:1 Help Now