Run-time Error 91 : Object variable or With block variable not set

Dragon Warrior picture Dragon Warrior · Jun 29, 2015 · Viewed 16.6k times · Source

I have 2 separate word documents with Mail Merge lists. And I have an excel workbook with 2 sheets. Based on the worksheet name & if the sheet is not empty, I need to send the mailmerge to that respective word document(s).

When I try to execute this code, it runs upto the first document and at the second document, it stops with an error Run-time Error 91 : Object variable or With block variable not set

I'm not sure what's causing this error (if it's the Dim variable or With block). Would greatly appreciate if someone could kindly help me rectify this error.

Sub Generate_Certificate() 

    Dim wd As Object 
    Dim wdoc_reg As Object 
    Dim wdoc_occ As Object 
    Dim strWbName_reg As String 
    Dim strWbName_occ As String 


    Const wdFormLetters = 0, wdOpenFormatAuto = 0 
    Const wdFormLetters1 = 0, wdOpenFormatAuto1 = 0 
    Const wdSendToNewDocument = 0, wdDefaultFirstRecord = 1, wdDefaultLastRecord = -16 
    Const wdSendToNewDocument1 = 0, wdDefaultFirstRecord1 = 1, wdDefaultLastRecord1 = -16 


    On Error Resume Next 
    Set wd = GetObject(, "Word.Application") 
    If wd Is Nothing Then 
        Set wd = CreateObject("Word.Application") 
    End If 
    On Error Goto 0 


    For Each Sheet In ActiveWorkbook.Sheets 

         'Generate report using "Mailmerge" if any data available for Mailmerge1
        If Sheet.Name Like "Sheet1" And IsEmpty(ThisWorkbook.Sheets("Sheet1").Range("A2").Value) = False Then 
            Set wdoc_reg = wd.Documents.Open("C:\Mailmerge1.docx") 


            strWbName_reg = ThisWorkbook.Path & "\" & ThisWorkbook.Name 


            wdoc_reg.MailMerge.MainDocumentType = wdFormLetters 


            wdoc_reg.MailMerge.OpenDataSource _ 
            Name:=strWbName_reg, _ 
            AddToRecentFiles:=False, _ 
            Revert:=False, _ 
            Format:=wdOpenFormatAuto, _ 
            Connection:="Data Source=" & strWbName_reg & ";Mode=Read", _ 
            SQLStatement:="SELECT * FROM `Sheet1$`" 

            With wdoc_reg.MailMerge 
                .Destination = wdSendToNewDocument 
                .SuppressBlankLines = True 
                With .DataSource 
                    .FirstRecord = wdDefaultFirstRecord 
                    .LastRecord = wdDefaultLastRecord 
                End With 
                .Execute Pause:=False 
            End With 


            wd.Visible = True 
            wdoc_reg.Close SaveChanges:=False 


            Set wdoc_reg = Nothing 
            Set wd = Nothing 
        End If 


         'Generate report using "Mailmerge" if any data available for Mailmerge2
        If Sheet.Name Like "Sheet2" And IsEmpty(ThisWorkbook.Sheets("Sheet2").Range("A2").Value) = False Then 
            Set wdoc_occ = wd.Documents.Open("C:\Mailmerge2.docx") 


            strWbName_occ = ThisWorkbook.Path & "\" & ThisWorkbook.Name 


            wdoc_occ.MailMerge.MainDocumentType = wdFormLetters1 


            wdoc_occ.MailMerge.OpenDataSource _ 
            Name:=strWbName_Occ, _ 
            AddToRecentFiles:=False, _ 
            Revert:=False, _ 
            Format:=wdOpenFormatAuto1, _ 
            Connection:="Data Source=" & strWbName_occ & ";Mode=Read", _ 
            SQLStatement:="SELECT * FROM `Sheet2$`" 


            With wdoc_occ.MailMerge 
                .Destination = wdSendToNewDocument1 
                .SuppressBlankLines = True 
                With .DataSource 
                    .FirstRecord = wdDefaultFirstRecord1 
                    .LastRecord = wdDefaultLastRecord1 
                End With 
                .Execute Pause:=False 
            End With 


            wd.Visible = True 
            wdoc_occ.Close SaveChanges:=False 


            Set wdoc_Occ = Nothing 
            Set wd = Nothing 
        End If 


    Next 


End Sub

Answer

DiegoAndresJAY picture DiegoAndresJAY · Aug 5, 2015

As stated by Tim Williams in the question's comments.

You have Set wd = Nothing inside your loop, which will clear your reference to Word after the first sheet. Move that to just before the End Sub