Mail merge not working correctly and skip if

[Mike2502]

Hi All,

In a current situation where I run a mail merge, is there any way to 'skip record' if Column A is duplicate? or what are my options around with this?

So if Column A equals 'ABC123' skip the second row

Code below ..

Sub Mail_Merge2()

Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long
Const StrNoChr As String = """*./\:?|"
Dim MyDate
Dim Month

MyDate = Format(Date, "yyyymmdd")
Month = Format(Date, "mmmm")


    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Users\now\file_.xls" _
        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, _
        WritePasswordDocument:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\now\file_.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB" _
        , SQLStatement:="SELECT * FROM `ToDo_2$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
        
Set MainDoc = ActiveDocument
With MainDoc
  StrFolder = "C:\Users\now\" & Month & "\"
  With .MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    On Error Resume Next
    For i = 1 To .DataSource.RecordCount
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("ID")) = "" Then Exit For
        'StrFolder = .DataFields("Folder") & "\"
        StrName = MyDate & " - " & .DataFields("File_Name")
      End With
      .Execute Pause:=False
      If Err.Number = 5631 Then
        Err.Clear
        GoTo NextRecord
      End If
      For j = 1 To Len(StrNoChr)
        StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With ActiveDocument
        '.SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
NextRecord:
    Next i
  End With
End With
Application.ScreenUpdating = True
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
End Sub

 

[Mike2502]

Hi again,

Just had a thought - like Excel VBA could we use If file exists then skip if not continue merge?

Sorry for double reply needed urgent! How would one follow through on above

 

[Mike2502]

Bump

 

[Macropod]

The simplest way is to add a column to your data source that tests for duplicates and add a 'where' test to your SQL code to exclude those records.

 

[Peter_SSs]

Sorry for double reply needed urgent!

Bump

Regarding urgency, I suggest that you have a look at #12 of the Forum Rules and 5g & 5h of the Guidelines