• Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.

    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

VBA Mail-merge Date format inconsistencies


New Coder
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 [email protected]"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