Any MS Excel Spreadsheet Developers Out There?

Joseph Herrin (08-15-2015)

Yahweh has greatly blessed me to be able to reach out to a growing number of men in prison. At this time while I am completing some personal projects to free my thoughts up from distractions, so that I might be more efficient in the carrying out of my ministry duties, I am also looking at ways to handle the prison newsletter ministry more efficiently.

Since I began the prison newsletters in 2009 I have been keeping track of the associated information in the most rudimentary method. I have a sheet of paper printed out for every man with their name, prison ID, and address, followed by a table that lists every newsletter currently available, which presently numbers around 120 editions. I place a check through each box on the table as I print out the newsletter so that I know what has been mailed to each person.

This basic method of data keeping worked fine while the number of men was small. As the number of recipients of the newsletters has expanded, it has become increasingly laborious to sort through a growing stack of paper to find the appropriate form when I need to make a change to a man’s contact information; check to see what newsletters have been mailed to an individual; make an alteration to the order in which a man receives the newsletters; or to suspend, stop, or resume sending of newsletters.

I am at the point now where I need to place this information on computer so that I can quickly access a man’s file and make alterations to his information. I believe that MS Excel offers the abilities to create the type of forms I need. I would also like to have a graphical user interface (GUI) created to make the input and editing of data more streamlined. (An example of a GUI that was created to make entry of data into a table more accessible is shown in the image at the top of the page.)

I use MS Publisher 2007 to create and print the Parables Bookshelf newsletters. Publisher has a mail merge feature which I have used to create a table of basic mailing address information. This allows me to open any newsletter and click on the button to open the mail merge utility where I can select men from the list who will receive that specific newsletter for the week.

Screen Shot of MS Publisher Mail Merge

This mail merge feature has been a great time saver, but it too is beginning to suffer from the scale of growth as there are now more than 160 men’s names and their mailing information entered into the table.

Description of Job Requirements

Basically, I have two different data tables. One contains all the contact information of the men who
receive the Parables Bookshelf Newsletters. This information includes the following data fields:

First Name
Last Name
Prisoner ID#
Prison Name
Mailing Address line 2 (street address or P.O. Box)
City
State
Zipcode

The second table includes the man’s name and contact information followed by a listing of all the available newsletter editions beginning with Newsletter 1.1.1 and currently ending with 2.1.20 (122 newsletters, and more to be created soon). See image of sample sheet below.

(The series beginning with 1.18.1, 1.19.1, and 1.20.1 have not been created yet. These are reserved for books I have written but have not yet put into newsletter format.)

As previously mentioned, the information shown in the printout above has not been computerized. It is kept on sheets of paper printed out for each man on the mailing list. I place an X across each box once the newsletter associated with that number has been printed for the individual.

Aiming Small

Following is my minimum aspiration for what I would like to have done.

I would like to take the table above and migrate it from paper to MS Excel. I would then like to have a GUI created that would present me with the option to Add, Delete, or Edit entries to this data table. I would like the additional ability to be able to mark which newsletters a man has received, and to compile a list of what newsletters need to be printed each week.

The spreadsheet should include the following print options. Print a list sorted by newsletter ID number that gives the name and prison ID # for each man who will receive specific newsletters each week. For example, if 4 men have written to me to ask to join the mailing list that week, then associated with the ID number for newsletter 1.1.1 would be the names and prison ID number of these 4 men. This would be followed on the printout by newsletter 1.1.2 and the names and ID numbers of the men receiving it that week, and on down the line.

I would also like the ability to generate a report on any individual man showing what newsletters he has received to date. I would also like to be able to generate a report listing every man on the mailing list and their contact information, sorted either alphabetically, or by prison name.

Extra Credit

A further feature would be the ability to note a change in the order of the newsletters a man is to receive. For example, if a man requests that he receive the book Dragon Flood next, I would like to be able to indicate that he receive the series beginning at 1.15.1 next. This could be as simple as putting a flag on the entry that I would see when I pull up the man’s account.

This does raise an issue to be considered in generating the list of what newsletter is to be printed for each man each week. The printing used to follow a specific order with very little deviation. I started all men out at newsletter 1.1.1 and proceeded in order from there. That has changed in the past 6 months as men began receiving the Dragon Flood series in prison. Now many new men write to me asking if I will send them that series first. I also receive requests from men who want to receive other series out of their standard order. It would be nice to have some way to create a custom queue for each man when necessary, that would list a specific order in which they are to receive the newsletters. This custom table would be accessed by the report program that generates the list of newsletters to be printed each week.

I would then be able to take the print out of the report showing the newsletters to be printed each week, and the men receiving them, and use it as a reference to enter the information in MS Publisher as I print out the newsletters using the mail merge feature.

Aiming Big

Following is my maximum aspiration for what I would like to have done.

I am not familiar enough with the capabilities of MS Publisher and Excel to know how far they can interoperate. I am assuming, however, that Publisher can read a mail merge table created by Excel. It would be great to be able to automate the printing of newsletters in Publisher without having to manually open each newsletter that needs to be printed for the week and selecting the men who are to receive it from a list containing all the names of men currently enrolled.

My very limited experience in this area will be reflected in the possibilities I set forth here.

If Excel can generate a separate mail merge table for every newsletter (all 122 of them, and counting), and populate the tables with the data for each man to receive specific newsletters for that week, possibly a macro could be created in Publisher that would open each newsletter in order, check the mail merge table associated with that newsletter, and if any men are listed for the week, print the newsletters out.

If Visual Basic works with both Excel and Publisher, perhaps a VBA program could be written to do this same task.

A final option I have thought of is there might be some software product available already that will do what I am suggesting here. I am not familiar with this genre of software offerings, but perhaps some person who reads this blog does have knowledge and experience in this area. I am in a very general sense aware that there are mail management and newsletter management programs available, but I do not know their capabilities, or if any of them would perform the type of automated functions I have described. Any program would have to work with newsletters I have already created in MS Publisher. I do not want to have to create the newsletters all over again in another program. That would be a huge task since there are more than 120 distinct newsletters created at this time. If there is a newsletter, or mail management program, that includes the features I am looking for that can import MS Publisher documents, then it may be a viable option.

To the readers of this blog who have experience or knowledge of the things mentioned, please consider this post an open invitation to share your thoughts on what has been written, and to mention other approaches or solutions I may not have thought of due to my limited experience in this area.

Heart4God Website: http://www.heart4god.ws    

Parables Blog: www.parablesblog.blogspot.com    

Mailing Address:
Joseph Herrin
P.O. Box 804
Montezuma, GA 31063