Run-time Error 91 : Object variable or With block variable not set
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 theEnd Sub
Dragon Warrior
Updated on July 18, 2022Comments
-
Dragon Warrior almost 2 years
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