Mail Merge Automation Problem

**Jenn68** · 05-21-2012, 09:16 PM

I am using this code in my Excel VBA application to execute a mail merge routine in MS Word.

Please Login or Register  to view this content.

The application hangs at the line in red. I am not able to manipulate anything in Excel at this point, it does not react. Microsoft Word is not visible, yet it is there in Task Manager. I have to close Word via task Manager to regain control.

The value in worksheets("varhold").range("A37") is E:\Integrity12\Reports\DR\DR_CUL2.dot

Any ideas as to what I am doing wrong?

**jaslake** · 05-21-2012, 10:28 PM

Hi Jenn68
You might try

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

**Jenn68** · 06-03-2012, 01:45 PM

Hi John,

I appreciate your reply. Thank you. My apologies for taking so long to reply back, I've been pre-occupied getting DDE tyo work with my reports before I try to automate things.

I implemented your suggestions, but I'm unsure if things are working. Once the original problem line is encountered, I'm not entirely sure what is going on. Seems the computer becoames somewhat unresponsive. Eventually, I can start minimizing messages.

I see two Messages. I'm uncertain as to order in which they are appearing. There is Microsoft Word's confirmation to run the mail merge SQL command. It's waiting for a user selection. I guess the question is, how do I overcome the need for user confirmation?

The second message box, is from Excel ... telling me that Microsoft Office Excel is waiting for another application to complete an OLE action. I'm assuming that's referring to Word's user input.

Cheers all. Looking for some help.

**jaslake** · 06-03-2012, 03:35 PM

Hi Jenn68
I've done several Mail Merge projects with success. If you'd like to attach your files (both the Excel file and the Word file and including code) and an explanation of what you're attempting to do, I'll be glad to look at it with you.

**Jenn68** · 06-03-2012, 04:43 PM

Hi jaslake ... thank you for your kind offer.

The mail merge works fine. If I manually call up the mail document it populates nicely and produces a final document no problems.

Its the launching of the .dot document from my Excel application which is causing me the grief.

From a user form, the user selects the report they wish to have printed by clicking a button.

Please Login or Register  to view this content.

Once it is know which report the user wants printed, the Word lauch macro is started ...

Please Login or Register  to view this content.

I'm going to have to edit the the main application (Excel) file as it's quite a big application. The mailmerge data is a separate workbook, needing some editting of confidential information.
I'll see what I can do.

Jenn

**jaslake** · 06-03-2012, 07:19 PM

Hi Jenn

Go into VBA (Alt + F11) or however you do it. Select View ----> Immediate Window

Then go into your code Sub merge and add this line of code

Please Login or Register  to view this content.

Call your code as you normally do...step past the Break Point (F8)...look at the Immediate Window...what does it say...take a screen capture...upload the image.

Post the files...it'll be much more intuitive.

**Jenn68** · 06-03-2012, 08:17 PM

Hi jaslake ... thanks for your continued support and patience.

As requested, here is a screeny ...

vba_capture.jpg

I'm working on the three files for submission.

**Jenn68** · 06-03-2012, 08:46 PM

Some files .... dispatch.xls is the main application and user interface. From worksheet "frontpage", select Step 3. Enter the date in the form that pops up ... 6/14/12. (You will get an error here ... a "Variable not defined" ... but I have no idea what it's referring to. (Understand, I have removed lots of worksheets, forms and modules redundant to this operation. "Step 3" should run without referencing any of these missing components.) 41064(04-June-12).xls

The code then searches a directory to see if a data file exists for this date. The file exists, and is included ... 41064(04-June-12).xls. This file is the data source for the mail merge. dispatch.xls

A the form expands providing the user with a selection of report printing option based on the data in the dataset. Only those buttons enabled have a records to report. I did not include all 18 merge documents, just one. Chose the button in frame CUPE, subframe late, button "diamonds". It's report is attached as DR-CUL5.dot. (renamed to DR-CUL5.doc so that I could upload it) DR-CUL5.doc

Hope this helps a bit.

**Jenn68** · 06-05-2012, 09:16 AM

Me thinks that this problem may largely be due to the mail merge's apparent dislike of having two open Excel workbooks. Workbook "dispatch.xls" is the workbook the mail merge documents are launched via Word. "MergeData.xls" holds the data of the mail merge.

Anytime I have to interrupt whatever is going on, the focus is always on the "Dispatch.xls" sheet. Could the DDE feature of the merge be so limited to only the "first" workbook and the "first " worksheet of that workbook. I noticed this when I was working with the mail merge documents independent of "dispatch.xls". Everything worked fine. However, if I opened up another unrelated Excel workbook, I lost the functionality of the mail merge docs I had been working with successfully prior.

I can't very well close "dispatch.xls", as it's the core of my application. "mergedata.xls" is critical ... it's the food for the merge. The DDE named data range is 'CONTROL_1!$A:$EH' I know from experience, that having the reference to 'CONTROL_1' seems irrelevant. With or without it, it insists on using whatever the first worksheet in workbook 'mergedata.xls', regardless of it's content. This caused me a lot of grief until I had to implement a line of code to move the worksheet to position 1 in the workbook.

**jaslake** · 06-05-2012, 02:48 PM

Hi Jenn

I don't think it's an issue of having two or more Excel files open...I've got things working...I need to clean up the code to accommodate your File Tree Structure. I'll get back to you.

By the way, this issue

You will get an error here ... a "Variable not defined" ... but I have no idea what it's referring to

is caused by this line of code in Sub chkdate_Click

Please Login or Register  to view this content.

It's looking for a UserForm that appears not to exist.

I'm going to be traveling Thursday through Sunday (my Son's receiving his Masters Degree on Friday...Hallelujah!!! ) ...hopefully I can post something for you to play with tonight or tomorrow.

**Jenn68** · 06-05-2012, 09:57 PM

Hi John, I know you're doing this out of the goodness of your heart, so no expectations or demands. I will take what you can offer, when you can offer it.

I hope it's not a big complicated problem. I'm a novice and the thoughts of rewriting reams of code scares me. I'm glad you're optimistic about the problem not being the two workbooks being open. I think that may have been a tough one to conquer.

**Jenn68** · 06-18-2012, 06:48 PM

Still no success after some additional tinkering around.

Mail Merge Automation Problem

LinkBack

Thread Tools

Rate This Thread

Display

Mail Merge Automation Problem

Re: Mail Merge Automation Problem

Re: Mail Merge Automation Problem

Re: Mail Merge Automation Problem

Re: Mail Merge Automation Problem

Re: Mail Merge Automation Problem

Re: Mail Merge Automation Problem

Re: Mail Merge Automation Problem

Re: Mail Merge Automation Problem

Re: Mail Merge Automation Problem

Re: Mail Merge Automation Problem

Re: Mail Merge Automation Problem

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions