VBA Mail-merge Date format inconsistencies

[lmorse]

I have a spreadsheet that I am using to mail-merge with a Word template document. The mail merge is triggered by VBA code. I have some dates which come out correctly in the mail-merge and others that show incorrectly.

The Word-doc merge field is formatted thus: {MERGEFIELD DUE@"dd MMM yyyy hh:mm AM/PM"}

The Excel cell has the following date: "01/07/2021 10:58:00 AM" and has a custom format of "dd mmmm yyyy hh:mm AM/PM" so that it shows as "01 July 2021 10:58 AM"

However, when merged this date comes out as: "07 January 2021 10:58 AM"

We are using the UK date format. The date\time on the PC is set as: Short Date: 24/06/21

The VBA code that triggers the mail merge is:

Set wdDoc = wdApp.Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
    With wdDoc
      With .MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
          LinkToSource:=False, Connection:="Provider=Microsoft.ACE.DDE.12.0;User ID=Admin;" & _
          "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
          SQLStatement:="SELECT * FROM `FTR$`"
        For i = 1 To .DataSource.RecordCount
          .Destination = wdSendToNewDocument
          .SuppressBlankLines = True
          With .DataSource

..... etc

Can anybody give me some guidance on how I can make this mail-merge more reliable?

Thank you

Lee