Automated Letter Composition

[jmwheatley]

Hello all, noob here,

I have a more general question, unless you guys have more specific answers for me.

Scenario: My company has to compose letters to accompany map products that have to be sent to every community involved in a particular study. This is repeated for every study (usually county-wide), and the task is very redundant. Currently it is done manually and well, I know there is a better way.

Before the letter composition begins, one member of the team researches information regarding each community. They capture information such as Primary Contact's Name, Address, etc. Along with that is information internal to our company that is needed in the letter. All of this information input into an MDB.

There is a deduction matrix used to determine the content of the letter, based on specifics (attributes in the table) of the project for that locale. There are 7 different letter options (templates).

What I would like to do is have Access and Word communicate to automate this letter generation. I've tested Word's MailMerge functionality and was able to see how this can be done. But I'm looking for further automation.

What I would like to happen is this:

1. Have Word access the MDB and look for the letter number for each record (field = LETTER_NUM), and launch that template.
2. Populate the template with the information where it is needed. Using MailMerge or another method that will work the same way.
3. Save the document with a specified naming convention. This can even point to a field in the DB if need be.

Ideally, this would be automated for every record in the DB. Essentially, running as a batch.



I'm fairly certain this is possible. However, It's a bit beyond my knowledge of the Office Suite. I'm looking for any help I can get in order to get me started. I guess I need to know what programming languages should I investigate to complete this task? Or if there is anything already in place that will accomplish what I'm trying to do here, that would also be great! If you feel like helping me figure it out, that is also appreciated! :-)

Currently this is done manually. The database exist from the initial data capture, but every letter is composed and information replaced by hand (copy/paste/save). This takes a person 1-2 days depending on the study area. A waste of time if you ask me.

Thanks for all of your help. Oh.... And I'm using the Office 2007 Suite if that makes a difference. Sorry the post is so long, I wanted to make sure to be thorough in my inquiry.

Sincerely,

jmwheatley

[RuralGuy]

It most certainly *is* a waste of time and can be achieved from within Access using VBA. That is about the most I can offer without knowing more about the details.

[jmwheatley]

Well if you are interested, I can certainly provide you with any details you might need?

Aside... I was hired for this company to improve their current GIS Program. I've only been with the company for a little over a month now and I realized very quickly that they do things manually, well because that is the way it's been done and no one has taken the time or interest in making it better. That is why I'm there. I am happy as I can be to be charged with this task.

I have very little VBA experience... Any specific starting points you might recommend? (books, online tuts, etc.)

[RuralGuy]

Albert Kallal's Super Easy Word Merge would be something to look at.

[RuralGuy]

Is the MDB a form driven system or are the tables simply completed by entering data directly into the table?

[jmwheatley]

Well actually at this point, they are just creating spreadsheets in excel. Each study different. Just depends on who is charged with getting the contact info. However, I feel this stuff needs to be standardized anyway. Especially if we are going to automate it. And I assumed this was better accomplished with Access than Excel anyway. If this works then there will be no problem moving to Access and making those charged with the task input this information into a form.

I was planning on setting up a form for the input of the information. Complete with validation rules/domains/etc.

I checked out the Easy Mail Merge option you posted above. Definitely cool. If I knew more, I could build off of it.

[RuralGuy]

Just so you know, it is a lot easier to slap something together in Excel and have it half way useful that it is to do the same in Access. Since Access is a real RDBMS it really prefers the data to be normalized in order to get it back out easily. The move from Excel to Access can be a little daunting at first because of poor habits developed in Excel. Are you familiar with Normalization? Here's some links if not:
http://www.accessmvp.com/strive4peac...Chapter_03.pdf
http://www.phlonx.com/resources/nf3/

[jmwheatley]

Thanks for that. It was a good refresher. I am familiar with normalization. This really is nothing more than a record set of contact information. Name, Address, City, State, Zip, Phone Number, etc.

That being said, would I be better off making this happen through an excel macro? The thing is, I'm pretty much starting off with a clean slate. There is no existing policy on this data capture, only to get full contact info. However I'm able to set it up effectively, will be the new way it's done. I just noticed this process during my workflow analysis and thought to myself, "this needs help" LOL

I appreciate the time you given me.