Automatically importing emails to SugarCRM using custom logic

Some six months ago I accepted a new position with SugarCRM, which I am enjoying enormously. Here's a bit of code that I've sent out as an example a couple of times when advising developers, so I thought I would share it.


Suppose you want to integrate a legacy email interface into SugarCRM, or that you are using some custom header to identify a customer, or something along those lines. As far as you have a very specific idea of what you want to be doing, you can implement that easily in, for example, Python, as shown below.

import poplib
from email.Parser import Parser
import email as emaillib
import re
from suds.client import Client
import logging

#configure logging
logging.basicConfig(level=logging.INFO)
logging.getLogger('suds.client').setLevel(logging.INFO)

#get email from gmail
host = "pop.gmail.com"
mail = poplib.POP3_SSL(host)
print mail.getwelcome()
print mail.user("SOMETESTEMAILADDR")
print mail.pass_("THEPASSWORD")
print mail.stat()
print mail.list()
print ""

if mail.stat()[1] > 0:
 print "You have new mail."
else:
 print "No new mail."

print ""

#initialize generic email parser
parser = Parser()

#process message list
numMessages = len(mail.list()[1])
if numMessages > 0:

  #initialize SugarCRM Session (login, etc)
  USERNAME= "someusername"
  PASSWORD= "somepassword"

  SUGAR_SOAP_URL = "http://MYSUGARCRMHOST.URL/soap.php"
  SUGAR_WSDL_URL = SUGAR_SOAP_URL + '?wsdl'

  #create proxy for the SOAP calls
  sugar = Client(SUGAR_WSDL_URL)

  #build an authentication object for login
  auth = sugar.factory.create("user_auth")
  auth.user_name = USERNAME
  auth.password = PASSWORD
  auth.version = "1.1"

  #perform login and keep sessionid for later calls
  session = sugar.service.login(auth, "mailarchiver")
  sessionid = session.id

  #iterate through the messages and import them in Sugar, associated to a contact with the email
  #address specified in the "EmailToRelate" field in the body. The association can also function trhough
  #email headers, any type of body data that can be looked up, etc.
  for i in range(numMessages):

    #parse the message
    msg = mail.retr(i+1)[1]
    email = parser.parsestr('\n'.join(msg))

    #find the email address in the body field with a regexp and store it
    bodyText = email.get_payload()
    m = re.search('^EmailToRelate\: (.*)$', bodyText, re.MULTILINE)

    searchemail =  m.group(1)

    #lookup the contact with the email. Normally lookup works easier, but for email addresses of
    #users/leads/contacts use this example
    query = "contacts.id in (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 and ea.email_address = '" + searchemail + "')" 
    response = sugar.service.get_entry_list(sessionid,'Contacts',query, '', 0, ['id'],1,0)
    contactid = response.entry_list[0].id

    #collect the data we will archive in Sugar from the parsed email
    emaildata = [
      {'name' : 'from_addr', 'value' : email.get("From") },
      {'name' : 'to_addrs', 'value' : email.get("To").replace('\n','') },
      {'name' : 'name', 'value' : email.get("Subject") },
      {'name' : 'description', 'value' : email.get_payload() }
    ]

    #store the email in Sugar, and keep its newly generated id in res
    res = sugar.service.set_entry(sessionid, 'Emails', emaildata)

    #associate the newly created email with the contact we looked up
    relval = sugar.factory.create("set_relationship_value")
    relval.module1='Contacts'
    relval.module1_id = contactid
    relval.module2='Emails'
    relval.module2_id=res.id
    sugar.service.set_relationship(sessionid, relval)

#flush and quit
mail.quit()

The code above fetches an email from gmail, parses its body to find a specific "field", EmailToRelate in this case, identifies a record within SugarCRM related to the field, stores the email in SugarCRM, and associates it with the record found. The example illustrates using Python and suds for call the SugarCRM SOAP interface, which is really quite easy. I suspect my Python leaves things to be desired, and I will appreciate corrections.


On a more general note, I recently had a discussion with a partner regarding the most elegant way to archive emails, independent of the email client the end user is using. At the moment, the best way to archive emails is using our interactive Outlook plugin, available for Outlook 2003, 2007 and 2010. Forwarding the emails to a machine readable mailbox, and having them automatically harvested and imported into Sugar is generic enough, but the lack of interactivity might prevent the system from recognising the user's intent, and lead to archiving the email at the wrong level (e.g. at the Contact level, instead of the Opportunity level, etc). My current thinking is that it would be cool to do forwarding and automatic harvesting, but follow it with an interactive popup within SugarCRM, where the proper association can be defined. This would achieve both platform independence and flexibility. In order to achieve this in a scalable manner, however, one needs a real-time notification mechanism for SugarCRM. I my next post, I will demonstrate a way to build such a mechanism using a simple evented node.js notification server.