Solved

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

Posted on 2016-07-19
7
34 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 69

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 69

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 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.

 

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 69

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 69

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

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…
Many people use more than one email account and so it becomes difficult for them to manage them when they use separate accounts,  so, in this article, I have shared an easy way to add Other Mail Accounts in your Google Inbox. It helps to combine all…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

828 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