Batch Email/Text Sender with Data Import

The Business Problem 

Last semester, I was called to be the activities coordinator for my church in my area. I quickly realized that there was a “business problem” that made fulfilling my responsibilities more difficult. The congregation where I attend church is unique compared to other congregations of our church in that it is made up entirely of college students. This means that the congregation is very transient with people coming and going all the time. As part of my responsibilities, I am sending out correspondence via email/text all the time. The “business problem” that I faced was how to keep up communications about activities with an ever-changing member base. In fact, the member base changes so much that almost weekly I received a new member directory with new members added and old members removed. In short, my email list was out of date almost every week. I needed a way to import the new list every time it was sent to me. In addition to this, I want to be able to send group texts with out having to enter in every number manually each time. Finally, I thought that I could increase turnout at activities by sending both emails and texts to everyone to make sure that everyone is informed about the activity. The solution that I built solves all of these problems by allowing me to import the new directory into Excel and send batch emails/texts (or both) to either the entire list of members or certain people that I select. In short, my business is a combination of data importing and batch messaging and while this solution solves my business problem, I believe that it could also be easily modified to help anyone who is in need of an efficient way of sending batch emails/texts to lists of subscribers, registrants, students, etc.

System Overview

As stated before, my system allows for a comma-delimited .txt file (this was how my list was formatted, but other options are available with slight modification of the code) containing user data to be imported in to Excel. Once imported, the data is automatically organized in to a table with headers at the top for first name, last name phone number, email and mobile provider. Once data is imported, the system contains three forms that allow the user to work with the data. The first form is the Send Form, which allows the user to select both email addresses and phone numbers to send emails/text too. If the user wants to find a specific member or group of members, you can access the Find Form from the Send Form by click the respective “Add Phone Number” and “Add Email” buttons. This takes you to the Find Form where you can search by all or part of the first or last name of the person you are attempting to find. Once found the form lets you add the personal information to either the email or phone list. Finally, an Add Form has been included, which allows the user to add a new member to the table if you don’t yet have the new file containing the data for that person. More in-depth information about the implementation of these forms is included below in the user manual. Last but not least, one unique feature of this system has to do with its ability to send text messages. In order to send a text message from Excel, the user needs to know the current mobile provider of the phone number that will receive the text. In creating this system, I assumed that users working with large amounts of data (i.e., long lists of phone numbers) wouldn’t be able to know the provider of every phone number in their dataset. My system solves this problem with automation of the internet. The system takes each number in the dataset and runs it through whitepages.com’s reverse phone search. This returns the provider for that given phone number and the system adds it to the last column of the table. Once the system is done importing the provider data for all of the phone numbers, the user can then send text messages to any of the phone numbers in the list. While some reverse phone number search engines are very inaccurate, whitepages.com’s search returned the correct mobile provider for every phone number I tested, even phone numbers that have been ported from one provider to another. The result is a system that is very accurate for sending text messages. This feature will also be further discussed in the user manual.

Here is the User Manual: 
http://files.gove.net/shares/files/13w/ctrben/Final_Project_Write_Up.pdf

Here is the Excel Workbook and Text File(this will be imported into the worksheet):
http://files.gove.net/shares/files/13w/ctrben/Final_Project.xlsm
http://files.gove.net/shares/files/13w/ctrben/21st_Ward_Directorytxt.txt