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 at "dd MMM yyyy hh:mm AM/PM"}' (I had to replace the 'at' symbol because it would not allow me to post otherwise)

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