Mail Merge?


Board Regular
Sep 20, 2006
Hello All,

What I am trying to do is simular to a mail merge. I want to pull data from a excel sheet and push the data to a word document. For each record create a new word document. I have it working with a excel to excel document but I am not sure to to push the data from excel to word and then save the word for each record. (if this is making any sense)

Here is the code I started with:

Sub Create_SteFiles()
Dim SiteName, SaveAsName As String
Dim SiteID, LAT, LON, StreetAddress, City, State, ZIPCode As String
Dim CounterRange, MaxValueRange As Integer
MaxValueRange = 69
For CounterRange = 2 To MaxValueRange
Workbooks.Open Filename:="C:\Site Survey Form.xls"
'****Clears out all the variable Data for the next Cycle *****
SiteName = ""
SiteID = ""
LAT = ""
LON = ""
StreetAddress = ""
City = ""
State = ""
ZIPCode = ""
SaveAsName = ""
TransferString = ""
Windows("HI Market Tracker.xls").Activate
'************Pull Data From the spreadsheet Here ******
SiteName = Range("A" + Trim(Str(CounterRange))).Value
SiteID = Range("B" + Trim(Str(CounterRange))).Value
LAT = Range("F" + Trim(Str(CounterRange))).Value
LON = Range("G" + Trim(Str(CounterRange))).Value
StreetAddress = Range("K" + Trim(Str(CounterRange))).Value
City = Range("L" + Trim(Str(CounterRange))).Value
State = Range("M" + Trim(Str(CounterRange))).Value
ZIPCode = Range("N" + Trim(Str(CounterRange))).Value
Windows("Site Survey Form.xls").Activate
'************Push Data to the spreadsheet Here ******
Range("F10").Value = Trim(SiteName)
Range("C10").Value = Trim(SiteID)
Range("F13").Value = Trim(LAT)
Range("H13").Value = Trim(LON)
Range("C12").Value = Trim(StreetAddress)
Range("H12").Value = Trim(City)
Range("J12").Value = Trim(State)
Range("C13").Value = Trim(ZIPCode)
SaveAsName = "C:\_LC_Sites\" + SiteID + ".xls"
ActiveWorkbook.SaveAs Filename:=SaveAsName
'Workbooks.Open Filename:=SaveAsName
'Windows(Sitename + ".xls").Activate
'Range("C8").Value = Sitename
Next CounterRange
'ActiveWorkbook.SaveAs Filename:=SaveAsName
End Sub

Any Ideas


Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hello Kenneth,

I have been trying to get this to work and I keep getting "out of subscript" Here is the code...what is wrong?

Private Sub CommandButton2_Click()
Dim wdApp As Object, wd As Object, ac As Long, ws As Worksheet
Set ws = Worksheets("HI Market Tracker.xls")
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wd = wdApp.Documents.Open("C:\Final Mile Site Survey.doc")
wdApp.Visible = True
With wd
.formfields("Text39").Result = ws.Range("A2").Value
.formfields("SiteID").Result = ws.Range("B2").Value
.formfields("Text14").Result = ws.Range("C2").Value
.formfields("Text15").Result = ws.Range("D2").Value
.formfields("Text33").Result = ws.Range("E2").Value
.formfields("Text34").Result = ws.Range("F2").Value
.formfields("Text35").Result = ws.Range("G2").Value
.formfields("Text13").Result = ws.Range("H2").Value

End With
Set wd = Nothing
Set wdApp = Nothing
End Sub
Upvote 0
You did not set the worksheet object reference properly.
Set ws = Worksheets("Set ws = Worksheets("HI Market Tracker.xls")
Should go something like:
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set ws = Workbooks("HI Market Tracker.xls").Worksheets("YourSheetNameHere")
Last edited:
Upvote 0
Hey...thanks alot!

that worked!! Now I need it so loop through each row. I will try at the end of each loop save the document...close it and loop again until it is finished.

still alittle green behind the ears with this...
If you have any suggestions I am all hears....

thanks again
Upvote 0
If you want to create multiple DOC files, I guess that would be fine. You might use FileCopy() to copy the master DOC.

The easier method is to create one DOC using the traditional mail merge in the DOC's vba from Word. Of course we can do the mail merge from Excel. It just takes a bit more work to set the Word references.
Upvote 0

Forum statistics

Latest member
Home Nest

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back