Solved

Problem with Mail Merge using VBA

Posted on 2009-06-30
17
574 Views
Last Modified: 2012-05-07
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.Visible = True
   
   'Set mail merge data source as current database
   If intReportType = 1 Then
      objWord.MailMerge.OpenDataSource _
      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.OpenDataSource _
      Name:=CurrentDb.Name, _
      LinkToSource:=True, _
      Connection:="Query qryFundedAwards-Partial", _
      SQLStatement:="Select * from [qryFundedAwards-Partial]Where " & strCriteria
   ElseIf intReportType = 3 Then
      objWord.MailMerge.OpenDataSource _
      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.Visible = True
   
Err_MergeIt_Exit:
   Exit Sub

Err_MergeIt:
   MsgBox "Error No: " & Err.Number & "; Description; " & Err.Description, , _
                     "frmfrmPrintAwardLetters_MergeIt"
   Resume Err_MergeIt_Exit
   

End Sub
0
Comment
Question by:gemost
  • 10
  • 7
17 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24747047
change this
-----------------------------------------------------v- need a space
SQLStatement:="Select * from [qryFundedAwards-Fully]Where " & strCriteria

to

SQLStatement:="Select * from [qryFundedAwards-Fully] Where " & strCriteria
0
 
LVL 3

Author Comment

by:gemost
ID: 24747492
The document opens but still asks to confirm the data source and once the document is displayed access throughs the following error.
 

 
 

Doc1.doc
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24747626
 If intReportType = 1 Then
      objWord.MailMerge.OpenDataSource _
      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
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 3

Author Comment

by:gemost
ID: 24747702
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.OpenDataSource _
      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.OpenDataSource _
      Name:=CurrentDb.Name, _
      LinkToSource:=True, _
      Connection:="Query qryFundedAwards-Partial", _
      SQLStatement:="Select * from [qryFundedAwards-Partial]Where " & strCriteria
   ElseIf intReportType = 3 Then
      objWord.MailMerge.OpenDataSource _
      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.Visible = True
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24747742
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.Visible = True
--------------------------------------






 Set objWord = GetObject(strFilepath, "Word.document")
   objWord.Application.Visible = True
 'Set mail merge data source as current database
   If intReportType = 1 Then
      objWord.MailMerge.OpenDataSource _
      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.OpenDataSource _
      Name:=CurrentDb.Name, _
      LinkToSource:=True, _
      Connection:="Query qryFundedAwards-Partial", _
      SQLStatement:="Select * from [qryFundedAwards-Partial] Where " & strCriteria
   ElseIf intReportType = 3 Then
      objWord.MailMerge.OpenDataSource _
      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
0
 
LVL 3

Author Comment

by:gemost
ID: 24747803
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.
0
 
LVL 3

Author Comment

by:gemost
ID: 24747827
I am also getting the attached error.
Doc2.doc
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24747845
change  Query  to Queries

Connection:="Query qryFundedAwards-NonFunded",
to

Connection:="Queries qryFundedAwards-NonFunded",
0
 
LVL 3

Author Comment

by:gemost
ID: 24748097
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_MergeIt"

And I get the error message that I posted earlier.
0
 
LVL 3

Author Comment

by:gemost
ID: 24748204
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_MergeIt"

Doc3.doc
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24748281

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
0
 
LVL 3

Author Comment

by:gemost
ID: 24748290
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?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24748321
gemost,
could you try what i posted last.
0
 
LVL 3

Author Comment

by:gemost
ID: 24748449
Sorry, I did not see your last post until now attached is the screen shot.
Doc4.doc
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 24748549
<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.>

this could be the problem.

try creating a new Word document for the mail merge

select the query qryFundedAwards-NonFunded

from Tools > Office Links > Merge with Microsoft Word

just follow the wizard
0
 
LVL 3

Author Comment

by:gemost
ID: 24756922
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.OpenDataSource _
      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.OpenDataSource _
      Name:=CurrentDb.Name, _
      LinkToSource:=True, _
      Connection:="Queries qryFundedAwards-Partial", _
      SQLStatement:="Select * from [qryFundedAwards-Partial] Where " & strCriteria
   ElseIf intReportType = 3 Then
      objWord.MailMerge.OpenDataSource _
      Name:=CurrentDb.Name, _
      LinkToSource:=True, _
      Connection:="Queries qryFundedAwards-NonFunded", _
      SQLStatement:="Select * from [qryFundedAwards-NonFunded] Where " & strCriteria
   End If
0
 
LVL 3

Accepted Solution

by:
gemost earned 0 total points
ID: 24795670
I creted temp tables to hold the data from the query for each letter and then used and insert statement to populate the temp table. The code then runs the merge using the temp table and works great with the exception of  it opens to word windows one containing the document  and one record and the other containing the completed merge.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question