Excel to Excel "mail merge" using VBA

[CuddlyGoldfish]

Every month we run a standard mailmerge to send a number of emails. After the emails are sent, we create an excel workbook which relates to each of the emails sent. I would like to automate this process rather than manually creating 200 workbooks! I would like to copy all of the data in the relevant row in the datasource to a specific row in the new workbook. The workbook used needs to be a specific workbook rather than just a new workbook, and would need to save as with a name based on the value in one cell in the row.

I already have a piece of VBA which takes one row of data from the datasource, opens a specific, unchanging, workbook, copies the data into a row, saves and closes the second workbook, then goes back to the original workbook. What I'm struggling to work out is how to modify that code to cycle through multiple rows, and how to give each workbook it creates its own unique name. The VBA I have is below, for what it's worth. Any help you guys can provide will be very much appreciated!

' DataExport Macro

Dim ExportFilename As String
Dim ImportFilename As String
Dim DataArray As Variant

'Save the name of the current workbook (assumes you run the macro when the export workbook is your "current window")
ExportFilename = ThisWorkbook.Name

'Create a reference to the import file (only need to change this bit if you change the name of the import file)
ImportFilename = "H:\\Administration\Upload Data.xlsm"

'copy data to be exported into an temporary array
Sheets("Upload export").Visible = True
Sheets("Upload export").Select
DataArray = Range("A3:BZ3").Value

'Open Import workbook
Workbooks.Open Filename:=ImportFilename

'Select Data Import sheet and insert new row between rows 1 and 2
Sheets("Data").Select
Range("3:3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

'Transfer data from array into import spreadsheet
Range("A3").Select
If UBound(DataArray, 1) > UBound(DataArray, 2) Then
For i = 1 To UBound(DataArray, 1)
ActiveCell.Offset(0, i - 1).Value = DataArray(i, 1)
Next
Else
For i = 1 To UBound(DataArray, 2)
ActiveCell.Offset(0, i - 1).Value = DataArray(1, i)
Next
End If


'Save and close import file
ActiveWorkbook.Save
ActiveWorkbook.Close

'Go back to the original workbook
Workbooks(ExportFilename).Activate
Sheets("Upload export").Visible = False
Sheets("Data Input").Select