Results 1 to 4 of 4

Thread: Mail Merge using VBA in Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Mail Merge using VBA in Excel

    I am trying to simplify a process here at work, but have run into a problem that I can not identify. I run the below, and it will only output one line entry from the spreadsheet. i.e. If I have 10 lines on the sheet, I only get the first one. All tough each line is marked as "DONE" in column 11.
    I know this should be obvious, but I am having no luck at all identifying where the breakdown is.

    If someone could take a look, I would be very grateful.

    Dim bCreatedWordInstance As Boolean
    Dim objWord As Object
    Dim objMMMD As Object
    Dim SMName As String
    Dim cDir As String
    Dim r As Long
    Dim s As Long
    Dim ThisFileName As String

    lastrow = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
    r = 1
    For r = 1 To lastrow
    If Cells(r, 11).Value = "DONE" Then GoTo nextrow

    SMName = Sheets("Data").Cells(r, 2).Value
    ' Setup filenames
    Const WTempName = "ARReleaseMerge.docx" 'This is the Word Templates name, Change as req'd
    Dim NewFileName As String

    ' Setup directories
    cDir = ActiveWorkbook.Path + "" 'Change if appropriate
    ThisFileName = ThisWorkbook.Name
    On Error Resume Next
    ' Create a Word Application instance
    bCreatedWordInstance = False
    Set objWord = GetObject(, "Word.Application")
    If objWord Is Nothing Then
    Err.Clear
    Set objWord = CreateObject("Word.Application")
    bCreatedWordInstance = True
    End If
    If objWord Is Nothing Then
    MsgBox "Could not start Word"
    Err.Clear
    On Error GoTo 0
    Exit Sub
    End If

    ' Let Word trap the errors
    On Error GoTo 0
    ' Set to True if you want to see the Word Doc flash past during construction
    objWord.Visible = False
    'Open Word Template
    Set objMMMD = objWord.Documents.Open(cDir + WTempName)
    objMMMD.Activate
    'Merge the data
    With objMMMD
    .MailMerge.OpenDataSource Name:=cDir + ThisFileName, sqlstatement:="SELECT * FROM `Data$`" ' Set this as required
    With objMMMD.MailMerge 'With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
    .FirstRecord = r - 1
    .LastRecord = r - 1
    .ActiveRecord = r - 1

    End With
    .Execute Pause:=False
    End With
    End With
    ' Save new file
    NewFileName = SMName & " - Release Letter " & ".docx" 'This is the New Word Documents File Name, Change as req'd"
    objWord.ActiveDocument.SaveAs cDir + NewFileName
    ' Close the Mail Merge Main Document
    objMMMD.Close savechanges:=wdDoNotSaveChanges
    Set objMMMD = Nothing
    ' Close the New Mail Merged Document
    If bCreatedWordInstance Then
    objWord.Quit
    End If
    For s = 1 To lastrow
    Cells(s, 11).Value = "DONE"
    nextrow:
    Next s
    Next
    End Sub

  2. #2
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,272
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Mail Merge using VBA in Excel

    For some alternative mailmerge code see Run a Mailmerge from Excel, Sending the Output to Individual Files in the Mailmerge Tips & Tricks thread at: http://www.msofficeforums.com/mail-m...ps-tricks.html

    PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code has lost whatever structure it had.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mail Merge using VBA in Excel

    Thank you for the link, but I have perused it several times already, and still don't have an answer to my issue.

    As to the # thing for code, sorry...was unaware, I will try it again and see what happens. I just can't figure out why it only outputs the first doc and not all. THe code does update all lines in the spreadsheet, so I know it is running through the loop. I have traced it many times, and just can't figure out where it is going awry. THE only thing I can think of has to do with the network path. With a network stored account, I believe it might be losing connection with the network path to the folder. Since this folder is local, but still runs through the network security device, it is hard to tell.
    If you can't see any code reason why it is failing, I will have to start looking at the network thing and see how I can run and end round.
    Any help would be appreciated.

    Code:
    Dim bCreatedWordInstance As Boolean
    Dim objWord As Object
    Dim objMMMD As Object
    Dim SMName As String
    Dim cDir As String
    Dim r As Long
    Dim s As Long
    Dim ThisFileName As String
    
    lastrow = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
    r = 1
    For r = 1 To lastrow
    If Cells(r, 11).Value = "DONE" Then GoTo nextrow
    
    SMName = Sheets("Data").Cells(r, 2).Value
    ' Setup filenames
    Const WTempName = "ARReleaseMerge.docx" 'Word Template name
    Dim NewFileName As String
    
    ' Setup directories
    cDir = ActiveWorkbook.Path + "" 
    ThisFileName = ThisWorkbook.Name
    On Error Resume Next
    ' Create a Word Application instance
    bCreatedWordInstance = False
    Set objWord = GetObject(, "Word.Application")
    If objWord Is Nothing Then
    Err.Clear
    Set objWord = CreateObject("Word.Application")
    bCreatedWordInstance = True
    End If
    If objWord Is Nothing Then
    MsgBox "Could not start Word"
    Err.Clear
    On Error GoTo 0
    Exit Sub
    End If
    
    ' Let Word trap the errors
    On Error GoTo 0
    
    objWord.Visible = False
    'Open Word Template
    Set objMMMD = objWord.Documents.Open(cDir + WTempName)
    objMMMD.Activate
    'Merge the data
    With objMMMD
    .MailMerge.OpenDataSource Name:=cDir + ThisFileName, sqlstatement:="SELECT * FROM `Data$`" 
    With objMMMD.MailMerge 'With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
    .FirstRecord = r - 1
    .LastRecord = r - 1
    .ActiveRecord = r - 1
    
    End With
    .Execute Pause:=False
    End With
    End With
    ' Save new file
    NewFileName = SMName & " - Release Letter " & ".docx" 'This is the New Word Documents File Name
    objWord.ActiveDocument.SaveAs cDir + NewFileName
    ' Close the Mail Merge Main Document
    objMMMD.Close savechanges:=wdDoNotSaveChanges
    Set objMMMD = Nothing
    ' Close the New Mail Merged Document
    If bCreatedWordInstance Then
    objWord.Quit
    End If
    For s = 1 To lastrow
    Cells(s, 11).Value = "DONE"
    nextrow:
    Next s
    Next
    End Sub
    

  4. #4
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,272
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Mail Merge using VBA in Excel

    You really should look more closely at the code in the link; it would only require a few changes to merge all your records and to exclude records that already have the 'DONE' parameter set. Your own code - which still lacks a proper structure - potentially repeatedly creates new Word instances, then destroys them, which is a huge drain on performance.
    Cheers
    Paul Edstein
    [MS MVP - Word]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •