Macro to open Word and Start Mail Merge

  • Hi Guys


    I currently have an excel spreadsheet which populates some data into a Word Mail Merge Document.


    Does anybody know of a macro in which I could


    a) Open the specific Word document directly from excel
    and then
    b) Run the Mail Merge
    and then
    c) Print the results of the document


    I would like this all to happen with the running of the macro. It would save the user from looking for the word doc etc


    If anybody could help this would be great

  • Re: Macro to open Word and Start Mail Merge


    Well yes that is the option.


    I justed wanted to simplify it so it would work at the click of ONE button, rather than have to search for a word sheet, click the merge, click the print icon.

  • 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 :

    Code
    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


    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:


    Code
    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


    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.



    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:

    Code
    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:


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


    will generate an error. Should read like this:

    Code
    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:



    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.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!