Mail merge not working correctly and skip if

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
143
Office Version
  1. 2010
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 ..

VBA Code:
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
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
143
Office Version
  1. 2010
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
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,557
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,205
Messages
5,640,835
Members
417,171
Latest member
Mr___D

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top