I have the following code working EXCEPT it keeps asking me for the sheet name. I want it to be fully automated. It is using objects from the Microsoft Word 11.0 Object Library.
What is it doing you might ask...
Opening a word document and word app
Making the word doc a merge docment (it already has merge fields in it)
Opens the Excel spreadsheet datasource
Merges the document into a new doc
saves the new doc
closes the new doc
closes the merge doc
I would like to accomplish all of this WITHOUT prompts, but it keeps asking for the table name no matter what I have tried.
Dim wordDoc As Word.Document
Dim wordApp As Word.Application
Set wordDoc = GetObject("fullpathandmerge.doc", "word.document")
Set wordApp = wordDoc.Application
.Visible = True
wordDoc.MailMerge.MainDocumentType = wdFormLetters
wordDoc.MailMerge.SuppressBlankLines = True
wordDoc.MailMerge.DataSource.FirstRecord = wdDefaultFirstRecord
wordDoc.MailMerge.DataSource.LastRecord = wdDefaultLastRecord
wordDoc.MailMerge.Destination = wdSendToNewDocument
.ActiveDocument.SaveAs "fullpathandfile.doc", wdFormatDocument, False