New with VBA

[Ogedozie]

I'm having challenges identifying the errors on the code. Although, I got this code from a video tutorial I watched and I was able to create almost the exact Excel spreadsheet but it doesn't tend to execute the task of replacing the details in a word doc with that on the table.
I don't mind rewriting/using a different code that will work accurately.
Your assistance will be very much appreciated.

Sub CreateWordDocuments()
Dim CustRow, CustCol, LastRow, TemplRow As Long
Dim DocLoc, TagName, TagValue, TemplName, FileName As String
Dim WordDoc, WordApp, OutApp, OutMail As Object
Dim WordContent As Word.Range
With Sheet3
 
  If .Range("Q2").Value = Empty Then
    MsgBox "Please select a correct template from the drop down list"
    .Range("E2").Select
    Exit Sub
  End If
    TemplRow = .Range("Q2").Value 'Set Template Row
    TemplName = .Range("E2").Value 'Set Template Name
    DocLoc = Sheet1.Range("E" & TemplRow).Value 'Word Document Filename
    
    'Open Word Template
    On Error Resume Next 'If Word is already running
    Set WordApp = GetObject("Word.Application")
    If Err.Number <> 0 Then
    'Launch a new instance of Word
    Err.Clear
    'On Error GoTo Error_Handler
    Set WordApp = CreateObject("Word.Application")
    WordApp.Visible = True 'Make the application visible to the user
    End If
    
    
    LastRow = .Range("A9999").End(xlUp).Row  'Determine Last Row in Table
        For CustRow = 5 To LastRow
                    Set WordDoc = WordApp.Documents.Open(FileName:=DocLoc, ReadOnly:=False) 'Open Template
                          For CustCol = 5 To 13 'Move Through 9 Columns
                                    TagName = .Cells(6, CustCol).Value 'Tag Name
                                    TagValue = .Cells(CustRow, CustCol).Value 'Tag Value
                                     With WordDoc.Content.Find
                                        .Text = TagName
                                        .Replacement.Text = TagValue
                                        .Wrap = wdFindContinue
                                        .Execute Replace:=wdReplaceAll 'Find & Replace all instances
                                     End With
                                Next CustCol
                        
                        If .Range("I3").Value = "PDF" Then
                                       FileName = ThisWorkbook.Path & "\" & .Range("E" & CustRow).Value & "_" & .Range("F" & CustRow).Value & ".pdf" 'Create full filename & Path with current workbook location, Last Name & First Name
                                       WordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF
                                       WordDoc.Close False
                                   Else: 'If Word
                                       FileName = ThisWorkbook.Path & "\" & .Range("D" & CustRow).Value & "_" & .Range("E" & CustRow).Value & ".docx"
                                       WordDoc.SaveAs FileName
                                   End If
    
                                    If .Range("I2").Value = "Email" Then
                                                  Set OutApp = CreateObject("Outlook.Application") 'Create Outlook Application
                                                  Set OutMail = OutApp.CreateItem(0) 'Create Email
                                                  With OutMail
                                                      .To = Sheet3.Range("P" & CustRow).Value
                                                      .Subject = "Hi, " & Sheet3.Range("E" & CustRow).Value
                                                      .Body = "Hello, " & Sheet3.Range("F" & CustRow).Value
                                                      .Attachments.Add FileName
                                                      .Display 'To send without Displaying change .Display to .Send
                                                  End With
                                    Else: 'Print Out
                                           WordDoc.PrintOut
                                           WordDoc.Close
                                    End If
                        Kill (FileName) 'Deletes the PDF or Word that was just created
            End If '3 Condition met
        Next CustRow
        WordApp.Quit
End With
End Sub

 

[Dan_W]

Hi - I suspect you should be able to identify the cause of the problem if you comment out the following line: (meaning you should add a ' (quote) at the beginning of the line)

On Error Resume Next

Where the code would ordinarily break and tell y ou the source of the error, this line of code tells Excel to ignore it. Once you've done that, it should be easier to see why it's not working as expected. The only other thing that jumps out at me is that the code is only capturing data in columns 5 to 13, and rows 5 onwards. If your data doesn't sit in that range, I suspect it'll be ignored. I don't know if that's at all relevant - I can't see the image you uploaded, its too small on my computer.

 

[Ogedozie]

Hi - I suspect you should be able to identify the cause of the problem if you comment out the following line: (meaning you should add a ' (quote) at the beginning of the line)

On Error Resume Next

Where the code would ordinarily break and tell y ou the source of the error, this line of code tells Excel to ignore it. Once you've done that, it should be easier to see why it's not working as expected. The only other thing that jumps out at me is that the code is only capturing data in columns 5 to 13, and rows 5 onwards. If your data doesn't sit in that range, I suspect it'll be ignored. I don't know if that's at all relevant - I can't see the image you uploaded, its too small on my computer.

Thank you Dan,
I've made the corrections and its working, though it seem not to replace all the targeted words in the Word Doc. What do i do?
Below is the image of the spread sheet.

 

[Dan_W]

Well, I think you're going to need to go through the code line by line, and work out where things are going wrong. If it's working for some of the targeted words but not others, then you'll need to work out why. I expect the that the problem is something in the following part of the code:

For CustCol = 5 To 13 'Move Through 9 Columns
      TagName = .Cells(6, CustCol).Value 'Tag Name
      TagValue = .Cells(CustRow, CustCol).Value 'Tag Value
      With WordDoc.Content.Find
            .Text = TagName
            .Replacement.Text = TagValue
            .Wrap = wdFindContinue
            .Execute Replace:=wdReplaceAll 'Find & Replace all instances
      End With
Next CustCol

Try and look for some guides on how to debug your code. This site is a great resource, and the blogger appears to have written some guidance about debugging. How to debug VBA? Debugging VBA in Excel - Analyst Cave I would suggest paying particular attention to the part about 'stepping through code'.