Label Mailmerge To Word - Too Many Labels/blank Records

Hi!

I'm REALLY new to this programming stuff, so please bear with me.
I've created the following code to perform a mailmerge to word from my excel spreadsheet, but when I run the code I am having a couple problems, and I have no idea where to start looking to correct them. I set up the Mailmerge Main Document as a Word Template with the Mergefields already filled in. The data is coming from the first sheet in my excel workbook which has the headers for each column in Row A, and the headers match the word Mergefields I entered in my word template. When I run the following code, even though there are currently only 60 or so rows of "records" the result in word is 34 pages of my 5160 Avery Labels. After it runs out of the actual records it creates 32 pages of blank labels which appear to be printing only the current date and appear to be pulling the data from blank cells in my "ActivityDate" column of my datasource.

Also, everytime I run the macro, I get a message saying that "a table in the document has become corrupted"

Can someone please tell me where in my code I've gone wrong or what I'm missing?

I'd also like to figure out how to only select certain records from my datasource to be merged, but I suspect that might have something to do with a query or some similar function that might be better addressed in another sub-forum (however any tips on where i might look or post would also be greatly appreciated)

Sub MaiMerge()
    Dim oApp As Word.Application
    Dim oMainDoc As Word.Document
    Dim wb As Excel.Workbook
    Dim sDBPath As String
    Dim sTPath As String


    Set wb = ActiveWorkbook
    sDBPath = wb.Path & "\CalendarStickers.xls"
    sTPath = wb.Path & "\CalendarStickers.dot"
      
'Create a new Word Session
  Set oApp = CreateObject("Word.Application")


'Start a new main document for the mail merge.
    Set oMainDoc = oApp.Documents.Add(sTPath)
    
    With oMainDoc.MailMerge
    
        .MainDocumentType = wdMailingLabels
    
        'Set up the mail merge data source to CalendarStickers.xls.
        sDBPath = wb.Path & "\CalendarStickers.xls"
        .OpenDataSource Name:=sDBPath, _
        SQLStatement:="SELECT * FROM `Dates$`"
    End With
    
    'Perform the mail merge to a new document.
    With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.SuppressBlankLines = True
        With .MailMerge.DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .MailMerge.Execute Pause:=False
    End With
 
    'Activate word and display document
    With oApp
      .Visible = True
      .ActiveWindow.WindowState = 0
      .Activate
    End With
    
End Sub

Re: Label Mailmerge To Word - Too Many Labels/blank Records

Thanks for the reply!

Unfortunately I've already tried that. And I've checked many of the cells individually...they actually are blank...no formatting, no formulas, nothing. Any other thoughts? Could it have something to do with how I'm calling the mailmerge in my code?

Re: Label Mailmerge To Word - Too Many Labels/blank Records

Unfortunately, when i tried the manual macro-record method, the part where I selected which records I wanted, didn't appear in the macro...I saw a couple posts from others noting this problem as well, which appears to be something in the Mail merge wizard which prevents the macro from recording the code for the selection of records/recipients.

Is there any way, i can use the same VBA statement and tell it to select all the records until there's a blank row? (although it's currently not recognizing my blank rows....grrrr)

Re: Label Mailmerge To Word - Too Many Labels/blank Records

sure....assuming i can figure out how to attach stuff....it won't let me b/c it says both file sizes exceed the allowable amount....any ideas how I can shrink them without messing up the data?

Re: Label Mailmerge To Word - Too Many Labels/blank Records

Hopefully, they're attached in Zipform...There should be 2 files in there one word template and one excel workbook both named calendarstickers...let me know if I did it wrong

Re: Label Mailmerge To Word - Too Many Labels/blank Records

What did you set (maybe the proper word is define?) the data range as though? I need to be able to continue adding rows without having to reset any ranges....will the named range allow me to continue to add?

I had tried the longer string of code with all the settings and ended up with continual error messages when I tried to run through the code...for some reason, it didn't like my code eventhough i had literally cut and paste it from the code generated by the mailmerge macro I had recorded. Again, I'm so new, I didn't know where to look to figure out what i had done wrong...i just tried to adapt someone elses suggestion which seemed to work a little better.