VBA Mail Merge Date - Issue: Swapping Month and Day

Question

0

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

I have aslo tried the following variations in the Word Template:
{MERGEFIELD DUE\@ "dd MMM yyyy hh:mm AM/PM"}
{MERGEFIELD DUE \@ "dd MMM yyyy hh:mm AM/PM"}
{MERGEFIELD DUE \@ dd MMM yyyy hh:mm AM/PM}
Can anybody give me some guidance on how I can make this mail-merge more reliable?

UPDATE: I have now tried several different date formats in both the spreadsheet and in the Word Template. Always with the same results.

I also added the following code to format the cell values:

Value = Format(DueDate, "dd MMMM yyyy hh:mm AM/PM")
Thank you

Lee