Debugging Help needed!

Smilechild793

New Member
Joined
Apr 27, 2017
Messages
20
I've followed the following Excel Tutorial from Youtube: How To Create Word Documents From Excel WITHOUT Mail Merge by Excel for Freelancers.

I've attempted to use my own TEST Data (screenshot below):

ama7a1.png



However, when I attempt to Print or Email it doesn't work. I got the below error but have no idea how to fix.

9

jig6px.png



Here is the line of code that should pertain to this:

jl2q7l.png
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
What does the error say? and can you copy and paste the full code in the thread in code tags (Copy the code, paste it in the thread, select all the code and click the # icon at the top of the window).

The reason I ask for the code is to start with the variables aren't declared correctly, I also suspect that your error message is also referring to the variables.
 
Upvote 0
Thanks for the response!! Please see error and code below:

2iql2d1.png


Code:
Sub CreateWordDocuments()Dim CustRow, CustCol, LastRow, TemplRow, DaysSince, FrDays, ToDays As Long
Dim DocLoc, TagName, TagValue, TemplName, FileName As String
Dim CurDt, LastAppDt As Date
Dim WordDoc, WordApp, OutApp, OutMail As Object
Dim WordContent As Word.Range
With Sheet1
  
  If .Range("B3").Value = Empty Then
    MsgBox "Please select a correct template from the drop down list"
    .Range("G3").Select
    Exit Sub
  End If
    TemplRow = .Range("B3").Value 'Set Template Row
    TemplName = .Range("G3").Value 'Set Template Name
    'FrDays = .Range("L3").Value 'Set From Days
    'ToDays = .Range("N3").Value 'Set To Days
    DocLoc = Sheet2.Range("F" & TemplRow).Value 'Word Document Filename
    
    'Open Doc Template if not already open
    On Error Resume Next 'If Word is already running
    Set WordApp = GetObject("Word.Application")
    If Err.Number <> 0 Then
    'Launches 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("E9999").End(xlUp).Row  'Determines Last Row in Table
        For CustRow = 8 To LastRow
                'DaysSince = .Range("M" & CustRow).Value
                If TemplName <> .Range("N" & CustRow).Value Then 'And DaysSince >= FrDays And DaysSince <= ToDays Then
                                Set WordDoc = WordApp.Documents.Open(FileName:=DocLoc, ReadOnly:=False) 'Opens Template
                                For CustCol = 5 To 25 'Move Through Columns
                                    TagName = .Cells(7, CustCol).Value 'Tag Name (First mail merge cell)
                                    TagValue = .Cells(CustRow, CustCol).Value 'Tag Value
                                     With WordDoc.Content.Find 'Look for merge field in doc
                                        .Text = TagName 'what you're looking for
                                        .Replacement.Text = TagValue 'what you're going to replace with
                                        .Wrap = wdFindContinue
                                        .Execute Replace:=wdReplaceAll 'Actually replaces - idea:Find & Replace all instances
                                     End With
                                Next CustCol
                            'Now, what are we going to do wit merged doc? Is it going to be Word or PDF?
                        If .Range("I3").Value = "PDF" Then
                                       FileName = ThisWorkbook.Path & "\" & .Range("E" & CustRow).Value & "_" & .Range("F" & CustRow).Value & ".pdf" 'Creates full filename & Path with current workbook location (Last Name & First Name)
                                       WordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF 'takes pdf and prints
                                       WordDoc.Close False
                                   Else: 'If Word
                                       FileName = ThisWorkbook.Path & "\" & .Range("E" & CustRow).Value & "_" & .Range("F" & CustRow).Value & ".docx"
                                       WordDoc.SaveAs FileName 'template won't be touched, saves as new file
                                   End If
                                   '.Range("N" & CustRow).Value = TemplName 'Template Name
                                   '.Range("O" & CustRow).Value = Now
                                    If .Range("P3").Value = "Email" Then
                                                  Set OutApp = CreateObject("Outlook.Application") 'Creates Outlook Application
                                                  Set OutMail = OutApp.CreateItem(0) 'Creates Email
                                                  With OutMail
                                                      .To = Sheet1.Range("K" & CustRow).Value 'K is the column with email listed
                                                      .Subject = "Hi, " & Sheet1.Range("I" & CustRow).Value & " We Miss You"
                                                      .Body = "Hello, " & Sheet1.Range("I" & CustRow).Value & " Its been a while since we have seen you so we wanted to send you a special letter. Please see the attached file"
                                                      .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
 
Upvote 0
I 'll post later updated code to correct the variables, as for your error go to Tools - References, scroll down and check that Microsoft Word ??.? Object Library (where ??.? is your version number) is checked.
 
Upvote 0
I 'll post later updated code to correct the variables, as for your error go to Tools - References, scroll down and check that Microsoft Word ??.? Object Library (where ??.? is your version number) is checked.

Sorry also check that Microsoft Office ??.? Object Library is checked.
 
Upvote 0
You can also try removing the "As Word.Range" from Dim WordContent As Word.Range (especially as I can't see where they are using WordContent in the code).
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top