[RESOLVED] VBA - Mailmerge

[kalam1989]

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 tried to execute this code, it shows "Run-time Error 91 : Object variable or With block variable not set".
And my first word document could be executed. There is only problem with the 2nd document (debugger also highlights in yellow for the code of my 2nd document).

Would greatly appreciate if anyone could kindly tell what do I have to do, in order to remove this error.
Thank you so much for your time!!

My coding is as follows:

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

[westconn1]

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

on which line?
you should remove set wd = nothing from within your loop (2 places), it should be after the loop has finished

there is no need for a separate code block for each sheet, only need to set appropriate document and data sheet for each.

try like

   For Each Sheet In ActiveWorkbook.Sheets 
         
         'Generate report using "Mailmerge" if any data available for Mailmerge1
        If (Sheet.Name ="Sheet1" or sheet.name ="Sheet2")  And not IsEmpty(ThisWorkbook.Sheets("Sheet1").Range("A2").Value) Then 
            Set wdoc_reg = wd.Documents.Open("C:\Mailmerge" & right(sheet.name, 1) & ".docx") 
             
           
            wdoc_reg.MailMerge.MainDocumentType = wdFormLetters 
             
             
            wdoc_reg.MailMerge.OpenDataSource _ 
            Name:=strWbName_reg, _ 
            AddToRecentFiles:=False, _ 
            Revert:=False, _ 
            Format:=wdOpenFormatAuto, _ 
            Connection:="Data Source=" & thisworkbook.fullname & ";Mode=Read", _ 
            SQLStatement:="SELECT * FROM `" & sheet.name & "$`" 
             
            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 
        End If 
         
     Next 
  Set wd = Nothing

you should avoid using reserved words (Sheet in this case) for variable or procedure names, try some abbreiviation or prepend some indication of variable type

[kalam1989]

Thank you so much for your prompt response, westconn1!
And, that's very thoughtful of you to point out the easier way!!

Sorry I'm a newbie in terms of VBA / Macro programming
Your help is much appreciated!!

on which line?
you should remove set wd = nothing from within your loop (2 places), it should be after the loop has finished

there is no need for a separate code block for each sheet, only need to set appropriate document and data sheet for each.

try like

   For Each Sheet In ActiveWorkbook.Sheets 
         
         'Generate report using "Mailmerge" if any data available for Mailmerge1
        If (Sheet.Name ="Sheet1" or sheet.name ="Sheet2")  And not IsEmpty(ThisWorkbook.Sheets("Sheet1").Range("A2").Value) Then 
            Set wdoc_reg = wd.Documents.Open("C:\Mailmerge" & right(sheet.name, 1) & ".docx") 
             
           
            wdoc_reg.MailMerge.MainDocumentType = wdFormLetters 
             
             
            wdoc_reg.MailMerge.OpenDataSource _ 
            Name:=strWbName_reg, _ 
            AddToRecentFiles:=False, _ 
            Revert:=False, _ 
            Format:=wdOpenFormatAuto, _ 
            Connection:="Data Source=" & thisworkbook.fullname & ";Mode=Read", _ 
            SQLStatement:="SELECT * FROM `" & sheet.name & "$`" 
             
            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 
        End If 
         
     Next 
  Set wd = Nothing

you should avoid using reserved words (Sheet in this case) for variable or procedure names, try some abbreiviation or prepend some indication of variable type

[supersid]

Dear westconn1,

May I ask one more question pertaining to the above code?
Using this loop, if I want to save both the output MailMerge files as "A" and "B" (if it's from Sheet 1 and Sheet 2 respectively), how can I do so?

Sorry for the trouble again, and hoping to hear from you!

[westconn1]

you should be able to just save the last document opened (added)

try like

wd.documents(1).saveas thisdocument.path & "\merge" & chr(right(sheet.name, 1) + 64) & ".doc"

change path and document name to suit
test to see if correct file is saved

[supersid]

There is a

Runtime error : 424 - Object required

, if I put it after the With block
Could you please show me an example?

Also, if I want to save the documents as different strings (based on criteria), can I do so?

[westconn1]

Also, if I want to save the documents as different strings (based on criteria), can I do so?

yes of course

Runtime error : 424 - Object required

probably from the use of thisdocument.path
try changing to wdoc_reg.path

[supersid]

Thank you!
And sorry again... A last question...
Could you kindly show me an example of how to save the documents as different strings (based on criteria)?

[westconn1]

as different strings (based on criteria)?

what criteria?
the example posted saved based on the sheet name criteria, which is an example
you would get files mergeA.doc and mergeB.doc for sheet1 and sheet2 respectively, what other criteria do you have?