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.

Sub merge()
    
    Set objword = Nothing
    Set odoc = Nothing
    Set Odoc2 = Nothing
    Set objword = CreateObject("Word.Application")
    objword.DisplayAlerts = True
    Set odoc = objword.Documents.Open(Range("A37").Value)
    objword.Visible = True
    'Odoc.Application.Visible = True
    odoc.MailMerge.Destination = wdsendtonewdocument
    odoc.MailMerge.Execute
    Set Odoc2 = odoc.Application.Documents("Catalog1")
    odoc.Close False
    mypath = "e:\Integrity12\Workorders\" & Format(Range("A26"), "ddd dd-mmm-yy")
    If Len(Dir(mypath, vbDirectory)) = 0 Then MkDir mypath
    Odoc2.SaveAs mypath & "\" & (Range("A36").Value & ".doc")
    objword.DisplayAlerts = True
    objword.Application.Quit True
    
    AppActivate "Microsoft Excel"
    
End Sub

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

Dim fName as string
fName = Sheets("varhold").Range("A37").Text
Set odoc = objword.Documents.Open (fName)

or

Set odoc = objword.Documents.Open (Sheets("varhold").Range("A37").Text)

**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.

Private Sub tb1_cue_dia_Click()
    tb1_cue_dia.Value = True
    If varhold.Range("I16") > 0 Then
        varhold.Range("A36") = "\DT\v5\DT-CUE5.dot"
        Call merge
    End If
    varhold.Range("A36") = "DR\v5\DR-CUE5.dot"
    Call merge
End Sub

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

Option Explicit

    Dim objword As Object
    Dim odoc As Object
    Dim odoc2 As Object
    Const wdSendToNewDocument = 0
    Dim mypath As String

Sub merge()
    
    Dim fName As String
    Set objword = CreateObject("Word.Application")
    objword.DisplayAlerts = True
    fName = Sheets("varhold").Range("A37").Text  ' A37 = CONCATENATE("E:\Integrity12\Reports\",A36) = E:\Integrity12\Reports\DR\v5\DR-CUL5.dot
    Set odoc = objword.Documents.Open(fName) 'hangs here. Even if the user approves SQL request, code will hang as per original post
    objword.Visible = True
    'Odoc.Application.Visible = True
    odoc.MailMerge.Destination = wdSendToNewDocument
    odoc.MailMerge.Execute
    Set odoc2 = odoc.Application.Documents("Catalog1")
    odoc.Close False
    mypath = "e:\Integrity12\Workorders\" & Format(Range("A26"), "ddd dd-mmm-yy")
    If Len(Dir(mypath, vbDirectory)) = 0 Then MkDir mypath
    odoc2.SaveAs mypath & "\" & (Range("A36").Value & ".doc")
    objword.DisplayAlerts = True
    objword.Application.Quit True
    AppActivate "Microsoft Excel"
    
    Set objword = Nothing
    Set odoc = Nothing
    Set odoc2 = Nothing
End Sub

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

Sub merge()

    Dim fName As String
    Set objword = CreateObject("Word.Application")
    objword.DisplayAlerts = True
    fName = Sheets("varhold").Range("A37").Text  ' A37 = CONCATENATE("E:\Integrity12\Reports\",A36) = E:\Integrity12\Reports\DR\v5\DR-CUL5.dot
    


    Debug.Print fName  '<------ Add this line of code and put a break point here
    


    Set odoc = objword.Documents.Open(fName)    'hangs here. Even if the user approves SQL request, code will hang as per original post
    objword.Visible = True
    'Odoc.Application.Visible = True
    odoc.MailMerge.Destination = wdSendToNewDocument
    odoc.MailMerge.Execute
    Set odoc2 = odoc.Application.Documents("Catalog1")
    odoc.Close False
    mypath = "e:\Integrity12\Workorders\" & Format(Range("A26"), "ddd dd-mmm-yy")
    If Len(Dir(mypath, vbDirectory)) = 0 Then MkDir mypath
    odoc2.SaveAs mypath & "\" & (Range("A36").Value & ".doc")
    objword.DisplayAlerts = True
    objword.Application.Quit True
    AppActivate "Microsoft Excel"

    Set objword = Nothing
    Set odoc = Nothing
    Set odoc2 = Nothing
End Sub

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.