Solved

launch MS mail merge wizard via code

Posted on 2000-03-28
7
428 Views
Last Modified: 2012-08-13
I have an interface that lets users build an SQL string (via text boxes, list boxes, etc.) that is used to launch forms and reports. I want to use this string to launch the MS mail merge wizard (Tools/OfficeLinks/Merge it with MS Word) and have it use my SQL string or a query def.

The command to run the wizard seems to be:

DoCmd.RunCommand (acCmdWordMailMerge)

Two problems:
1. I can't get it to work
2. It doesn't have a parameter to specify the SQL or table name.  
Any other ideas?
0
Comment
Question by:loe
  • 4
  • 3
7 Comments
 
LVL 14

Expert Comment

by:mgrattan
ID: 2674815
First, make sure you add the wizard file to your References -- open a module and click Tools, References.  Select wzmain80 from the list (if it's not there, Browse for the wzmain80.mda file in the Office directory).

Once the reference is added you can use the following function:

Function RunMerge(sSQL as String)
  PM_Entry(sSQL)
End Function

0
 

Author Comment

by:loe
ID: 2681705
This looks like a good first step to a solution. I found this file:  Wzmain80.mde which obviously contains a bunch of wizard stuff, but did not find a file with the extension .mda.  Nevertheless, once I added the Wzmain80.mde file to my references I got the function PM_Entry() to work. However, it appears to need a query or table as an argument. Strings don't work.

A couple of questions:
1. Have you gotten it to work using an SQL string? Does the .mda file allow you to use a string? I suppose I could switch to using a querydef, but I'd rather use my code as-is if I can.
2. How did you discover this function? Is there a directory of libraries and their functions out there somewhere? I never would have known to look there if you hadn't suggested it.

Thanks. I look forward to your response.
0
 
LVL 14

Accepted Solution

by:
mgrattan earned 100 total points
ID: 2681731
Forgive me for not testing!  Looks like I need to take a little more time with my answers.  Anyway, a few points:

1.  It will not work with an SQL string as you have discovered.  However, there are ways around this (see routine below).
2.  It is an MDE file (it used to be an MDA file in 2.0 I think.)
3.  The Wizard functions are outlined in several references; I found this information in the Access 97 Developers Handbook by Litwin/Getz.

Sub RunMailMerge(sSQL As String)
Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb
Set qdf = db.CreateQueryDef("TempQuery", sSQL)

PM_Entry (qdf.Name)

For each qdf in db.querydefs
    if qdf.name = "TempQuery" then
    DoCmd.DeleteObject acQuery, "TempQuery"
    Exit For
Next qdf
End Sub
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:loe
ID: 2701584
Your FOR loop needed a little fine-tuning, but the concept was sound. Here's the final code I used:

Sub RunMailMerge(sSQL As String)
  Dim db As Database
  Dim qdf As QueryDef

  Set db = CurrentDb
  Set qdf = db.CreateQueryDef("TempQuery", sSQL)

  PM_Entry (qdf.Name)

  For Each qdf In db.QueryDefs
      If qdf.Name = "TempQuery" Then
          DoCmd.DeleteObject acQuery, "TempQuery"
      End If
  Next qdf

End Sub

Thanks for your help!
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 2704225
loe,

The only thing you changed in my For loop was to remove the Exit For line.  Actually, it's better if you leave it in.  That way the routine will be exited as soon as the temp query is found and deleted and you won't be looping through the entire QueryDefs collection.
0
 

Author Comment

by:loe
ID: 2704476
I think this was what you had in mind:

For Each qdf In db.QueryDefs
    If qdf.Name = "TempQuery" Then
        DoCmd.DeleteObject acQuery, "TempQuery"
        Exit For
    End If
Next qdf

Your code was missing an End If statement, so it didn't compile when I tried to run it.

Good point about the Exit For statement. I was not familiar with it, so when I was troubleshooting the error I thought perhaps you had intended to put the End If statement there. I'll plug it back into my routine.

Thanks again for your help, my end-user is very happy with the new feature!
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 2704547
Glad to hear everything's working.  Sorry about the missing End If.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Runtime 2010 Error 17 32
Field behavior for "locked" form 12 29
Search Form not Querying 2 11
Field Size - Double?  Want to display 0 5 30
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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 …

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now