Mail Merge, Matrixes, And Automation MAX points!!

This is not necessarily a C++ question although I would like to design the system in C++ if possible but I am getting a few headaches.

Take a Deep Breath you will need it.

I am developing a system so that I can implement a pre-delivery, delivery time letter. This is so the customer knows what time to be in i.e. Am/Pm when their delivery arrives, on a given date.

The current system involves sorting throguh a batch of physical letters. This is how it works

1. Finds the date of delivery on the standard format letter.
2. Find the corresponding day for that date.
3. Look up the postcode Prefix on the letter.

4. Search paper based Matrix to find the row of the corresponding Postcode
5. Read accross the column header which lists the days and find what time the delivery will be delivered i.e. Am/Pm/Both/Neither.
6. Then Highlight either Am/Pm on the letter.

What I wanted to do was semi automate this system were a program can:

1.When Mail merge is implemented save all letters to temp directory  
2.Fseek/Ftell To find the date of deliverySearch the day for corresponding date.
3.Have 5 txt files with matrix information Mon-Fri
4.Open relevant txt file with corresponding day
5.String Search matching postcode from the matrix txt files
6.Fseek AM or PM or Non
7.Store AM or PM as delivery time String
8. Open letter for amendment Fseek position for delivery time
9. Insert relevant time period i.e. Am/PM/Both/Neither
10. Close file

The fault I see with the automated system is that when a mail merge is done all the individual letters are saved under the same file name. Which adds confusion. If they were individual Fseek/Fteel would work perfectly because the letters are a standard format and you can set the offset to find you relevant information.

Secondly how would you be able to find the corresponding day for the date, if the date is in the form DD/MM/YY. It would have to be searched and stored as a string because file manipulation is used.

An alternative way of implementing the system.

The reason why i would have to fseek from the end of the letter is because at the start of the letter in *.doc their is a letter head which would have unknown results if read from the start.

Thanks for your time and pateince.

In theory this is possible but the above issues have put me back to square 1


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

My first suggestion is to avoid C++. Why? Because Office Automation (VisualBasic scripting for Office applications) should be able to do what you want. You can write a macro that runs right after the mail merge creates a letter and does exactly what you describe (finds the string with the given date, determines the day of the week (VB has a Date class though I might not have the right name for it here), looks things up in a great big matrix of information (think Excel spreadsheet) and prints the required letter). Hooking the moment of mail merge is the hardest part in my mind because I have never tried to do it and haven't read any of the documentation.

I urge this because the difficulty of reading .doc files is non-trivial, seeking by position is almost always too brittle (what about the boss getting a new secretary who uses 2 initials instead of 3 when she types the letters; modifying the template by that one character changes your whole program and being off by just 1 character, who is going to notice glancing at a stack that looks about right?). You should be searching for text values to replace; VB has reasonable support for regular expressions as well as access to the search and replace functions in the application.

Having said that and urged you (emphatically) to think about a higher-level approach using the tools that are already in place, I would be happy to help you with any particular problems you have with either approach. As I said I am not familiar with mail merge in Word (and I don't have Office installed at all so I can't really learn) but I know just enough VB to be dangerous.

Hope this helps and good luck, -bcl
Bazza1982Author Commented:
Hi Bcladd,

Firstly thankyou for your Reply.

I understand why you would urge me to use VB with office scripting tools although I am unaware of these. I touched on VB once about 4 years ago but my knowlefe is very cloudy. i.e. Dim num (Whatever that means)

First of all im unsure of any commands  to search for the date.
Would the date be searched as a string on the letter and will it support UK date forma, to extract the relevant day ie. Mon-Fri

Secondly how would you write a script to search an excel spreadsheet matrix and could i have one excel file with columns going Mon-Fri and rows being relevant postcodes.

Then I would need to copy the information in that particular cell copied and transfered to the letter which will be amended. So as it needs to replace a word. I presum you could search for  your & delivery & is & on and VB strcat equivalent it with that and the information in the cell.

I think my main problem with this is A. Startin, and B. Coding.

I will aquire a VB ASAP from the library any recommendations or WWW web link would be appreciated relevant to the above.

Thank you for your interest.
I  am at work so I don't have time to address your post in detail but:

   In Word (Word 2002 SP-2) you can open Tools | Macro | Visual Basic Editor
   In the editor select Help | Microsoft Visual Basic Help
   In the Answer Wizard, type "day of week"; this will bring back results including the Weekday function (can convert from a date to an integer representing the day of the week).

You can also look under
   Contents | Visual Basic Conceptual Topics | Working Across Applications - for an introduction to opening another Office application programatically (you can open the Excel spreadsheed from within your script so that you can extract the delivery schedule (using an Excel formula you could do it in a couple of operations: write the day of the week in a known cell and read value from a known cell where the second cell uses the data functions in Excel to extract a matching value from the table given the information in the written cell)).

I tend to learn the parts of VB I need by reading the documentation or going on line to MSDN (in the VB editor Help | MSDN on the Web).

Hope this helps, -bcl
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

I was surfing today, thinking a little bit about your problem and I found some tutorials on getting started with VB in Office. Some are a little bit stale but they contain some good pointers: - some help with visual basic in general. Some choice snippets include interacting with Excel (writing a spreadsheet:; loading a CSV file into Excel: and interacting with Word (spell checking programatically: - One of many pages at MSDN that discuss Office product automation. describes the BeforMerge event and how to hook it (there are several other events including AfterMerge and AfterRecordMerge that you could hook for your use). describes the concepts behind find (and find and replace) from VB inside an Office application.

Not all of these resources refer to the most current version of Office; I am pretty sure most of the interfaces remain unchanged. If not I am sure a search of MSDN would reveal the newer interfaces.

Hope this helps, -bcl

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bazza1982Author Commented:
Thanks BCL unfortunatley I wont be able to read these till the weekend but thanls for your help and interested it is most appreciated I may stay in touch to let you  know of my progress and bother you if thats ok


How goes the automation project? Did any of the literature help?
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: bcladd {http:#9690896}

Please leave any comments here within the next seven days.

EE Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.