Last Row of Data for Mail Merge

Question

I am currently working with VBA in excel. I have an excel database that is really large, and many different people use it. Thus, we input our data at the bottom of the spreadsheet. I have created a button in the excel file to automatically merge the file with Word to create a form for the user to print. However, since our newest data is at the bottom of excel, there are some extra steps you have to go through once in word to choose that piece of data (i.e. complete merge --> edit individual documents --> select range of documents From 8000 to 8000). So, I am looking for a VBA code to add to my current button that will choose the last set of data automatically and merge it into word. Does anyone know how to do this? We are constantly adding data points so it can't be a set row number.

Current code I am using:

    Sub RunMailMerge()

Dim wdOutputName, wdInputName As String
wdOutputName = ThisWorkbook.Path & "\WHAT database CURRENT " & Format(Date, "d mmm yyyy")
wdInputName = ThisWorkbook.Path & "\Recreated Quote Form.docx"

' open the mail merge layout file
Dim wdDoc As Object
Set wdDoc = GetObject(wdInputName, "Word.Document")
wdDoc.Application.Visible = True

With wdocSource.mailmerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
        .First Record = wdDefaultFirstRecord
    End With
    .Execute Pause:=False
End With

' show and save output file
wdDoc.Application.Visible = True
wdDoc.Application.ActiveDocument.SaveAs wdOutputName

' cleanup
wdDoc.Close SaveChanges:=False
Set wdDoc = Nothing

End Sub

P.S.

This code works, but it says there is a pop up stating there is a problem with With wdocSource.mailmerge ((run time error 424 Object Required)) can anyone help me with this as well? Please keep in mind I am good with computers, but new to VBA!

to get last row you may see [this](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) — Siddharth Rout, Jul 03 '15 at 04:47

score 0 · Answer 1 · answered Jul 03 '15 at 10:13

Here my approach for you.

Declare a constant for your last row as follow :

Const wdDefaultLastRecord = 10 '(last record)

And Set it as follow :

With .DataSource

    .First Record = wdDefaultFirstRecord

    .LastRecord = wdDefaultLastRecord

End With

Last Row of Data for Mail Merge

1 Answers1