Email-to-Case can create too many cases.

First, how does email-to-case work? Salesforce provides a built in email handler that will accept an email and create a Case. (It does other tasks such as saving attachments and linking the Case to the customer Contact record.) When the Service Representative emails the answer the response contains a reference ID that the Salesforce email handler can use to link the next email to the original Case.

For example, our customer writes an email with the subject "another question". They send the email to our support email address which forwards the email to our instance of Salesforce. The built-in handler creates a new Case. Our staff will respond using the Salesforce email tools and the customer gets their answer. The subject line of our email to the customer looks like this:

RE: another question [ ref:00D7JFzw.5007KcZYu:ref ]

When the customer writes back the email handler will decipher the reference and append the response to the original case. All this is great. The problem comes when that reference Id is not part of the incoming email because this creates a new Case. The problem is worse when many people are involved in the email problem and they all continue correspondence without including the reference. Our staff can spend many hours manually combining the resulting Cases.

My plan is to build my own email handler that can match email subject lines and combine the cases for my users.

This handler can also solve another problem for my users: get rid of those cryptic and long references. Who wants to see "[ ref:00D7JFzw.5007KcZYu:ref ]" in every subject line. What does this mean to our users? Why not link the email to the case based on Case Number?

RE: another problem Case: 0031452

Doesn't that look better? It is also easy to train our staff and regular customers to include the Case Number in any email to discuss a particular issue.

Here is class with code that works for us. I'm posting it without comment just to get this code out. Perhaps in a follow up post I'll explain the parts.

public with sharing class CaseEmailInBoundUtilities {

private Static Boolean TRACE = true;

protected Case theCase = null;
protected Messaging.InboundEmail inboundEmail;
protected String defaultCaseOwnerId;

public CaseEmailInBoundUtilities() {
 Group[] queues = [select Id, Name from Group where Name = 'Triage'];
 this.defaultCaseOwnerId = queues[0].Id;  
}

public Messaging.InboundEmailResult processInboundEmail(Messaging.InboundEmail email)
{
 Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();
 result.success = true;
 this.inboundEmail = email;
 

 String caseNumber = extractRef(email.subject);
 if(caseNumber != null)
 {  
  if(TRACE)system.debug(Logginglevel.ERROR,'CaseEmailInBoundUtilities.  extracted case number: "' + caseNumber + '"');
  this.theCase = locateByCaseNumberAsString(caseNumber);
  if(this.theCase == null) {
   // TODO email error message to SysAdmin
   system.debug(Logginglevel.ERROR,'CaseEmailInBoundUtilities.  Create a new case.  Could not find case number: "' + caseNumber + '"');
  }
 } else {
  // try to match subject
  String mainSubject = extractMainSubject(email.subject);
  Case[] matchingCases = [Select Id, CaseNumber, Subject, Description from Case where Subject = :mainSubject
   and CreatedDate = LAST_N_DAYS:5];
  if(matchingCases.size() == 1) {
   this.theCase = matchingCases[0];
  } else 
  {
   system.debug(Logginglevel.ERROR,'CaseEmailInBoundUtilities.  Create a new case because we found '+matchingCases.size() + ' cases with the subject: "' + mainSubject + '"');
  }
 }
 if(this.theCase == null) {
  // else create a new Case
  this.theCase = new Case();
  theCase.SuppliedEmail = email.fromAddress;
  theCase.SuppliedName = email.fromName;
  theCase.Status = 'New';
  theCase.Priority = 'Low';
  theCase.OwnerId = this.defaultCaseOwnerId;
        theCase.Origin = 'Email';
  theCase.Subject = email.Subject;
  theCase.Description = email.plainTextBody;
  
  Contact[] contacts = [SELECT Id, Name, AccountId, Email FROM Contact WHERE Email = :email.fromAddress];
  if(contacts.size() >0) 
  {
   Contact theContact = contacts[0];
   theCase.ContactId = theContact.Id;
   theCase.AccountId = theContact.AccountId;   
   if(contacts.size() > 1) {
    // Could-Should create a new Case here to get CS to resolve this....
    theCase.Description = 'Note: there is more than on Contact with this email address. Fix this. ' + theCase.Description;
   }
  }
  insertSObject(this.theCase);
 }

 createEmailMessage(theCase,email);
 handleAttachments(theCase, email);

  // send success notification. This can be disabled once we know the code is stable.
 // if(result.success)
 // {
 //  String successMessage = 'Successful processing of inbound email  \n'+ noteBody;
 //  TriggerErrorNotification.reportInfo(messagePrefix+ ' success ',successMessage);      
 // }
 return result;
}

// Save attachments, if any
private void handleAttachments(Case theCase, Messaging.InboundEmail email) {
 if(email.textAttachments!=null && email.textAttachments.size() >0) {
  for (Messaging.Inboundemail.TextAttachment tAttachment : email.textAttachments) {
    Attachment attachment = new Attachment();  
    attachment.Name = tAttachment.fileName;
    attachment.Body = Blob.valueOf(tAttachment.body);
    attachment.ParentId = theCase.Id;
    insertSObject(attachment);
  }
 }
 
 if(email.binaryAttachments!=null && email.binaryAttachments.size() >0) {
  for (Messaging.Inboundemail.BinaryAttachment bAttachment : email.binaryAttachments) {
    Attachment attachment = new Attachment();
    attachment.Name = bAttachment.fileName;
    attachment.Body = bAttachment.body;
    attachment.ParentId = theCase.Id;
    insertSObject(attachment);
  }
 } 
}

private void insertSObject(sObject obj) {
 try {insert obj;} catch (System.DmlException e) {handleError(e, 'Could not insert obj '+ obj);}
}

private String limitLength(String input, Integer maxLength)
{
 String results;
 if(input != null && input.length() > maxLength)
  results = input.substring(0,maxLength);
 else 
  results = input;
 return results;
}

private void createEmailMessage(Case theCase, Messaging.InboundEmail email) {
 String value;
 Integer maxlength;
 EmailMessage theEmail = new EmailMessage();
 theEmail.ParentId = theCase.Id;
 theEmail.Incoming = true;
 Schema.DescribeFieldResult F = EmailMessage.HtmlBody.getDescribe();
 //.HtmlBody.getDescribe();
 maxlength = F.getLength();
 theEmail.Subject = limitLength(email.Subject, EmailMessage.Subject.getDescribe().getLength());
 theEmail.MessageDate = datetime.now();
 theEmail.HtmlBody = limitLength(email.htmlBody,EmailMessage.HtmlBody.getDescribe().getLength());  
 theEmail.TextBody = limitLength(email.plainTextBody,EmailMessage.TextBody.getDescribe().getLength());

 /* **** To */
 value = '';
 if(email.toAddresses != null) {
  Boolean seenOne= false;
  for(String to : email.toAddresses) {
   if(seenOne) {
    value += ';\n';
   }
   to  = extractAddress(to);
   system.debug('ToAddress: ' + to);
   value += to;
   seenOne = true;
  }
 }
 theEmail.ToAddress = limitLength(value,EmailMessage.ToAddress.getDescribe().getLength());
 
 /* **** From */
 theEmail.FromName = email.fromName;
 theEmail.FromAddress = email.fromAddress;
 
 /* **** CC */
 value = '';
 if(email.ccAddresses != null) {
  Boolean seenOne= false;
  for(String cc : email.ccAddresses) {
   if(seenOne) {
    value += ';\n';
   }
   cc  = extractAddress(cc);
   system.debug('CcAddress: ' + cc);
   value += cc;
   seenOne = true;
  }
 }
 theEmail.CcAddress = limitLength(value,EmailMessage.CcAddress.getDescribe().getLength()); 
 insertSObject(theEmail);
}



private void handleError(System.DmlException e,  String message){
 String baseURL = URL.getSalesforceBaseUrl().toExternalForm() + '/';
 if(TRACE)system.debug(baseURL);
 String caseURL;  
 String msg = message + '\n';
 if(this.theCase != null)
 {
  caseURL = baseURL + theCase.Id;
  msg += '\n';
  msg += 'Originating Case Number: ' + theCase.CaseNumber + '  '+ caseURL+'\n';   
 }
 if(this.inboundEmail != null) {
  msg += '\nEmail:';
  msg += '  subject: ' + inboundEmail.Subject + '\n'; 
  msg += '  from: ' + inboundEmail.FromName + '\n'; 
  msg += '  address: ' + inboundEmail.FromAddress + '\n'; 
 }
 if(e != null) { // compose the DmlException message on one line to minimize the number of untested lines.  AFAIK easy to instantiate a DmlException in a unit test. 
  msg += '\n';
  msg += 'EXCEPTION:\n  Error: ' + e.getMessage() + '\n  Type: ' + e.getTypeName() + '\n  Line Number: ' + e.getLineNumber() + '\n  Trace:\n' + e.getStackTraceString() + '\n(end stack trace)\n';
 }

 Case errCase = new Case();
 errCase.OwnerId = this.defaultCaseOwnerId;
 errCase.Status = 'New';
 errCase.Priority = 'Low';
    errCase.Origin = 'Email';
 errCase.Subject = 'Error processing incoming email';
 errCase.Description = limitLength(msg,Case.Description.getDescribe().getLength());
 insert errCase;
 errCase = [Select Id, CaseNumber from Case where Id = :errCase.Id limit 1];  

 caseURL = baseURL + errCase.Id;
 msg += '\n\n';
 msg += 'Created new Case number ' + errCase.CaseNumber + ' for this error.  See: ' + caseURL +'\n'; 

 TriggerErrorNotification.reportError('CaseEmailInBoundUtilities', msg); 

}


/*
Given a case number such as 8144 find the exact case that use this number. Note that CaseNumber is a string field 
that may have any number of leading zeros. 
*/
private Case locateByCaseNumberAsString(String caseNumberStr){
 Integer target = Integer.valueOf(caseNumberStr);
 Case theResult = null;
 String caseNumber = '%' + String.valueOf(target);
 Case[] matchingCases = [Select Id, CaseNumber, Subject, Description from Case where CaseNumber like :caseNumber];
 for(Case aCase: matchingCases) {
  Integer cnum = Integer.valueOf(aCase.CaseNumber);
  if(cnum == target) {
  theResult = aCase;
  break;
  }
 }
 return theResult;
}

/*
Look for the case reference in the email subject line.  First search for a case reference using the
standard Salesforce method of creating that complicated and non-user-friendly reference.  Do this first
so it takes precedence.

But, also search for the case number itself. This is user-friendly!
*/
private String extractRef(String emailSubject)
{
 String itemRef = null;
 String target = emailSubject.toLowerCase();
 String patternString;
 Pattern thePattern;
 Matcher matcher;
 
/*  Take the text between the period and ":ref"  For example in the ref [ ref:00D7JFzw.5007H3Rh8:ref ] extract 5007H3Rh8
 Take that text and remove the 5007. For example H3Rh8 
 Append H3Rh8 to https://na5.salesforce.com/5007000000  to produce https://na5.salesforce.com/5007000000H3Rh8.   This is your link to get to the case.
*/  
 patternString = '.*ref:(.{8}).(.{4})(.+):ref.*';
 thePattern = Pattern.compile(patternString);
 matcher = thePattern.matcher(emailSubject); // do not change to lower case for this test because Id's are case sensitive
  
 if (matcher.matches()) {
  String caseId = matcher.group(2) + '000000' + matcher.group(3);
  if(TRACE) system.debug(Logginglevel.ERROR,'extractRef "' + caseId + '"');    
  Case[] matchingCases = [Select CaseNumber from Case where Id = :caseId];
  if(matchingCases.size() == 1) {
   Case theCase = matchingCases[0];
   itemRef = theCase.CaseNumber;
  }    
 }  
 if(itemRef == null) {
  // extract the Case Number from the email Subject
  // Re: Test two numbers Case: 30088 and Case: 30089'
  // returns 30089, the last pattern matched
  patternString = '.*case[;:=]?\\s*([0-9]+).*';
  thePattern = Pattern.compile(patternString);
  matcher = thePattern.matcher(target);
  
  if (matcher.matches()) {
   itemRef = matcher.group(1);
   if(TRACE) system.debug('Extracted case number ' + itemRef); 
  }
 }

 return itemRef; 
}

private String extractMainSubject(String emailSubject)
{
 if(emailSubject == null || emailSubject.length() < 3)
  return emailSubject;
 String[] prefixes = new String[] {'fw:','re:', 'automatic reply:', 'out of office autoreply:', 'out of office'};  
 String target = emailSubject.toLowerCase();
 for(String prefix: prefixes) {
  Integer index = target.indexOf(prefix); 
  if(index == 0 ){
   String mainSubject = emailSubject.substring(prefix.length(),emailSubject.length());
   return mainSubject.trim();
  }  
 }
 return emailSubject; 
}

private String extractAddress(String inAddress)
{
 String address;
 String patternString;
 Pattern thePattern;
 Matcher matcher;
 patternString = '.*<(.*)>.*';
 thePattern = Pattern.compile(patternString);
 matcher = thePattern.matcher(inAddress);
 if (matcher.matches()) {
  address = matcher.group(1);
  system.debug('Extracted address ' + address); 
 }
 else
 {
  address = inAddress;
  system.debug('Did not match angle-address ' + address);   
 }
 return address;
}
/* *****************************************************************
TEST METHODS
*/
 
 
static testmethod void testExtractAddress()
{
 system.debug('testExtractAddress ');   
 String expected = 'b.g@g.com';
 String target = 'Bryan <'+expected+'>';
 CaseEmailInBoundUtilities prutil = new CaseEmailInBoundUtilities();
 String results = prutil.extractAddress(target);
 System.assertEquals(expected,results);  
target = 'Helen W ';
expected = 'Helen.W@on.com';

 results = prutil.extractAddress(target);
 System.assertEquals(expected,results);  



} 
static testmethod void testExtractRef()
{
 Test.startTest();
 String emailSubject = 'Subject Case: 987 asdas asdasd ';
 CaseEmailInBoundUtilities prutil = new CaseEmailInBoundUtilities();
 String caseNumber = prutil.extractRef(emailSubject);
 
 system.assertEquals('987',caseNumber);
 
 emailSubject = 'Subject cAse=987 asdas asdasd ';
 caseNumber = prutil.extractRef(emailSubject);
 system.assertEquals('987',caseNumber);

 
 Case aCase = new Case();
 insert aCase;
 Case bCase = [Select Id, CaseNumber from Case where Id = :aCase.Id limit 1];
 
 String caseId = bCase.Id;
 String left = caseId.substring(0, 4);
 String right = caseId.substring(caseId.length()-8, caseId.length());
 right = right.substring(0,5);
system.debug(Logginglevel.ERROR,'testExtractRef case id: "' + caseId + '"');    
system.debug(Logginglevel.ERROR,'testExtractRef case n: "' + bCase.CaseNumber + '"');    
   
 emailSubject = 'For example in the ref [ ref:00D7JFzw.5007H3Rh8:ref ] ';
 emailSubject = 'For example in the ref [ ref:00D7JFzw.'+left+right+':ref ] ';
system.debug(Logginglevel.ERROR,'testExtractRef email subject: "' + emailSubject + '"');    

 caseNumber = prutil.extractRef(emailSubject);
 system.assertEquals(bCase.CaseNumber,caseNumber);
 Test.stopTest();
 
 //TRIM(" [ ref:" + LEFT( $Organization.Id, 4) + RIGHT($Organization.Id, 4) +"."+ LEFT( Id, 4)
 //  + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Id, RIGHT( Id, 4), ""), LEFT( Id, 4), ""), "0", "") 
 //  + RIGHT( Id, 4) + ":ref ] ")
 
// LEFT( Id, 4) 
//SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Id, R4, ""), L4, ""), "0", "") + R4  
 
}

 
static testmethod void testExtractMain()
{
 CaseEmailInBoundUtilities prutil = new CaseEmailInBoundUtilities();
 String emailSubject = 'Some Subject Case';
 String fwSubject = 'FW: ' + emailSubject;
 
 String mainSubject;
 
 mainSubject = prutil.extractMainSubject(fwSubject);
system.debug(Logginglevel.ERROR,'testExtractMain "' + mainSubject + '"'); 
 system.assertEquals(emailSubject,mainSubject);

 String reSubject = 'Re: ' + emailSubject;
 mainSubject = prutil.extractMainSubject(reSubject);
system.debug(Logginglevel.ERROR,'testExtractMain "' + mainSubject + '"'); 
 system.assertEquals(emailSubject,mainSubject);

}

static testMethod void testHandleError()
{
 CaseEmailInBoundUtilities emailProcess = new CaseEmailInBoundUtilities();
 // create a new email 
 Messaging.InboundEmail email;
 email = new Messaging.InboundEmail() ;
 email.subject = 'Test subject';
 email.plainTextBody = 'Test email';
 email.fromname = 'FirstName LastName';
 email.toAddresses = new String[] {'someaddress@email.com'};
 emailProcess.inboundEmail = email;
 
 emailProcess.handleError(null,'unit testing handle error'); 
}

static testMethod void testProcessInboundEmailGoodWithAttachment() { 
  Test.startTest();
  // create a new email 
  Messaging.InboundEmail email = new Messaging.InboundEmail() ;
  email.subject = 'Subject asdas asdasd ';
  email.plainTextBody = 'Test email';
  email.fromname = 'FirstName LastName';
  email.toAddresses = new String[] {'someaddress@email.com'};
  email.ccAddresses = new String[] {'one@email.com','two@email.com'};

  String csv = 'this is just a test';
  
  // add an attachment
  Messaging.InboundEmail.BinaryAttachment attachment = new Messaging.InboundEmail.BinaryAttachment();
  attachment.body = blob.valueOf(csv);
  attachment.fileName = 'data.csv';
  attachment.mimeTypeSubType = 'text/plain';
  
  email.binaryAttachments = new Messaging.inboundEmail.BinaryAttachment[] { attachment };
  
  Messaging.InboundEmail.TextAttachment tattachment = new Messaging.InboundEmail.TextAttachment();
  tattachment.body = csv;
  tattachment.fileName = 'data.csv';
  tattachment.mimeTypeSubType = 'text/plain';
  
  email.textAttachments = new Messaging.inboundEmail.TextAttachment[] { tattachment };
  
  // call the email service class and test it with the data in the testMethod
  CaseEmailInBoundUtilities emailProcess = new CaseEmailInBoundUtilities();
  Messaging.InboundEmailResult result = emailProcess.processInboundEmail(email);
  System.assert(result.success == true);
  Test.stopTest();
 } 
 
 static testMethod void testProcessInboundEmailWithMatchingSubject() {
 
  Test.startTest();
  CaseEmailInBoundUtilities emailProcess = new CaseEmailInBoundUtilities();
  Messaging.InboundEmail email;
  Messaging.InboundEmailResult result;
  Case[] matchingCases;
  EmailMessage[] matchingEmailMessages;
  Case theCase;
  
  String mainSubject = 'Subject asdas asdasd';

// Test Part 1
  // create a new email 
  email = new Messaging.InboundEmail() ;
  email.subject = mainSubject;
  email.plainTextBody = 'Test email';
  email.fromname = 'FirstName LastName';
  email.toAddresses = new String[] {'someaddress@email.com'};

  // call the email service class and test it with the data in the testMethod
  result = emailProcess.processInboundEmail(email);
  System.assert(result.success == true);
  matchingCases = [Select Id, CaseNumber, Subject, Description from Case where Subject = :mainSubject];
  System.assertEquals(1,matchingCases.size());
  theCase = matchingCases[0];
  matchingEmailMessages = [Select Id from EmailMessage where ParentId = :theCase.Id];
  System.assertEquals(1,matchingEmailMessages.size());
  
// TEST Part 2
  // create a new email 
  email = new Messaging.InboundEmail() ;
  email.subject = mainSubject;
  email.plainTextBody = 'Test email';
  email.fromname = 'FirstName LastName';
  email.toAddresses = new String[] {'someaddress@email.com'};

  // call the email service class and test it with the data in the testMethod
  result = emailProcess.processInboundEmail(email);
  System.assert(result.success == true);
  matchingCases = [Select Id, CaseNumber, Subject, Description from Case where Subject = :mainSubject];
  // still only one Case because the system will append the second email to the case Case
  System.assertEquals(1,matchingCases.size());
  System.assertEquals(theCase.Id,matchingCases[0].Id);
  // Should be two emails on the case
  matchingEmailMessages = [Select Id from EmailMessage where ParentId = :theCase.Id];
  System.assertEquals(2,matchingEmailMessages.size());
  
// TEST Part 3
  // create a new email 
  email = new Messaging.InboundEmail() ;
  email.subject = 'Different subject line but include Case: ' + theCase.CaseNumber;
  email.plainTextBody = 'Test email';
  email.fromname = 'FirstName LastName';
  email.toAddresses = new String[] {'someaddress@email.com'};

  // call the email service class and test it with the data in the testMethod
  result = emailProcess.processInboundEmail(email);
  System.assert(result.success == true);
  matchingCases = [Select Id, CaseNumber, Subject, Description from Case where Subject = :email.subject];
  // Should be no cases created with that subject 
  System.assertEquals(0,matchingCases.size());
  // Should be three emails on the case
  matchingEmailMessages = [Select Id from EmailMessage where ParentId = :theCase.Id];
  System.assertEquals(3,matchingEmailMessages.size());  
  Test.stopTest();
 } 
 
 static testMethod void testProcessInboundEmailWithContacts() {
 
  Test.startTest();
  CaseEmailInBoundUtilities emailProcess = new CaseEmailInBoundUtilities();
  Messaging.InboundEmail email;
  Messaging.InboundEmailResult result;
  Case[] matchingCases;
  EmailMessage[] matchingEmailMessages;
  Case theCase;
  Contact theContact = new Contact();
  theContact.FirstName = 'FirstName';
  theContact.LastName = 'LastName';
  theContact.Email = 'someaddress@email.com';
  
  String mainSubject = 'Subject asdas asdasd';

// Test Part 1
  // create a new email 
  email = new Messaging.InboundEmail() ;
  email.subject = mainSubject;
  email.plainTextBody = 'Test email';
  email.fromname = 'FirstName LastName';
  email.toAddresses = new String[] {theContact.Email};

  // call the email service class and test it with the data in the testMethod
  result = emailProcess.processInboundEmail(email);
  System.assert(result.success == true);
  matchingCases = [Select Id, CaseNumber, Subject, Description from Case where Subject = :mainSubject];
  System.assertEquals(1,matchingCases.size());
  theCase = matchingCases[0];
  matchingEmailMessages = [Select Id from EmailMessage where ParentId = :theCase.Id];
  System.assertEquals(1,matchingEmailMessages.size());
 }  
}