Change Word mailmerge source with VBA?

[Gasman]

Hi all,
In this project I wish to be able to create a mailmerge to an A4 label format, however I am trying to make it easier for the controller to print the data.


The source workbook is going to have a sheet for each letter of the alphabet. Columns would be the same on each sheet.
I want to be able to ask the controller(user) which sheet he wishes to load to the document, just using a simple InputBox.


I was considering taking each sheet's data and creating an All sheet, referring to that in the word document and then get him to filter by using another column with the first character of the surname, but I think this would be easier for him, as he is not that computer literate. He would not be printing regularly, just initially and then after a few changes to that particular alpha set.


So I was thinking of an input box to ask what letter and then use that to change the mailmerge source of the word document, rather than him having to direct word to a new sheet each time?
Then all he has to do is Print.


Would anyone be able to direct me to some code that I could modify to do the above please?


TIA

[Gasman]

I have managed to cobble together this much from various searches.
Most of this from https://stackoverflow.com/questions/62006117/how-to-opendatasource-for-word-mailmerge-from-excel-worksheet
However I am not using a DB, just want to use an Excel worksheet.


Option Explicit
Sub Contract(Wordfile As String, strSheetName As String)
Dim wdApp As Object, wdDoc As Object
Dim StrMMSrc As String, strSQL As String


StrMMSrc = "F:\Temp\Address Trial,xlsm" 'ActiveWorkbook.FullName
Wordfile = "F:\Users\Paul\Documents\Address Details 7165 MM.docm"
strSheetName = "Sheet1"
strSQL = "SELECT * FROM " & strSheetName & ""


If Dir(Wordfile) = "" Then
MsgBox "Cannot find:" & vbCr & Wordfile, vbExclamation
Exit Sub
End If
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If wdApp Is Nothing Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo Err_Handler


With wdApp
.Visible = True
.WordBasic.DisableAutoMacros
.DisplayAlerts = 0 ' wdAlertsNone
Set wdDoc = .Documents.Open(Wordfile)
With wdDoc
With .MailMerge
.MainDocumentType = wdFormLetters
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
"Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
SQLStatement:=strSQL, SubType:=wdMergeSubTypeAccess
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
.Close SaveChanges:=False
End With
End With


Err_Handler:
Debug.Print Err.Number & " - " & Err.Description
MsgBox Err.Number & " - " & Err.Description

Set wdDoc = Nothing
Set wdApp = Nothing

End Sub


but get the attached screen when trying to set the source?