Skip MS Word Mail Merge if datasource has no records

Question

i currently have 6 mail merge templates that i execute via the following vbs.

this opens each file in the root folder and runs the mailmerge,

VBS

 Set fs = CreateObject("Scripting.FileSystemObject")
    Set rootFolder = fs.GetFolder(fs.GetParentFolderName(wscript.ScriptFullName))
    Set oWord = Createobject("Word.Application")
        oWord.Visible = False

    For Each file in rootFolder.Files
       If LCase(fs.GetExtensionName(file.Name)) = "docx" Then
        Set oDocument = oWord.Documents.Open(file.path)
            oWord.Run "regular_mail"
            oDocument.Close(False)
        Set oDocument = Nothing
       End If
    Next
    oWord.Quit
    set oWord = nothing

the vba inside word, does the mailmerge puts it in the designated folder, what i get is an error when the datasource for that file has no data. since StrName = .DataFields("pk") wont have any values.

Where im stuck is how to go around that error, or check whether the data source is blank then move on to the next template.

each template should save to one file so my mailroom can print.

VBA in word:

Sub regular_mail()
Dim sDate As String, StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long, fso As Object, StrMonthPath As String, StrDayPath As String, StrFileName As String

sDate = Format(Now(), "mmddyy")
Const StrFolderName As String = "C:\Test\Files\"
Set fso = CreateObject("Scripting.FileSystemObject")

    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True

        With .DataSource
            .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i

            StrName = .DataFields("pk")
            StrMonthPath = .DataFields("month_path")
            StrDayPath = .DataFields("day_path")
            StrSendDate = .DataFields("send_date")
            StrFileName = sDate & "_" & fso.GetBaseName(ActiveDocument.Name)
        End With
        .Execute Pause:=False



'Creates directory if it doesnt exist

    If Not fso.FolderExists(StrFolderName & StrMonthPath) Then
        fso.CreateFolder (StrFolderName & StrMonthPath)
    End If

    If Not fso.FolderExists(StrFolderName & StrMonthPath & StrDayPath) Then
        fso.CreateFolder (StrFolderName & StrMonthPath & StrDayPath)
    End If


    If Not fso.FolderExists(StrFolderName & StrMonthPath & StrDayPath & "letters\") Then
        fso.CreateFolder (StrFolderName & StrMonthPath & StrDayPath & "letters\")
    End If
    End With



    ActiveDocument.SaveAs2 FileName:=StrFolderName & StrMonthPath & StrDayPath & "letters\" & StrFileName, FileFormat:=16, AddToRecentFiles:=False

    ActiveWindow.Close
End Sub

any and all help is appreciated, thank you in advance.

score 0 · Answer 1 · answered Feb 09 '19 at 08:18

0

Without being able to test it myself right now, you could use both or one of these approaches:

You could check it DataSource is nothing (or if is not Nothing, as you need):

If .DataSource Is Nothing Then ...

You could check if there are records in the Datasource:

If .DataSource.RecordCount = 0 Then

answered Feb 09 '19 at 08:18

AHeyne

3,377
2
11
16

just erroring out. I think im just working to close to it and looking at it for too long im sure the answer is simple. – lookslikeanevo Feb 09 '19 at 22:12

Skip MS Word Mail Merge if datasource has no records

1 Answers1