Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Nov 2014
    Posts
    12
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Question Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge

    Hi,

    I'm hoping for a resolution. I read the instructions and I'm pretty comfortable with Mergefields and have all of my documents all aligned and functioning. My problem is when I went to use the VBA code supplied under "Merging by Catalog/Director to E-Mail". I had to manually type into the document because of network security settings and I truly thought I was doing something wrong as I keep getting the below error:

    Debug.PNG

    So I decided to copy and paste the code from the tutorial into the Email Merge documents including in the zip file on my personal laptop and I received the same error. What am I doing wrong or is their a piece of code that should/shouldn't be included in the code? Below is the code and I bold the line where the debug button leads me to. Hoping for a resolution. Please and thank you Paul.
    Code:
    Sub RunMerge()
    Application.ScreenUpdating = False
    Dim Doc1 As Document, Doc2 As Document, Doc3 As Document, StrDoc As String
    Set Doc1 = ThisDocument
    StrDoc = ThisDocument.Path & "\EmailDataSource.docx"
    If Dir(StrDoc) <> "" Then Kill StrDoc
    With Doc1.MailMerge
    If .State = wdMainAndDataSource Then
    .Destination = wdSendToNewDocument
    .Execute
    Set Doc2 = ActiveDocument
    End If
    End With
    Call EmailMergeTableMaker(Doc2)
    With Doc2
    .SaveAs FileName:=StrDoc, AddToRecentFiles:=False, FileFormat:=wdFormatDocument
    StrDoc = .FullName
    .Close
    End With
    Set Doc2 = Nothing
    Set Doc3 = Documents.Open(FileName:=Doc1.Path & "\Email Merge Main Document.docx", _
    AddToRecentFiles:=False)
    With Doc3.MailMerge
    .MainDocumentType = wdEMail
    .OpenDataSource Name:=StrDoc, ConfirmConversions:=False, ReadOnly:=False, _
    LinkToSource:=True, AddToRecentFiles:=False, Connection:="", SQLStatement:="", _
    SQLStatement1:="", SubType:=wdMergeSubTypeOther
    If .State = wdMainAndDataSource Then
    '.Destination = wdSendToNewDocument
    .Destination = wdSendToEmail
    .MailAddressFieldName = "Recipient"
    .MailSubject = "Monthly Sales Stats"
    .MailFormat = wdMailFormatPlainText
    .Execute
    End If
    End With
    Doc3.Close SaveChanges:=False
    Set Doc3 = Nothing
    Application.ScreenUpdating = True
    End Sub
    '--------------------------------------------------------------
    Sub EmailMergeTableMaker(DocName As Document)
    Dim oTbl As Table, i As Integer, j As Integer, oRow As Row, oRng As Range, strTxt As String
    With DocName
    .Paragraphs(1).Range.Delete
    Call TableJoiner
    For Each oTbl In .Tables
    j = 2
    With oTbl
    i = .Columns.Count - j
    For Each oRow In .Rows
    Set oRng = oRow.Cells(j).Range
    With oRng
    .MoveEnd Unit:=wdCell, Count:=i
    .Cells.Merge
    strTxt = Replace(.Text, vbCr, vbTab)
    On Error Resume Next
    If Len(strTxt) > 1 Then .Text = Left(strTxt, Len(strTxt) - 2)
    End With
    Next
    End With
    Next
    For Each oTbl In .Tables
    For i = 1 To j
    oTbl.Columns(i).Cells.Merge
    Next
    Next
    With .Tables(1)
    .Rows.Add BeforeRow:=.Rows(1)
    .Cell(1, 1).Range.Text = "Recipient"
    .Cell(1, 2).Range.Text = "Data"
    End With
    .Paragraphs(1).Range.Delete
    Call TableJoiner
    End With
    Set oRng = Nothing
    End Sub
    Private Sub TableJoiner()
    Dim oTbl As Table
    For Each oTbl In ActiveDocument.Tables
    With oTbl.Range.Next
    If .Information(wdWithInTable) = False Then .Delete
    End With
    Next
    End Sub
    Last edited by RetiredGeek; 2014-11-13 at 21:36.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    BerryBlue,

    Welcome to the Lounge as a new poster!

    If you hit the Debug button which statement is highlighted?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Berryblue (2014-11-14)

  4. #3
    New Lounger
    Join Date
    Nov 2014
    Posts
    12
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I'm giving you two rows; it's right after the Sub EmailMergeTableMaker(DocName As Document)

    With DocName
    .Paragraphs(1).Range.Delete

    Thank you! And thank you for the welcome!

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    Sorry I missed your name the first time.

    I can't see anything glaring. You've initialized the variable Doc2 before using it in the call. My initial reaction was that the object wasn't initialized which is normally what that message means.

    What I'd try is changing 2 lines (Note these are only variations of what you are doing but who knows?)

    Code:
    'Change this:
    Call EmailMergeTableMaker(Doc2)
    
    'To this:
    EmailMergeTableMaker Doc2
    
    'Change this:
    Sub EmailMergeTableMaker(DocName As Document)
    
    'To this:
    Sub EmailMergeTableMaker(byRef DocName As Document)

    Note: you could also include this just before the With DocName line:

    Msgbox "The Document name is: " & DocName.Name, vbokonly+vbinformation, "Debug DocName"

    If the error moves to this line there is something wrong with your call statement or before!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Berryblue (2014-11-14)

  7. #5
    New Lounger
    Join Date
    Nov 2014
    Posts
    12
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Paul,

    Sorry I missed your name the first time.
    I laughed when I saw this. I realized I signed it as if I were Paul. I was actually thanking Paul as I came across his name on YouTube; he is the original code writer as I took this out of his tutorial. He also recommended that I come here to post. However I want to say THANK YOU!! I'm going to try the changes you suggested and hopefully it will fix it. I truly appreciate this. Actually my name is Anna.

  8. #6
    New Lounger
    Join Date
    Nov 2014
    Posts
    12
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Hi,

    No that didn't clear the error; I'm still getting it. I suspect that it has something to do with the sub below:

    Code:
    Private Sub TableJoiner()
    Dim oTbl As Table
    For Each oTbl In ActiveDocument.Tables
      With oTbl.Range.Next
        If .Information(wdWithInTable) = False Then .Delete
      End With
    Next
    End Sub
    I also added the code as you suggested and the error did move to that line.

    Code:
    Msgbox "The Document name is: " & DocName.Name, vbokonly+vbinformation, "Debug DocName"
    I say this because the error appears right before this call. Thanks,
    Anna
    Last edited by Berryblue; 2014-11-13 at 22:47. Reason: Added code info

  9. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Anna,

    Sorry, but I think I've exhausted my Word VBA knowledge! I do almost all of my work in Excel & Access and I'm just not familiar with the Word Object Model. I'm sure someone else here will jump in and give you a hand. Good Luck!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Berryblue (2014-11-14)

  11. #8
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    The error is telling you that the DocName object doesn't exist (or perhaps more accurately, it exists but it doesn't contain anything).

    The RunMerge Macro is passing a Document to EmailMergeTableMaker but the value of that document at the time is 'Nothing'. This would be the case if Doc1.MailMerge.State = wdMainAndDataSource. If you step through the code in RunMerge you will discover that the line 'Set Doc2 = ActiveDocument' doesn't get run and therefore when you subsequently 'Call EmailMergeTableMaker(Doc2)' you are passing in an empty document variable instead of a document.

    The fix is to ensure that the location where this code is placed is a Word document with an attached data source.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  12. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    Berryblue (2014-11-14)

  13. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Basically, the problem comes down to not following the instructions in the tutorial closely enough. When followed, the code runs without error.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. The Following User Says Thank You to macropod For This Useful Post:

    Berryblue (2014-11-14)

  15. #10
    New Lounger
    Join Date
    Nov 2014
    Posts
    12
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Andrew Lockton View Post
    The error is telling you that the DocName object doesn't exist (or perhaps more accurately, it exists but it doesn't contain anything).

    [/B].
    I extracted all the files from the zip and placed them in my Documents folder. The instructions says to copy and paste the code into VBA in the Directory/Catalog file or at least that is how I'm reading it. I'm placing the VBA coded into the file EmailDataSource.doc which contains the catalog data fields.

    Am I placing the code into the wrong file? Was I suppose to rename that file?

    Also I agree that Doc2 is set to nothing. Thank you for explanation.

  16. #11
    New Lounger
    Join Date
    Nov 2014
    Posts
    12
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    Basically, the problem comes down to not following the instructions in the tutorial closely enough. When followed, the code runs without error.
    Please help me understand and I'm hoping I got it right. I have 2 files and the names on my office computer are as follows:

    EmailMergeDataCatalog.doc
    Was originally a "docx" file format but changed it to "doc". This contains a table with Mergefields that are in a table format and I've associated the Excel file that it is to pull from. I added the VBA code to this file.

    Email Merge Main Document.doc
    This is the formatted text file with my paragraph and the Mergefield called Data.

    According to the tutorial, as soon as I run the macro, it will also create a file named "EmailDataSource.doc" and pull data from both of these files. Was I suppose to change anything in the VBA code? Or did I place it in the correct file? And yes I was thanking you in my initial post.

  17. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by Berryblue View Post
    EmailMergeDataCatalog.doc
    Was originally a "docx" file format but changed it to "doc". This contains a table with Mergefields that are in a table format and I've associated the Excel file that it is to pull from. I added the VBA code to this file.
    Docx files can't be saved with macros - you'd need to have saved the file in either the docm or doc formats. Also, as noted in the tutorial:
    The ‘RunMerge’ macro that drives this process (see below) assumes your e mail mailmerge main document will be named ‘Email Merge Main Document’. If that’s not what you name your document, you’ll need to change the corresponding name in the ‘RunMerge’ macro
    Did you make that change?

    EmailMergeDataCatalog.doc
    Was originally a "docx" file format but changed it to "doc".
    Unless you changed the filename in the macro too, which is coded to look for 'EmailMergeDataCatalog.doc', having the docx extension would cause such the error.

    Did you save both the 'EmailMergeDataCatalog.doc' and your mailmerge main document to the same folder before running the macro?
    Last edited by macropod; 2014-11-14 at 17:34.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  18. The Following User Says Thank You to macropod For This Useful Post:

    Berryblue (2014-11-14)

  19. #13
    New Lounger
    Join Date
    Nov 2014
    Posts
    12
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Did you save both the 'EmailMergeDataCatalog.doc' and your mailmerge main document to the same folder before running the macro?
    Yes I changed everything to reflect "doc" and I also have all of the files in the same folder. I also kept your naming convention for the file and made sure that it states the same thing in the VBA code. For the life of me I can't figure out why it doesn't want to work. I'm going to write sample files this weekend and upload them here to see if you see anything glaring or if I'm doing something wrong. I can't post the actual files due to company rules. Thanks for confirming.

  20. #14
    New Lounger
    Join Date
    Nov 2014
    Posts
    12
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Cool

    I figured out the issue and it had nothing to do with my file format or even the code! As usual a compiler never truly provides exactly what the issue is. The issue was that if I closed the file that was my 'catalog' and came back. I would get a window that pops up that states

    Opening this document will run the following SQL command:

    SELECT * FROM "Test Data" ORDER BY 'ACCOUNT' ASC

    Data from your database will be placed in the document. Do you want to continue?
    I would always answer "No" and the file would load. Well that is why it refused to cooperate; the answer should always be Yes. I posting this here just in case someone else does the same thing.

    It is now running and thank you so much in helping me troubleshoot the issue.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •