Mail Merge Macro to produce multiple documents

[mcghue]

Good day everyone. I am new to the site but have been playing around with excel/VBA etc for a while but have recently had an issue that for the life of me I cant resolve. I am trying to produce a document using a macro from excel to a template file using mail merge. All works fine until I have more than 1 line on my spreadsheet. I am using XP and office 2003.

Basically I want to create a document for each line on the spreadsheet. Below is the macro

Sub RunMailMergeRN()

Dim wdOutputName, wdInputName As String
FName = Sheets("Sheet1").Range("A2").Text
FName2 = Sheets("Sheet1").Range("B2").Text
wdOutputName = ThisWorkbook.Path & "\Updated\RN 0" & FName & " - MIN - " & FName2 & ".doc"

wdInputName = ThisWorkbook.Path & "\RN Blank Templatemail.doc"

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

With wdDoc.MailMerge
.MainDocumentType = wdFormLetters
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.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