Many to one mail merge from Word Macro is sending just one email RRS feed

  • Question

  • Hi 

    I have this below code to do many to one email merge, ( I have many rows of data to combine to one email) hence using this below macro code, however, it is just sending one email, it is not hitting the "Next I" statement, or any statement after ".Execute Pause:=False", does not even enter the error handler "oops"

    It works fine on my home computer, but in my work environment it sends only one email

    checked Datasource.Recordcount = 90

    I changed the statement ".Execute Pause:=True" but it is not helping much 

    Any help is much appreciated Thanks Ira

    Sub SendEmail()
    ' SendEmail Macro
    '{DATABASE \d "{FILENAME \p}/../library.xlsx" \s "SELECT
    '[Title], [Barcode], [Due Date] FROM [Sheet1$] where [Id] = {MERGEFIELD Id} GROUP BY [Title], [Barcode], [Due Date]" \I "15" \b "49" \h}
        Application.ScreenUpdating = False
            Dim MainDoc As Document, StrID As String, i As Long
            Set MainDoc = ActiveDocument
            With MainDoc
              For i = 1 To .MailMerge.DataSource.RecordCount
                With .MailMerge
                  .Destination = wdSendToEmail
                  .SuppressBlankLines = True
                  With .DataSource
                    .FirstRecord = i
                    .LastRecord = i
                     On Error GoTo oops
                      .ActiveRecord = i
                  End With
                  If .DataSource.DataFields("Id") <> StrID Then
                    StrID = .DataSource.DataFields("Id")
                     .MailAddressFieldName = "Email"
                     '.MailSubject = "Library Overdue items"
                     '.MailFormat = wdMailFormatHTML
                     .Execute Pause:=False
                  End If
                End With
              Next i
            End With
            Application.ScreenUpdating = True
           Application.ScreenUpdating = True
        Exit Sub
    End Sub

    • Edited by Nalina Tuesday, October 8, 2019 4:52 AM
    Tuesday, October 8, 2019 4:52 AM

All replies

  • Have you checked the value of .MailMerge.DataSource.RecordCount (using MsgBox, for example)?

    Also make sure that If correctly filters the data. It is better to use breakpoints and the Debugger in step-by-step mode to investigate the data, variables and behaviour of your program.

    • Edited by Viorel_MVP Tuesday, October 8, 2019 6:49 AM
    Tuesday, October 8, 2019 6:48 AM
  • Hi Viorel

    Yes checked the recordcount it is showing 91 records. 

    I have already stepped through each statement in debug mode, it is not going to any statement after .Execute Pause:=False, it just sends one email and exits the code on this line, 

    I tried the same on my home computer works fine.  

    Tuesday, October 8, 2019 10:00 PM