Object variable or With block variable not set

[aziz rasul]

I have inherited the following code: -

Public Sub MergeLetter(TemplateTitle)

    Dim DBName As String, TemplateLocation As String
    Dim DatabaseName As String, DatabaseLocation As String
    Dim TemplateName As String, UserName As String, QrySource As String
    Dim Response As String, SQL1 As String, TableTitle1 As String
    Dim WordObj As New Word.Application
    Dim NewDoc As Word.Document
    
    On Error GoTo ErrorHandler
    
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    
    DatabaseName = "EP_Scheduling_Aug 2012.accdb"
    DatabaseLocation = "\\fas3040-sata\wccmydocs$\hrlr155\My Documents\Databases\"
    DBName = DatabaseLocation & DatabaseName
    TemplateLocation = "L:\PerfAndDev\HR\HRJobEval\Templates\"
    TemplateName = TemplateLocation & TemplateTitle
    
    Debug.Print DBName
    Debug.Print TemplateName

    QrySource = "8_AutomateMailMerge"
    
    DoCmd.SetWarnings False
    SQL1 = "SELECT 8_AutomateMailMerge.* INTO " & "tbl_TempTable IN '" & DBName & "' FROM 8_AutomateMailMerge"
    DoCmd.RunSQL (SQL1)
    DoCmd.SetWarnings True
    
    Call DeleteField("tbl_TempTable", "SigningDate")
    Call DeleteField("tbl_TempTable", "SigningTime")
    
    Set WordObj = CreateObject("Word.Application")
    Set NewDoc = WordObj.Documents.Add(Template:=TemplateName, NewTemplate:=False)
    
    With WordObj
         .Visible = True
         [COLOR="Red"]With NewDoc.MailMerge[/COLOR]             
             .MainDocumentType = wdFormLetters
             TableTitle1 = "`" & "tbl_TempTable" & "`"
             .OpenDataSource Name:=DBName, Connection:="DSN=MS ACCESS DATABASES;" & DBName & ";FIL=REDISAM;", SQLStatement:="Select * from " & TableTitle1, SQLStatement1:="", SubType:=wdMergeSubTypeAccess
             With .DataSource
                 .FirstRecord = wdDefaultFirstRecord
                 .LastRecord = wdDefaultLastRecord
             End With
             .Execute Pause:=True
         End With
            .Windows(1).Activate
            .ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
            .Application.WindowState = wdWindowStateMaximize
    End With
  
ErrorHandler:
    If Err.Number > 0 Then
        MsgBox Err.Number & " - " & Err.Description
    End If
  
End Sub

which fails on the line in red. The debug error 91 is 'Object variable or With block variable not set '. Any ideas why this error is happenning?

 

[boblarson]

First off you need to change this line:

Public Sub MergeLetter(TemplateTitle)

to this

Public Sub MergeLetter(TemplateTitle As String)

Then you need to make sure you are actually passing in the name of the file, including the extension.

 

[aziz rasul]

Changed to As String.

The MS Word template file name being passed is

L:\PerfAndDev\HR\HRJobEval\Templates\COT3Agreement.dotx

ran the code, but still errored.

 

[boblarson]

Does it still throw the error if you revise to:

    With WordObj
         .Visible = True
         [COLOR=red]With NewDoc[/COLOR]             
             [COLOR=#ff0000].MailMerge[/COLOR].MainDocumentType = wdFormLetters
             TableTitle1 = "`" & "tbl_TempTable" & "`"
             [COLOR=#ff0000].MailMerge[/COLOR].OpenDataSource Name:=DBName, Connection:="DSN=MS ACCESS DATABASES;" & DBName & ";FIL=REDISAM;", SQLStatement:="Select * from " & TableTitle1, SQLStatement1:="", SubType:=wdMergeSubTypeAccess
             With .DataSource

 

[aziz rasul]

It failed the same way on line

.MailMerge.MainDocumentType = wdFormLetters

 

[boblarson]

It failed the same way on line

.MailMerge.MainDocumentType = wdFormLetters

That would tend to indicate to me that you need to declare and instantiate a MailMerge object.

 

[aziz rasul]

I have changed the code to

Public Sub MergeLetter(TemplateTitle As String)

	Dim strQuerySource As String
	Dim strTableName As String
	Dim ObjWord As Word.Application
	Dim objDoc As Word.Document
	Dim myMerge As Word.MailMerge
	Dim strActiveControl As String
	
	On Error GoTo ErrorHandler
	
	strActiveControl = Screen.ActiveControl.Name
	
	DoCmd.GoToControl "Pay No"
	DoCmd.GoToControl strActiveControl

	strQuerySource = "8_AutomateMailMerge"
	
	DoCmd.SetWarnings False
	DoCmd.RunSQL "SELECT " & strQuerySource & ".* INTO " & "tbl_TempTable IN '" & CurrentDb.Name & "' FROM " & strQuerySource
	DoCmd.SetWarnings True

	Set ObjWord = New Word.Application
	
	Call TimeDelay(3)
	
	Set objDoc = ObjWord.Documents.Open(TemplateTitle)
	Set myMerge = objDoc.MailMerge
	
	With ObjWord
		 .Visible = True
		 With myMerge
			 .MainDocumentType = wdFormLetters
			 strTableName = "'" & "tbl_TempTable" & "'"
			 .OpenDataSource Name:=CurrentDb.Name, Connection:="DSN=MS ACCESS DATABASES;" & CurrentDb.Name & ";FIL=REDISAM;", SQLStatement:="SELECT " & strTableName & ".* FROM " & strTableName, SQLStatement1:="", SubType:=wdMergeSubTypeAccess
			 With .DataSource
				 .FirstRecord = wdDefaultFirstRecord
				 .LastRecord = wdDefaultLastRecord
			 End With
			 .Execute Pause:=True
		 End With
			.Windows(1).Activate
			.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
			.Application.WindowState = wdWindowStateMaximize
			.Activate
	End With
	
	Set myMerge = Nothing
	Set ObjWord = Nothing
	Set objDoc = Nothing
  
ErrorHandler:
	If Err.Number > 0 Then
		MsgBox Err.Number & " - " & Err.Description
	End If
  
End Sub

However it now fails further down the code i.e.

.OpenDataSource Name:=CurrentDb.Name, Connection:="DSN=MS ACCESS DATABASES;" & CurrentDb.Name & ";FIL=REDISAM;", SQLStatement:="SELECT " & strTableName & ".* FROM " & strTableName, SQLStatement1:="", SubType:=wdMergeSubTypeAccess

with error 5922 'Word was unable to open the data source'.

The argument TemplateTitle is now "L:\PerfAndDev\HR\HRJobEval\Templates\COT3Agreement.dotx"

 

[aziz rasul]

Changed

strTableName = "'" & "tbl_TempTable" & "'"

to

strTableName = "`" & "tbl_TempTable" & "`"

to get it to work.