Customer Relations Improver

If you have purchased Microsoft Office® with volume licensing, you’ve also got an Outlook® extension tool named Business Contact Manager (BCM). That’s an interesting software piece, as it provides complete customer relation management (CRM) features, but still, in my opinion, it’s very heavy: it uses a complex SQL Server® database infrastructure, has (too) many features for common users, and introduces performance issues and too many dependencies upon Office® upgrading, all these besides the inconvenience of not being available with standard licensing schemes.

Personally, I needed something lighter: a tool that simply checks Inbox periodically for conversations that occurred between me and people outside of the company at least one month ago but not continuing afterwards, listing the e-mail addresses that I should probably write to in order to ensure we don’t lose possible customers. (And this tool should be free or at least not very expensive.)

I’ve never found a solution so I’ve eventually created a CRImprover service myself, as an Outlook® 2013 add-in project using C# and Visual Studio® 2013 – see its primary source code below.

Now, every time I run Outlook® my tool automatically checks for possible customer relations that need to be improved (and it can be periodically called also while it’s loaded using simple Timer instance), automatically creating Outlook® tasks referring e-mail addresses that it selects as of possible customers that I’ve talked to several times a month or more time ago but not later. And when I mark the task as completed, it never gets recreated again.

ThisAddIn.cs:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    var session = Application.Session;
    var execution = (System.Action)delegate { Execute(session); };
    execution.BeginInvoke(null, null);
}

private void Execute(NameSpace session)
{
    MAPIFolder inboxFolder = session.GetDefaultFolder(
        OlDefaultFolders.olFolderInbox);
    foreach (var item in inboxFolder.Items)
    {
        try
        {
            var mailItem = item as MailItem;
            if (mailItem == null)
                continue;
            var customerInfo = 
                GetInfo(mailItem.SenderEmailAddress);
            if (customerInfo == null)
                continue;
            UpdateInfo(customerInfo, mailItem.ReceivedTime, 
                true, mailItem.SenderName);
        }
        catch { }
    }
    
    MAPIFolder sentFolder = session.GetDefaultFolder(
        OlDefaultFolders.olFolderSentMail);
    foreach (var item in sentFolder.Items)
    {
        try
        {
            var mailItem = item as MailItem;
            if (mailItem == null)
                continue;
            foreach (Recipient recipient in mailItem.Recipients)
            {
                var customerInfo = GetInfo(recipient.Address);
                if (customerInfo == null)
                    continue;
                UpdateInfo(customerInfo, mailItem.SentOn, 
                    false, recipient.Name);
            }
        }
        catch { }
    }


    var maxFinish = DateTime.Now.AddDays(-30);
    var minDuration = TimeSpan.FromDays(7);
    var minCount = 2;
    var customersToImproveRelationsWith = 
        from c in customers
        let i = c.Value
        where i.LastSent <= maxFinish && 
              i.Duration >= minDuration && i.Count >= minCount
        select c;
    var contactsFolder = session.GetDefaultFolder(
        OlDefaultFolders.olFolderContacts);
    
    var outputFolderName = "CRImprover";
    MAPIFolder outputFolder = null;
    foreach (MAPIFolder folder in contactsFolder.Folders)
    {
        if (folder.Name.ToLowerInvariant() ==
            outputFolderName.ToLowerInvariant())
        {
            outputFolder = folder;
            break;
        }
    }
    if (outputFolder == null)
        outputFolder = 
            contactsFolder.Folders.Add(outputFolderName);

    foreach (var customer in customersToImproveRelationsWith)
    {
        var emailAddress = customer.Key;
        if (outputFolder.Items.Cast<ContactItem>().Any(
            c => c.Email1Address.ToLowerInvariant() ==
                 emailAddress))
            continue;
        ContactItem contact = 
            outputFolder.Items.Add(OlItemType.olContactItem);
        var info = customer.Value;
        var name = info.Name;
        if (!string.IsNullOrEmpty(name))
            contact.FullName = name;
        contact.Email1Address = emailAddress;
        contact.MarkAsTask(OlMarkInterval.olMarkNoDate);
        contact.TaskSubject = (!string.IsNullOrEmpty(name) ? 
            name + " (" + emailAddress + ")" : 
            emailAddress) + " - CRImprover";
        contact.Body = info.FirstReceived + " - " + info.LastSent;
        contact.Save();
    }
}

private Dictionary<string, Info> customers = 
    new Dictionary<string, Info>();

private Info GetInfo(string emailAddress)
{
    emailAddress = emailAddress.ToLowerInvariant();
    if (!emailAddress.Contains("@") || 
        emailAddress.EndsWith("@" + "mydomain.com"))
        return null;
    if (customers.ContainsKey(emailAddress))
        return customers[emailAddress];
    var info = new Info();
    customers.Add(emailAddress, info);
    return info;
}

private void UpdateInfo(Info info, DateTime time, 
    bool received, string name)
{
    if (received)
    {
        if (info.FirstReceived > time)
            info.FirstReceived = time;
    }
    else
    {
        if (info.LastSent < time)
            info.LastSent = time;
    }
    info.Count++;
    if (!string.IsNullOrEmpty(name) && name.IndexOf("/cn") < 0)
    {
        if (string.IsNullOrEmpty(info.Name) || 
            info.Name.IndexOf("@") >= 0)
            info.Name = name;
    }
}

private class Info
{
    public string Name;
    public DateTime FirstReceived = DateTime.MaxValue;
    public DateTime LastSent = DateTime.MinValue;
    public TimeSpan Duration { 
        get { return LastSent - FirstReceived; }}
    public int Count = 0;
}
Advertisements

About Sorin Dolha

My passion is software development, but I also like physics.
This entry was posted in .NET and tagged , , . Bookmark the permalink.

Add a reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s