CODEWITHSUNDEEP
excelvbams-word
fireFly
fireFly

Reputation: 21

VBA to send Word Mail Merge

When attempting to run this code I get an object required error. I am not sure how to resolve this.

I am trying to run vba from excel to run mail merge in word. 

Sub MailMerge()
    Set wordapp = CreateObject("word.Application")
    wordapp.documents.Open "C:\Users\me\Desktop\hello.docx", ReadOnly:=True
    wordapp.Visible = True


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


End Sub

Upvotes: 1

Views: 1731

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25663

Since this is running in Excel VBA the code needs to specifically reference what belongs to Word. That means that this line:

With ActiveDocument.MailMerge

Needs to be 

With wordApp.ActiveDocument.MailMerge

Since Excel knows nothing about ActiveDocument. Even better, however, would be to assign an object to the Document when it's opened, rather than rely on ActiveDocument:

Dim wordDoc as Object
Set wordDoc = wordapp.documents.Open("C:\Users\me\Desktop\hello.docx", ReadOnly:=True)
With wordDoc.MailMerge

Once you have that working, you'll get additional error messages for all the enumeration values starting with wd. These, too, belong to Word VBA. Since you're using Object instead of Word.Application for wordapp the code cannot use the wd enumerations - VBA can't look them up. Using Word.Application would mean needing to set a Reference to the Word object model in Tools/References. This can make it difficult if the macro needs to be used by other people with a different (older) version of Office, which is why many code samples for running Word from Excel use Object.

If you want to use Object then you need to replace all the wd enumeration values with their numerical equivalent. wdSendToEmail for example has the value 2, so 

.Destination = 2

I leave it to you to look up the other values, which is simple enough. Open Word's VBA editor, press F2 to open the object browser, then search each wd value. The numeric equivalent will be at the very bottom of the window when you click on a value.

enter image description here

Upvotes: 1

Related Questions