Blacklist Email Addresses Using V-Look Up and Validation Rules

This is a great solution posted by Deepak Anand on the Salesforce.com Answers community today.



1. If you have 400 Invalid Domains and just few Valid ones then why not write a VR checking if they all have the Valid Ones or Not:

AND(
NOT(CONTAINS(Email, 'gmail.com')),
NOT(CONTAINS(Email, 'yahoo.com')),
NOT(CONTAINS(Email, 'outlook.com'))
)

2. This is where VLOOKUP plays the best role! You actually don't need any Apex to get this done. I will explain how to do it. You can create a Custom Object say 'Invalid Email Domains' with two fields say:

(i) Domain Name[Name Field(Default)]
(ii) Description[Text Area]

Say we have 'example.com' is an Invalid One or Not Allowed One. If so a record will be created like as below:

Domain Name - example.com
Description - Does not comply with PCI Rules.

So in short the Name field should contain the Domain Name and the Description should help for future use. If that is done, you can then populate the Custom Object(Invalid Email Domains) with the records via the Native Data Loader or Dataloader.io or whatever. So the best part of this is that it becomes very User friendly in that sense where you could just create a New Record to indicate a Invalid Email Domain or remove an existing record if at all any one becomes Valid.

Now, how will we go about with the VR. Here you go:

MID(Email, FIND('@', Email)+1, LEN(Email)) =
VLOOKUP(
 $ObjectType.Invalid_Email_Domain__c.Fields.Name ,
 $ObjectType.Invalid_Email_Domain__c.Fields.Name ,
 MID(Email, FIND('@', Email)+1, LEN(Email))
)

MID(Email, FIND('@', Email)+1, LEN(Email)) extracts the portion just after '@' from an Email Field. For eg: if the email was john.dale@example.com the the result would be 'example.com'.

The VLOOKUP actually performs a scan on all the records belonging to the Custom Object - Invalid Email Domain(that we just created) and if any one record's Name field matches with the extracted text - 'example.com', then it returns the value contained in the Name field of that matched record and it will be equated with the extracted portion(MID(Email, FIND('@', Email)+1, LEN(Email))). If they are equal then it returns a 'TRUE' and the VR fires!

This is just yet another awesome feature of Force.com - VLOOKUP!

Thats it!. You are done!