Posted on by raymond Posted in ToolsTagged , ,
Build a Subscriber Email List Part I have had a website for over a year now, and much to my delight I have modest traffic, real traffic – for a long time it was crickets.  It is gratifying -people are downloading my tools and no doubt improving them, it’s great -that was my plan all along. There was a problem thought, up until recently I was not capturing emails. No, that’s right I didn’t have a list of people that would like to subscribe or people that have emailed request.  Even though every single blogger, podcaster and side hustle expert says you must ‘Build an Email List’.  Yeah I’ve got a problem- I  tend to always swim against the current but after far too long I’ve decided to give in, but how?

I did some digging and there are many plugin options out there to have someone build an email list from your website, essentially capturing and holding your subscriber email database. Many involve a fee if you have more than a few subscribers – I am fee adverse. I guess paying a yearly or monthly fee makes sense to some people.  The thing is isn’t WordPress freeware itself?.  At this point my subscription list is modest, my mailings limited to alerts for new tools and blog posts. Most of all I am an independent person and I wanted to develop and hold my own email list. Naturally, I wanted to use Excel, Access, Outlook,and VBA to be the engine to do that -what else? So to recap here’s what I want to accomplish:

Build a Subscriber Email list Steps
Phase I
1. Open a Gmail account specifically for the subscription process. In this case: ExcelandVBACraftsman@Gmail.com
2. Add a ‘subscribe here’ form where the user can provide their name, email address and a submit button On all pages
3. Have the form generate an email and send it to the address in Item 1.
4. Process that Email by:
A).  Adding a subscribers record (details) to an access database (first check that it                       isn’t a duplicate).
B). Then Move that email into a Subscriber folder.

Phase II
5. Send a “Thanks for Subscribing” Email, which will also test the validity of the email address.  Then Update the subscriber record for those that fail.
6. Build in the functionality to:
        A). Send out mailings- Individual and mass mailings – say an alert of a new post.
        B)  Edit the subscriber Email list details record field Valid_Email and make it false
7. When the tool searches for new subscribers have it check for returned mail if found  update the Access Database subscriber details  Valid_Email to False Move any invalid email returned mail emails to the subscriber Trash Folder 

It simply says:
If you would like to be notified of future post and new tools please subscribe below
Your Name (required)
Your Email (required)
Oh, and it includes a submit button

 

* note the honey pot and quiz questions – they are there to thwart those nasty “bots” from being an annoyance.

  1. Build an Subscriber Contact Form First, I did some research and found that the plugin Contact Form 7, the most widely used contact vehicle in WordPress is highly customizable. It’s the plugin that nearly everyone uses for their contact me page- (including me). So, I created a simple ‘subscription’ form using contact form 7 that would capture just the a potential subscriber’s name and email address.
1. I installed that that new email subscriber form into my Blog by copying the lines of code (with the Blue background) into a short code box at the very bottom of each page.  So if a reader of my blog or a tool page decided that they liked my ‘stuff’ and wanted to see more they would fill out the two text boxes and hit the submit button and an email would be sent to me. I also opened a new Gmail account: ExcelandVBACraftsman@Gmail to keep all this mail activity in a place.  I added that  Gmail account to my Outlook.
Contact Form 7 also allows you to customize that Email that is sent to you. I made sure to include the subject keyword that I can use to “grab” that email later – Note: the user can’t see any of this:  In this example I used New_Joiner.  Once the email is received it sits in my inbox until the next time I open Excel.  [I learned the hard way – keep the mail simple I had to change the language to keep google from putting the emails in the trash.] Use a honey pot and quiz question to thwart those annoying hackers and bots.
2) Have the form generate an email and send it to me.
Its been Several days since I added my “care to subscribe?” boxes on my blog. (I have a couple of client jobs keeping me busy.) Guess what it is working.  I am receiving Email subscriptions- Nice! Ok there was a lot of phony emails coming in.  It appears I attracted the attention of a hacker. I added both a honey pot and a quiz question and the bogus mail stopped. – You can see them above the submit button on the Contact form 7 code. Oh the same spammer found my blog and I had to use some technology against him too. But that’s another post.

3) Process that Email by Adding a subscriber record (details) to an Access database.

Now that I have the Emails coming in I need a place to store them.  I created a Blank Access Database and named ‘Subscription Manager’ in a folder of the same name in the root directory: (C:\Subscription Manager the excel file must be there too).     Yes, I could have used an Excel worksheet but I want to make it scalable and take advantage of the power of a relational Database.  Don’t have Access? well, you could just as easily use mySQL or one of the free databases out there. 

I then created a six-field table in Access called tbl_Subscriptions. 1. ID an automumber to act as the primary field 2. Subscriber_Name 3. Subscriber_Email 4. Subscription_Date 5. Subscription_Notes 6. Valid_Email (Deafult = Yes)
After getting this far along in the code development I realized the code was geting a bit volumous and frankly complex and not because of the number of steps.  So I decided to make it a reusable tool and do this blog in two part sequel.  Ill cover the auto-subscriber capture in this Part I and the email address verification in Part II.
On the left you will find the Subscripition Manager tool – The first page of which you provide the Mailbox Name and necessary folder structure for the tool to sweep through your inbox and  capture and move our “New_Joiner” emails.  I have also provided a snapshot of the relevant section my Outlook email structure below so you can see where the data comes from. A couple of Hints: 1. The folders Subscribers and SubscribersTrash are not subfolders of the inbox. They are below [Gmail] or ‘Main’ folder. 2. As I mentioned I created a new Gmail account for the tool:, ExcelandVBACraftsman@Gmail.com.  I did this purely to keep this process discreet from my regular mail.   I strongly suggest you do the same. Keep them separate! 3. The Keyword (in the example: New_Joiner) must be 10 characters long and must include am underscore “_”.  I did this to make certian the keyword is unique and we only capture New Joiner Emails. 4. The test key is there for you to test if the folders and Mailbox Name you have provided are correct.  Make sure to have some normal mail and some test new joiners mail in the inbox.  You simply send some test joiners by filling out your subscription contact form with some test data. 
4. Then file that email into a Subscriber folder. I just hit the Test button on the Subscription Manager.  The status form tells us that the tool found 7 emails in the inbox and 4 of them were New Joiners.  You will note the tool cycles 3 times through the Outlook Inbox to ensure all emails are read and captured if appropriate. Once you have set up the mailboxes and folders and tested them sucessfully you can hit the Capture New Joiner Emails button.  The joiner mail will be moved to the sub joiner folder (sub = Subscription).   A new Joiners record will be added to the access database with email and name.  If its a duplicate it will be ignored.

If you enjoyed my blog and would like to comment or share a similar experience please send your comments  Here.
If you would like to say hello and Buy me a cup of coffee please follow the link.

Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets. Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here

If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me