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

Topic

New Reply

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:

38409-Debug

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
[COLOR="#FF0000"].Paragraphs(1).Range.Delete[/COLOR]
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

Reply | Quote

Replies

BerryBlue,

Welcome to the Lounge as a new poster! :cheers:

If you hit the Debug button which statement is highlighted? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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!

Reply | Quote

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)

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

Msgbox “The Document name is: ” & DocName.Name, vbokonly+vbinformation, “Debug DocName”
[/noparse]
If the error moves to this line there is something wrong with your call statement or before!

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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.

Reply | Quote

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.[/quote]
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?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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. :D: :cheers:

Reply | Quote