Macro to open Word and Start Mail Merge

Re: Macro to open Word and Start Mail Merge

Hi Peter,

Why not go into the word doc and mail merge it?

John

Re: Macro to open Word and Start Mail Merge

Peter,

First, to be able to control Word from Excel you need to add the MS Word Object Library Reference in the VB editor.

1) Open the excel file and Visual Basic Editor
2) in the VB Editor, go to Tools - References
3) find and check the Microsoft Word 11.0 Object Library (or something close)

Quote

a) Open the specific Word document directly from excel

Here is some code to do this:
[vba]
Set appWD = CreateObject("Word.Application")
appWD.Visible = True

appWD.Documents.Open Filename:="C:/My Documents/myDoc.doc"
[/vba]

Quote

b) Run the Mail Merge

Assuming the data source has already been set, you need to do something like this:
[vba]
appWd.MailMerge.OpenDataSource Name:="C:\My Documents\mydata.csv" _
, ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="", SQLStatement:="", SQLStatement1:=""
[/vba]

Quote

c) Print the results of the document

Try using something like this. It might bring up the Print dialog, but it'll point you in the right direction.

[vba]
appWD.ActiveDocument.PrintOut
[/vba]

Hope this helps!

Ezra Bowman

Re: Macro to open Word and Start Mail Merge

One more point re. Ezra's suggestion, I think you could set the ".Destination = wdSendToPrinter" property on the MailMerge object and the ".Execute" it to send to the printer automatically, as in :

appWd.MailMerge.Destination = wdSendToPrinter
appWd.MailMerge.Execute

I use code very similar to Ezra's suggestion and it works fine.

Except (in my case) ...

If the data source is the same excel workbook that runs the macro Word 2000 reports that it can't open the Datasource.

In Excel/Word 2002 and Excel/Word 2003, this works fine. In Excel/Word 2000, Word reports that it cannot open the datasource. I am using ODBC to connect to the datasource, having tried DDE already without success in Excel 2000.

Any suggestions gratefully received.

Note: I have used 'Revert:=False', and the merge does work fine if the datasource is closed at the time of the mailmerge.

Re: Macro to open Word and Start Mail Merge

Hi, I have the same query - how do I set the data source as per ender's post?

Re: Macro to open Word and Start Mail Merge

Just a question along very simialar lines to the above.

Although I can get the macro to run, when word opens/connects to the data file, it takes nearly 1 minute to do its thing. As you need to do this twice [at open then connect to the proper file] the user gets frustrated by having to wait.

If you just open / connect through word directly, instead of through VBA in excel, the document opens / connects in about 4 - 5 seconds.

Is there a reason for this, or any way to spead word up?

Thanks in advance,
Simon

Re: Macro to open Word and Start Mail Merge

I have been trying to use mail merge without success. I used the code provided in this thread (with different directory paths for my particular files) but I keep getting the same error:

"Run-time error 438:
Object doesn't support this property or method."

Can anyone help me figure out why?

I have posted the the excel data file and the word template.
Please help.

Re: Macro to open Word and Start Mail Merge

Peter,

First you need to check that the Word object model is referenced in Excel.
You do this in Tools - References as per

1) Open the excel file and Visual Basic Editor
2) in the VB Editor, go to Tools - References
3) find and check the Microsoft Word 11.0 Object Library (or something close)

Second, your code will cause errors to occur.

When connecting to word, you need to error trap in case word is not open, or is all ready open. Thirdly, it is good practice to dimension all objects and variables before you use them.

Finally, I think your error has two sources:
1) Have not referenced the Word Object Model
2) You have to Mail Merge a Document, Not the application as in your code.

try

Dim appWd as Word.Application
Dim WdDoc as Word.Document

On Error resume next
Set appWd = Getobject (,"Word.Application")
if appWd = nothing then
Set appWd = Getobject("","Word.Application")
end if
on error goto 0

with appWd
.visible = True
set WdDoc = .documents.open Filename:="C:/30DAYLTR - Template1.doc"
WdDoc.Mailmerge ........
end with
set WdDoc = Nothing
set appWd = Nothing

Hope that helps
Wedgetail

Re: Macro to open Word and Start Mail Merge

Wedgetail -

Apparently, my code doesn't like the following syntax:

set WdDoc = .documents.open Filename:="C:/30DAYLTR - Template1.doc"

I have tried it a few different ways but it doesn't like it. Can you help with this?

Re: Macro to open Word and Start Mail Merge

CapnAmazing

If you have set

with appWd

above the syntax should be correct.
The with command keeps the base at that level so you do not need to continually type it.

Also, have you dimensioned appWD and WdDoc?
Dim appWd as Word.Application
Dim WdDoc as Word.Document

Otherwise, the only other thing you need to check is that the references are set correctly as described earlier.

Regards
Wedgatail

Re: Macro to open Word and Start Mail Merge

Hi,

i am very interested in the solution for your question about the mail merge directed from excel.

i have a list of names and scores and would like to mail merge it to word without leaving excel

(i try to control it by using a userform in excel)

is there anywhere an example of such a excel macro or VBA code

Re: Macro to open Word and Start Mail Merge

Hi MaxDV,

The information above should be sufficient for you to create the macro.
I do not know of any complete examples on the net.

Just try the above code, and if you have any troubles, post a reply and I will try to assist.

Cheers
Wedgetail

Re: Macro to open Word and Start Mail Merge

thanks,

i will try and let you know :thanx:

Re: Macro to open Word and Start Mail Merge

Hi Everyone

I need some help with the problem of running a mailmerge from excel as you've described above. The Vba code runs a mailmerge of data stored in an excel called "mailmerge.xls" fine. However, When it does run it always asks me to chose which sheet I want to mailmerge which is a bit annoying when i want the process to be fully automatic.

ActiveDocument.Mailmerge.MainDocumentType = wdFormLetters 
ActiveDocument.Mailmerge.OpenDataSource 
Name:="H:\Mailmerge.xls" 
 
With ActiveDocument.Mailmerge 
    .Destination = wdSendToNewDocument 
    .SuppressBlankLines = True 
    With .DataSource 
        .FirstRecord = wdDefaultFirstRecord 
        .LastRecord = wdDefaultLastRecord 
    End With 
    .Execute Pause:=False 
End With

What line of code do i need to add to make the macro automatically mailmerge "sheet1" of mailmerge.xls?

Thanks in advance for your help!

James

Re: Macro to open Word and Start Mail Merge

I am not a programmer.

I created a work around to this issue, creating a regular hyperlink to the document in one field, say 'A43' that linked to the .doc file.

Then I recorded a macro that clicked on that link and got this as a result:

Range("A43").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Then I created a shape (button) over some text with a no-fill, no line format, and assigned the macro to it. That way I can incorporate the link into whatever macro I wish.

For me, this brings up the document, which already has the SQL Mail Merge source statement saved into it, so it requests to perform the sql statement each time I open the file.

This feature is exactly how I want it to be.

Simple.

Re: Macro to open Word and Start Mail Merge

I know I am a little late to the game here, but for my fellow users, I wanted to Note for those still reading this post: Wedgetail is right:

Set WdDoc = .documents.open Filename:="C:/30DAYLTR - Template1.doc"

will generate an error. Should read like this:

Set wdDoc = .Documents.Open(Filename:="C:/30DAYLTR - Template1.doc")

with that minor change, code works fine. And after using it, this is by far the simplest code I have created in months!

Happy coding!

Re: Macro to open Word and Start Mail Merge

I am trying to do the same thing but i keep getting a debug box. I have my mail merge already set up ready to go with the merge fields. Can someone look at the info below and help me out?

Sub Print_Tags()
Application.ScreenUpdating = True
Application.DisplayAlerts = False
Set appWd = CreateObject("Word.Application")
appWd.Visible = True

appWd.Documents.Open Filename:="C:\Users\J.Templet\Desktop\LOTO Print Tags\PRINTING TAGS1.docx"
appWd.MailMerge.OpenDataSource Name:="C:\Users\J.Templet\Desktop\LOTO Print Tags\Isolation LOTO Template.xlsx" _
, ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="", SQLStatement:="", SQLStatement1:=""

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
'
End Sub

Re: Macro to open Word and Start Mail Merge

jtemp59: You may need to open a new post for this, so don't be surprised if you get dinged by the moderator.

I am by no means an expert, so, for what it is worth:
A) Make sure that you have set your reference to the Microsoft Word XX.XX Object Library (pick the highest number available)
C) Set up the Word merge file, then just tell Excel to run the merge. Always works best for me.
B) From what I can see, you are not declaring your word document correctly. The application handles the word "window", but it does not control what happens inside the document.

Set up the application - then set up the document. But don't to use the application where you should be using the document property. Something like this:

'	Setting up the Word application    Dim wdApp As Word.Application
    
On Error Resume Next
    
'   open the mail merge layout file
    Dim wdDoc As Word.Document  '<== this is what I don't see in your code.
    
'   Error handling




    Set wdApp = GetObject(, "word.application")
    If wdApp Is Nothing Then
    Set wdApp = GetObject("C:YourWordDocumentObject.docx", "word.application") ' <== Just tells Excel where to go to find the file if the merge file is not open on your desktop
    End If
    
    On Error GoTo 0
    
' Now you start opening the Word application and the document
    With wdApp
    
'   Set wdDoc = .Documents.Open
    
    Set wdDoc = .Documents.Open(Filename:="C:YourWordDocToOpen.docx") '<==this opens the document so you can manipulate it.
    wdDoc.Application.Visible = True




'	Just telling word how to run the merge




    With wdDoc.MailMerge
         .MainDocumentType = wdFormLetters
         .Destination = wdSendToNewDocument
         .SuppressBlankLines = True
         .Execute Pause:=False
    
    End With




    wdDoc.Application.Visible = True
    
'   Sends to the default printer once created
     wdDoc.PrintOut




'   housekeeping




    wdDoc.Close SaveChanges:=False
    
    Set wdDoc = Nothing




End With

Hope that at least gives you some ideas to trouble shoot the problem. If not - open a post, and be clear what type of debug you are getting.

Re: Macro to open Word and Start Mail Merge

Quote from Shoo;169245

Hi, I have the same query - how do I set the data source as per ender's post?

This is what I used. I simply recorded a macro of me setting the Data Source.

Sub OpenDataSource()
'
' OpenDataSource Macro
'
'
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\FILENAME.XLSM", ConfirmConversions:=False _
        , ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\FILENAME.XLSM;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Lo" _
        , SQLStatement:="SELECT * FROM `'SHEETNAME$'`", SQLStatement1:="", _
        SubType:=wdMergeSubTypeAccess
End Sub