loe
asked on
launch MS mail merge wizard via code
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?
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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("TempQue ry", 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!
Sub RunMailMerge(sSQL As String)
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.CreateQueryDef("TempQue
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!
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.
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.
ASKER
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!
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!
Glad to hear everything's working. Sorry about the missing End If.
Once the reference is added you can use the following function:
Function RunMerge(sSQL as String)
PM_Entry(sSQL)
End Function