Turn Excel into Your Customer Email Command Center

Posted by Colleen Ludgate on Saturday, Feb 24th, 2018
Category : Microsoft Excel

Turn Excel into Your Customer Email Command Center

One of the key features with commercial email list, lead management and CRM services is the ability to create message sequences. After a new lead is added they will receive each message in the sequence taking into account how many days has passed so they get each message at just the right time.

While these tools are very useful, what if you do your referral and lead management in a spreadsheet? Can you do all of this right in Excel? Yup, you can, and the boffins at Cogniview worked out a solution so you can too!

How to Send an Email from Microsoft Excel

The first step is working out how Excel can send out an email. The tricky part would seem to be there is no email functionality built in and no “email” command that you can call. Luckily for us, the nice folks at Microsoft have included the feature into Windows until recently.

To send a mail message from Excel, we use MAPI – the Windows Mail API. It connects to your current mail client and uses it to send e-mail messages.

In order to get access to this API we need to add the MAPI objects to Excel’s VBA. First you need to open the macro editor, then select the ‘References…’ command from the Tools menu. Click ‘Browse’, and select the MSMAPI32.OCX object library from the Windows’ system32 folder (usually found in the directory C:\WINDOWS\SYSTEM32).

Once we have access to the MAPI object, we can use the SendMail function to send an e-mail:

Function SendMail(Recepient As String, Address As String, _
         Subject As String, Contents As String)
   Dim mapi_session As MSMAPI.MAPISession
   Dim mapi_messages As MSMAPI.MAPIMessages
   Set mapi_session = New MSMAPI.MAPISession
   With mapi_session
       .LogonUI = False
       ' Fill in username and password
       ' if necessary on this mail server.
       '.username = "username"
       '.password = "password"
   End With
   Set mapi_messages = New MSMAPI.MAPIMessages
   With mapi_messages
       .SessionID = mapi_session.SessionID
       .RecipIndex = 0
       .RecipDisplayName = Recepient
       .RecipAddress = Address
       .RecipType = mapToList
       .AddressResolveUI = False
       .MsgSubject = Subject
       .MsgNoteText = Contents
       .Send False
   End With
End Function

Sending Emails in Sequence

To show how you can create a list of leads to email and a sequence of messages, we created an example spreadsheet called ReferralTracking.xlsm that contains a sample data and macros.

The first sheet, Potential Clients, contains the details of referrals in the first three columns and the date of the referral on the fourth. The next two columns contain data about the messages that have already been sent to them so that we can email them the next in our sequence. Add new referrals to the first sheet by appending more leads and filling the appropriate columns with data.

The second sheet, Data, contains the sequence of messages to be sent, along with personalisation place holders marked with “” and ““, to be replaced with the names of the lead. We also need to add the amount of time to wait before sending the message.

To send e-mails, press the ‘Send Mail’ button on the first sheet (after adding the MAPI object as mentioned earlier). A macro called ‘SendMails’ will be called to go over the referrals and send messages if enough time has passed from the previous one.

Further Reading

How to use MAPI from VBA: p://www.vb-helper.com/howto_vba_email_mapi.html

The MAPI control API at MSDN: p://msdn.microsoft.com/en-us/library/aa228261%28VS.60%29.aspx


For many people a full CRM suite is too much functionality and too much expense. Adding this email functionality to your lead and referral management spreadsheet could be all you need. I can also imagine using this approach to keep in touch with networking contacts, perhaps a “how have you been?” message every few months. How about extending the macro to send birthday emails to all your clients? Can you think of other ways these features could help you in business?


Don’t forget to check out our PDF to Excel Converter. It can save you a lot of precious time and improve your productivity.

Latest From Our Blog