gemost
asked on
Problem with Mail Merge using VBA
I am trying to do a mail merge using the following code. The problem is the code executes but I get a dialog box asking me to confirm the Data Source. No matter which I choose the document does not populate. When I merge to the document through accesses merge utility I have no problems. I need this to merge without asking to confirm the data source as many people will be using this for many merges. Any help is appreciated.
Thanks
Steve
Private Sub MergeIt(strFilepath As String, strCriteria As String, intReportType As Integer)
Dim objWord As Word.Document
On Error GoTo Err_MergeIt
Set objWord = GetObject(strFilepath, "Word.document")
' Make Word visible
objWord.Application.Visibl e = True
'Set mail merge data source as current database
If intReportType = 1 Then
objWord.MailMerge.OpenData Source _
Name:="C:\GIMS_HS\GIMS-HS- DEV.mdb", _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Fully", _
SQLStatement:="Select * from [qryFundedAwards-Fully]Whe re " & strCriteria
ElseIf intReportType = 2 Then
objWord.MailMerge.OpenData Source _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Partial", _
SQLStatement:="Select * from [qryFundedAwards-Partial]W here " & strCriteria
ElseIf intReportType = 3 Then
objWord.MailMerge.OpenData Source _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-NonFunded" , _
SQLStatement:="Select * from [qryFundedAwards-NonFunded ]Where " & strCriteria
End If
' Execute the Mail Merge
objWord.MailMerge.Execute
Set objWord = GetObject(strFilepath, "Word.document")
objWord.Application.Visibl e = True
Err_MergeIt_Exit:
Exit Sub
Err_MergeIt:
MsgBox "Error No: " & Err.Number & "; Description; " & Err.Description, , _
"frmfrmPrintAwardLetters_M ergeIt"
Resume Err_MergeIt_Exit
End Sub
Thanks
Steve
Private Sub MergeIt(strFilepath As String, strCriteria As String, intReportType As Integer)
Dim objWord As Word.Document
On Error GoTo Err_MergeIt
Set objWord = GetObject(strFilepath, "Word.document")
' Make Word visible
objWord.Application.Visibl
'Set mail merge data source as current database
If intReportType = 1 Then
objWord.MailMerge.OpenData
Name:="C:\GIMS_HS\GIMS-HS-
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Fully", _
SQLStatement:="Select * from [qryFundedAwards-Fully]Whe
ElseIf intReportType = 2 Then
objWord.MailMerge.OpenData
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Partial", _
SQLStatement:="Select * from [qryFundedAwards-Partial]W
ElseIf intReportType = 3 Then
objWord.MailMerge.OpenData
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-NonFunded"
SQLStatement:="Select * from [qryFundedAwards-NonFunded
End If
' Execute the Mail Merge
objWord.MailMerge.Execute
Set objWord = GetObject(strFilepath, "Word.document")
objWord.Application.Visibl
Err_MergeIt_Exit:
Exit Sub
Err_MergeIt:
MsgBox "Error No: " & Err.Number & "; Description; " & Err.Description, , _
"frmfrmPrintAwardLetters_M
Resume Err_MergeIt_Exit
End Sub
ASKER
The document opens but still asks to confirm the data source and once the document is displayed access throughs the following error.
Doc1.doc
Doc1.doc
If intReportType = 1 Then
objWord.MailMerge.OpenData Source _
Name:="C:\GIMS_HS\GIMS-HS- DEV.mdb", _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Fully", _
SQLStatement:="Select * from [qryFundedAwards-Fully] Where " & strCriteria
objWord.MailMerge.Execute 'add this line
objWord.MailMerge.OpenData
Name:="C:\GIMS_HS\GIMS-HS-
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Fully", _
SQLStatement:="Select * from [qryFundedAwards-Fully] Where " & strCriteria
objWord.MailMerge.Execute 'add this line
ASKER
I have that further down in the code. Third line from the bottom
'Set mail merge data source as current database
If intReportType = 1 Then
objWord.MailMerge.OpenData Source _
Name:="C:\GIMS_HS\GIMS-HS- DEV.mdb", _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Fully", _
SQLStatement:="Select * from [qryFundedAwards-Fully]Whe re " & strCriteria
ElseIf intReportType = 2 Then
objWord.MailMerge.OpenData Source _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Partial", _
SQLStatement:="Select * from [qryFundedAwards-Partial]W here " & strCriteria
ElseIf intReportType = 3 Then
objWord.MailMerge.OpenData Source _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-NonFunded" , _
SQLStatement:="Select * from [qryFundedAwards-NonFunded ]Where " & strCriteria
End If
' Execute the Mail Merge
objWord.MailMerge.Execute
Set objWord = GetObject(strFilepath, "Word.document")
objWord.Application.Visibl e = True
'Set mail merge data source as current database
If intReportType = 1 Then
objWord.MailMerge.OpenData
Name:="C:\GIMS_HS\GIMS-HS-
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Fully", _
SQLStatement:="Select * from [qryFundedAwards-Fully]Whe
ElseIf intReportType = 2 Then
objWord.MailMerge.OpenData
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Partial", _
SQLStatement:="Select * from [qryFundedAwards-Partial]W
ElseIf intReportType = 3 Then
objWord.MailMerge.OpenData
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-NonFunded"
SQLStatement:="Select * from [qryFundedAwards-NonFunded
End If
' Execute the Mail Merge
objWord.MailMerge.Execute
Set objWord = GetObject(strFilepath, "Word.document")
objWord.Application.Visibl
this two line should be above your codes. i see that you have two sets of this two lines
remove the two at the bottom.
-------------------------- ---------- -
Set objWord = GetObject(strFilepath, "Word.document")
objWord.Application.Visibl e = True
-------------------------- ---------- --
Set objWord = GetObject(strFilepath, "Word.document")
objWord.Application.Visibl e = True
'Set mail merge data source as current database
If intReportType = 1 Then
objWord.MailMerge.OpenData Source _
Name:="C:\GIMS_HS\GIMS-HS- DEV.mdb", _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Fully", _
SQLStatement:="Select * from [qryFundedAwards-Fully] Where " & strCriteria
ElseIf intReportType = 2 Then
objWord.MailMerge.OpenData Source _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Partial", _
SQLStatement:="Select * from [qryFundedAwards-Partial] Where " & strCriteria
ElseIf intReportType = 3 Then
objWord.MailMerge.OpenData Source _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-NonFunded" , _
SQLStatement:="Select * from [qryFundedAwards-NonFunded ] Where " & strCriteria
End If
' Execute the Mail Merge
objWord.MailMerge.Execute
remove the two at the bottom.
--------------------------
Set objWord = GetObject(strFilepath, "Word.document")
objWord.Application.Visibl
--------------------------
Set objWord = GetObject(strFilepath, "Word.document")
objWord.Application.Visibl
'Set mail merge data source as current database
If intReportType = 1 Then
objWord.MailMerge.OpenData
Name:="C:\GIMS_HS\GIMS-HS-
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Fully", _
SQLStatement:="Select * from [qryFundedAwards-Fully] Where " & strCriteria
ElseIf intReportType = 2 Then
objWord.MailMerge.OpenData
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-Partial", _
SQLStatement:="Select * from [qryFundedAwards-Partial] Where " & strCriteria
ElseIf intReportType = 3 Then
objWord.MailMerge.OpenData
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Query qryFundedAwards-NonFunded"
SQLStatement:="Select * from [qryFundedAwards-NonFunded
End If
' Execute the Mail Merge
objWord.MailMerge.Execute
ASKER
When stepping through the code it never makes it to "objWord.MailMerge.Execute " but goes to the confirm datasource right after the SQLStatement in the IF Statement.
ASKER
I am also getting the attached error.
Doc2.doc
Doc2.doc
change Query to Queries
Connection:="Query qryFundedAwards-NonFunded" ,
to
Connection:="Queries qryFundedAwards-NonFunded" ,
Connection:="Query qryFundedAwards-NonFunded"
to
Connection:="Queries qryFundedAwards-NonFunded"
ASKER
Ok when stepping through the code at this Line 'Set objWord = GetObject(strFilepath, "Word.document")' I get word openning and asking for the data source confirmation. When the code gets to 'SQLStatement" the code goes to the error code below.
Err_MergeIt:
MsgBox "Error No: " & Err.Number & "; Description; " & Err.Description, , _
"frmPrintAwardLetters_Merg eIt"
And I get the error message that I posted earlier.
Err_MergeIt:
MsgBox "Error No: " & Err.Number & "; Description; " & Err.Description, , _
"frmPrintAwardLetters_Merg
And I get the error message that I posted earlier.
ASKER
Sorry, I gave the wrong info in last posting***
When stepping through the code at this Line 'Set objWord = GetObject(strFilepath, "Word.document")' I get the first message box. When I click yes the code goes to the if statement and when it gets to 'SQLStatement" I get the second message bax asking for the data source confirmation.I get the error code below no matter which option I select.
Err_MergeIt:
MsgBox "Error No: " & Err.Number & "; Description; " & Err.Description, , _
"frmPrintAwardLetters_Merg eIt"
Doc3.doc
When stepping through the code at this Line 'Set objWord = GetObject(strFilepath, "Word.document")' I get the first message box. When I click yes the code goes to the if statement and when it gets to 'SQLStatement" I get the second message bax asking for the data source confirmation.I get the error code below no matter which option I select.
Err_MergeIt:
MsgBox "Error No: " & Err.Number & "; Description; " & Err.Description, , _
"frmPrintAwardLetters_Merg
Doc3.doc
Private Sub MergeIt(strFilepath As String, strCriteria As String, intReportType As Integer)
'ADD THIS LINE
Msgbox strCriteria
post here the content of the message box
ASKER
Could part of the problem be that the document was originally set to the query so that when just openning the word document it wants to get the data from the query automatically. And if so how do I set it up to not look for the query when openning up?
gemost,
could you try what i posted last.
could you try what i posted last.
ASKER
Sorry, I did not see your last post until now attached is the screen shot.
Doc4.doc
Doc4.doc
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Two of the merges seem to be working but they open 2 instances of word the first has the merge data of one record and the second actually shows the data related to the criteria I set forth in the code. How can I stop this. The third keeps asking for the data source confirmation. I do not understand why this is happening because as I understand it the code below is the data source information I want to use for the merge,
'Set mail merge data source as current database
If intReportType = 1 Then
objWord.MailMerge.OpenData Source _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Queries qryFundedAwards-Fully", _
SQLStatement:="Select * from [qryFundedAwards-Fully] Where " & strCriteria
' objWord.MailMerge.Execute
ElseIf intReportType = 2 Then
objWord.MailMerge.OpenData Source _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Queries qryFundedAwards-Partial", _
SQLStatement:="Select * from [qryFundedAwards-Partial] Where " & strCriteria
ElseIf intReportType = 3 Then
objWord.MailMerge.OpenData Source _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Queries qryFundedAwards-NonFunded" , _
SQLStatement:="Select * from [qryFundedAwards-NonFunded ] Where " & strCriteria
End If
'Set mail merge data source as current database
If intReportType = 1 Then
objWord.MailMerge.OpenData
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Queries qryFundedAwards-Fully", _
SQLStatement:="Select * from [qryFundedAwards-Fully] Where " & strCriteria
' objWord.MailMerge.Execute
ElseIf intReportType = 2 Then
objWord.MailMerge.OpenData
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Queries qryFundedAwards-Partial", _
SQLStatement:="Select * from [qryFundedAwards-Partial] Where " & strCriteria
ElseIf intReportType = 3 Then
objWord.MailMerge.OpenData
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="Queries qryFundedAwards-NonFunded"
SQLStatement:="Select * from [qryFundedAwards-NonFunded
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
--------------------------
SQLStatement:="Select * from [qryFundedAwards-Fully]Whe
to
SQLStatement:="Select * from [qryFundedAwards-Fully] Where " & strCriteria