VBA mailmerge all but last row in datasource

[vincenzo345]

Hello,

Im trying to perform a mail merge through VBA. A problem that I encouter is that my datasource has a CRLF (carriage return line feed) in the last row of the datasource. This causes a mailmerge error log to pop up and say "Record X Was an Empty Record". Ultimately im trying to get this separate sheet to either go away or only show up when there is a real error on a record. Im having difficulty finding a solution on skipping or even deleting the last row of the datasource. Ive tried messing with

ActiveDocument.MailMerge.DataSource.RecordCount

but I get a result of -1 which makes me feel like its kinda useless to me.

Here is what I have so far. Any help on how I can merge all but the last row or even delete the last row is appreciated.

 
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:="FILEPATH", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, SubType:=wdMergeSubTypeOther
 
      With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With

[macropod]

You could apply a filter to the SQL (which you haven't included in your code) to exclude empty records.

[vincenzo345]

Thanks for that Idea, I think it'll work but im kind of clueless in regards to how that sql qry works.

 
 
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:="C:\DATA\" & Environ("USERNAME") & "\Word.txt", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, sqlstatement:="SELECT * FROM activedocument.MailMerge.DataSource WHERE ActiveDocument.MailMerge.DataSource.DataFields(2).Value IS NOT NULL", SubType:=wdMergeSubTypeOther
 
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

The datasource file is word.txt. I wasnt able to find how to reference the table and field for the sql qry.In regards to the Qry I had written, I was expecting it to qry word.txt and process all rows where field 2 is not null. Any idea on what im doing wrong?

[macropod]

To get the SQL query string, open up the document and connect it to the data source for a mailmerge if that hasn't already been done. Having done that, go to Mailings|Edit Recipient List>Filter and apply the requisite filter to the problematic field. The following macro will then tell you, in turn, what are the: Data Source Name; Connect String; and Query String. It's the last of these you need to incorporate into your code - be careful with the direction of any quote characters. You could, of course, output any of these to the end of the document so you can cut & paste any parts you need into your code.

Sub Test()
With ActiveDocument.MailMerge
  If .MainDocumentType <> wdNotAMergeDocument Then
    MsgBox "Mail Merge Data Source Name:" & vbCr & .DataSource.Name
    MsgBox "Mail Merge Connect String:" & vbCr & .DataSource.ConnectString
    MsgBox "Mail Merge Query String:" & vbCr & .DataSource.QueryString
  Else
    MsgBox "Not A Merge Document"
  End If
End With
End Sub

[vincenzo345]

You've been a great help but im still having issues with this.

 
Dim doc As String
 
doc = "C:\MERGE\" & Environ("USERNAME") & "\word.txt"
 
  ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:=doc, _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, sqlstatement:="SELECT * FROM doc WHERE [M_2] IS NOT NULL ", SubType:=wdMergeSubTypeOther
 
      With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With

I was able to identify the field name that im looking for as being M_2.

If I use SELECT * FROM doc WHERE M_2 IS NOT NULL then the file will still merge but It doesnt remove the empty row.

If I use SELECT * FROM doc WHERE [M_2] IS NOT NULL then I get a "Run-Time Error 5922. Word was unable to open the data source."

Im not sure what im doing wrong. I also tried using 'M_2' instead of [M_2].


any ideas on what im messing up here?

[macropod]

What did the SQL code returned by the macro I posted contain?

[vincenzo345]

c:\MERGE\user\word.txt

Connection String returned nothing.

SELECT * FROM c:\MERGE\user\word.txt

[macropod]

It'll have far more than that! It'll start with:
SELECT * FROM

[vincenzo345]

Originally Posted by macropod

It'll have far more than that! It'll start with:
SELECT * FROM

I think I was editing that last post just as you were writing this

Here are the three results that popped up.

c:\MERGE\user\word.txt

The Connection String returned nothing.

SELECT * FROM c:\MERGE\user\word.txt

[macropod]

Presumably, it was actually:
SELECT * FROM c:\MERGE\user\word.txt WHERE [M_2] IS NOT NULL

Your VBA code should thus contain:
SQLStatement:="SELECT * FROM " & doc & "WHERE [M_2] IS NOT NULL"