Mailmerge without using Microsoft Outlook uses only Excel, VBA, Word, and CDOSys

Summary: Send personalized emails to a list of email addresses and names in Microsoft Excel.

Technologies used

  • Microsoft Excel
  • Microsoft Word
  • VBA (Visual-basic for Applications)
  • CDOsys (Collaboration Data Objects for Windows 2000)

CDOSys Documentation

It’s amazing how poorly documented it is.. but this link has some answers.
https://msdn.microsoft.com/en-us/library/ms526318(v=exchg.10).aspx

And Dan at Microsoft has several links:
http://blogs.msdn.com/b/webdav_101/archive/2015/05/18/about-cdo-for-windows-2000-_2800_cdosys_2900_.aspx

 

DOWNLOAD THE SOLUTION

Download the Solution

IMPORTANT NOTE/BUG:
As of this article, I haven’t fixed this. Excel hangs (sort of) after sending. It works – everything sends but then the Excel screen becomes non-responsive. CPU and memory are fine. If I select the screen, press <alt + f> and then “S” for save, I can get to the sheets. Close Excel and re-open and all is well. If you solve this, let me know. If I solve I’ll post it on the blog.

 

Solution Notes

Some notes to explain what I’m doing. I also explain why we created it in case you’re curious.

Files

Two files in the Zip.

  • A formatted word document
  • An Excel file with two sheets and the code

 

The Word Document

I’ve gratuitously formatted this document with text and pictures of yours truly plus information about what I do. Hey.. I’m allowed, I’m giving you free stuff.

You may of course, modify this document or you can keep those photos and the text in all your future emails and promote the hell out of me. I thank you in advance.

Here are some notes about formatting the Word document: And if you are not comfortable formatting a Word document.. umm.. really? Get comfortable with it. (or ask me).

  • Insert a Table with 1 column,
    column preferred width: 6.49”
    Preferred width means it will scale down for mobile clients. That’s important.
  • For each section of text, use a different row. It’s easier to manage word wrapped images this way. Take on word on this.
  • When formatting an image for word wrapping, right mouse click on the image and:
    Word-Text->More Layout Options->
    Wrap text Right only for left aligned images and Left only for right aligned images.
  • Use Arial 14pt (or something 14+pts). It’s easier to read on mobile.
  • Just experiment. That’s always a good idea.

The Excel Workbook

The Excel workbook has two sheets. Sheet 1 “Account Info” stores your email account information. Sheet two “Mail List” is for your email address list. Oh.. you’ll need to enable your “Developer” tab in Excel. If  you are unsure how to do that, here is a secret place you can go to find out.

A note about security:

Someone’s going to bring this up. Do I really have a sheet with the email account information? Yes.. this is for internal use, we change the email account passwords frequently, and this is a productivity/efficiency solution, not a distributed application.

 

What the code does

  • Let’s you select your formatted Word document and places a path to that document in the “Account info” sheet.
  • Selects that Word document, creates (if necessary) a folder below your My Documents folder called, “Email-campaigns” and saves the document as HTML with the name:
    yyyy-mm-dd-cmpn.htm – an associated sub-folder is created with images.
  • Reads your email account information into memory
  • Reads the HTML from the above saved Word document into memory as “strOrigHTML”
  • Creates a CDO configuration object
  • Find the first email address.
  • Reads each email address and the name to send the email
  • Replaces text (first name) in the strOrigHTML and uses that to save the HTML over the original file.
  • Creates a CDO message and uses the CREATEMHTMLBody method from the newly saved HTML file. Hint: this is why we maintain and do not change the “strOrigHTML” information. It has to perform the replace function on the original code.
  • Timestamps the email address. To send to the same list, you need to remove the timestamp. I did this so that, should you stop the process or something crash, you can pick up right where you left off. Cool eh?

Things to consider

  • Most email systems have daily and hourly limits. That is why I have those fields identified in the sheet. I’ve added additional code to my solution that throttles how many emails go out in hourly and daily. If you want that, well.. ponder it for a bit and write it. It’s an interesting exercise.
  • I added a reference to “Microsoft CDO for Windows 2000 Library” in my code to give me access to the methods and properties of the objects. However, you can use early binding. Then you won’t need to include the above reference. It is a simple modification and makes deploying your Mailmerge tool simpler.

 

EARLY BINDING

Dim cdoConf as CDO.Configuration becomes

Dim cdoConf as Object

Set cdoConf = new CDO.Configuration becomes

Set cdoConf = CreateObject(“CDO.Configuration”)

….and so on…

 

The why or…What’s wrong with Mailmerge through Outlook?

Why go to so much trouble to avoid using Microsoft Outlook? There are several reasons.

  • Not all version of Office include Outlook but most people have Excel and Word.
  • Outlook requires that we setup a pop or imap account. It also requires that the account you are sending from is the default account. This solution requires NO configuration on the client.
  • Our client uses Gmail as their standard client. So do I. I own Outlook, we automate it, but I do not use it personally.

 

Our client’s dilemma and why they didn’t want the mailmerge to use Outlook

Our client sends out product information to a select list of media and publicity outlets. However, depending upon the workload, the product being covered, and who is available, several different individuals may be responsible for sending these emails. The emails all need to be sent from one email account. However, the person sending the email is not the person responsible for monitoring responses to that account.

They wanted a way to do a mailmerge with a formatted Microsoft Word document but not have to configure Outlook for each user’s desktop. Some of them have Outlook, some don’t.

Our client has a team of people supporting a sales organization. Depending on workload, different individuals may be tasked with contacting their partners with frequent media pitches and notifications. All of these emails come from the same account but none of the individual’s tasked with getting the emails out need to check this account. Responses all go to the same individual.

We had been setting up outlook and creating a special Send/Receive Group that did not check or retrieve mail form the mailbox. This configuration had to happen for each person who had to send out the emails.

These individuals are part-time contractors. Purchasing Outlook and setting up each client was time and effort intensive.

This solution removed their challenge and solves their problem. Voila!

Posted in Consulting, Programming, Tips and Tools and tagged , , , , , .

Leave a Reply

Your email address will not be published. Required fields are marked *