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!
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" .SignOn End With Set mapi_messages = New MSMAPI.MAPIMessages With mapi_messages .SessionID = mapi_session.SessionID .Compose .RecipIndex = 0 .RecipDisplayName = Recepient .RecipAddress = Address .RecipType = mapToList .AddressResolveUI = False .MsgSubject = Subject .MsgNoteText = Contents .Send False End With mapi_session.SignOff End Function
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 “
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.
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.