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).
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
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 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.
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.