Mail Merge, Matrixes, And Automation MAX points!!

Posted on 2003-11-03
Last Modified: 2010-04-01
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


Question by:Bazza1982
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 11

Expert Comment

ID: 9675518
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

Author Comment

ID: 9680023
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.
LVL 11

Expert Comment

ID: 9680426
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

LVL 11

Accepted Solution

bcladd earned 500 total points
ID: 9690896
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

Author Comment

ID: 9717178
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


LVL 11

Expert Comment

ID: 9891983
How goes the automation project? Did any of the literature help?

Expert Comment

ID: 10242486
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

  Included as part of the C++ Standard Template Library (STL) is a collection of generic containers. Each of these containers serves a different purpose and has different pros and cons. It is often difficult to decide which container to use and …
IntroductionThis article is the second in a three part article series on the Visual Studio 2008 Debugger.  It provides tips in setting and using breakpoints. If not familiar with this debugger, you can find a basic introduction in the EE article loc…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question