Controlling a Word Mail Merge (from Access)

D

Derek Wittman

Good afternoon,
I am working on controlling a Mail Merge from within MS Access using the
Word references. I have my main document and my datasource already linked to
it. What I cannot figure out is how to do the equivalent of Merging to a New
Document by way of the VBA code.

I want to merge to the new document, then save the document, then print or
email (depending on what the user selects).

Any and all help is greatly appreciated! Thank you!

Derek

My code is:

Private Sub cmdSubmit_Click()
Dim MSwordApp As word.Application
Dim MSwordDoc As word.Document
Dim strwordfilename As String
Dim savewordfilename As String
Dim selectme As Integer

DoCmd.RunSQL "Delete * from tblShortFormData"

Select Case txtRemaining

Case Is > 0.01, Is < -0.01

MsgBox "The total percentage MUST equal 100%. Please use the
'Remaining' field to check your math.", vbOKOnly, "Check your calcuations:"

Case Else
DoCmd.RunSQL "INSERT INTO tblShortFormData ([Store#], ProjectID,
ContractDate, Vendorname, Price, CompletionDate, WorkDesc, ProgPC1, ProgPC2,
ProgPC3, ProgPC4, ProgPC5, ProgPC6, ProgPC7, ProgPC8, Term1, Term2, Term3,
Term4, Term5, Term6, Term7, Term8) SELECT forms!frmShortForm.cboDCNumber,
Forms!frmShortForm.txtProjectNo, date(), forms!frmShortForm.txtVendor,
forms!frmShortForm.txtPrice, forms!frmShortForm.txtDone,
forms!frmShortForm.txtScope, forms!frmShortForm.txtPC1,
forms!frmShortForm.txtPC2, forms!frmShortForm.txtPC3,
forms!frmShortForm.txtPC4, forms!frmShortForm.txtPC5,
forms!frmShortForm.txtPC6, forms!frmShortForm.txtPC7,
forms!frmShortForm.txtPC8, forms!frmShortForm.txtTerm1,
forms!frmShortForm.txtTerm2, forms!frmShortForm.txtTerm3,
forms!frmShortForm.txtTerm4, forms!frmShortForm.txtTerm5,
forms!frmShortForm.txtTerm6, forms!frmShortForm.txtTerm7,
forms!frmShortForm.txtTerm8"

Set MSwordApp = CreateObject("word.application")

MSwordApp.Visible = True

strwordfilename =
"v:\distcntr\dcdesign\projmgmt\Contract\Short_Forms\Short_FormB_Unprotected.doc"

Set MSwordDoc = MSwordApp.Documents.Open(strwordfilename)

savewordfilename = "v:\distcntr\dcdesign\projmgmt\contract\Short_Forms\" &
Forms!frmShortForm.cboDCNumber & Forms!frmShortForm.txtVendor & ".doc"


MSwordApp.ActiveDocument.MailMerge.Destination = wdSendToNewDocument

MSwordDoc.SaveAs (savewordfilename)

selectme = MsgBox("Do you want to print or email the Short Form Contract?
YES to Print and NO to email", vbYesNo, "YES to Print or NO to email")

Select Case selectme
Case 6
MSwordDoc.PrintOut , , , , , , , , , , , True
Case 7
MSwordApp.ActiveDocument.SendMail
End Select
MSwordDoc.Close
MSwordApp.Quit
Set MSwordDoc = Nothing
Set MSwordApp = Nothing
DoCmd.SelectObject acForm, "frmShortForm"
DoCmd.Close
End Select
End Sub
 
J

Joe Smith

According to the MSDN, right after thie following line:

MSwordApp.ActiveDocument.MailMerge.Destination = wdSendToNewDocument

you then need

MSwordApp.ActiveDocument.MailMerge.Execute

I guess that executes the mail merge.

Here's the link I used as reference

http://msdn.microsoft.com/library/d...y/en-us/office97/html/output/F1/D4/S5A9DB.asp

Hope that helps

Steve


Derek Wittman said:
Good afternoon,
I am working on controlling a Mail Merge from within MS Access using the
Word references. I have my main document and my datasource already linked
to
it. What I cannot figure out is how to do the equivalent of Merging to a
New
Document by way of the VBA code.

I want to merge to the new document, then save the document, then print or
email (depending on what the user selects).

Any and all help is greatly appreciated! Thank you!

Derek

My code is:

Private Sub cmdSubmit_Click()
Dim MSwordApp As word.Application
Dim MSwordDoc As word.Document
Dim strwordfilename As String
Dim savewordfilename As String
Dim selectme As Integer

DoCmd.RunSQL "Delete * from tblShortFormData"

Select Case txtRemaining

Case Is > 0.01, Is < -0.01

MsgBox "The total percentage MUST equal 100%. Please use the
'Remaining' field to check your math.", vbOKOnly, "Check your
calcuations:"

Case Else
DoCmd.RunSQL "INSERT INTO tblShortFormData ([Store#], ProjectID,
ContractDate, Vendorname, Price, CompletionDate, WorkDesc, ProgPC1,
ProgPC2,
ProgPC3, ProgPC4, ProgPC5, ProgPC6, ProgPC7, ProgPC8, Term1, Term2, Term3,
Term4, Term5, Term6, Term7, Term8) SELECT forms!frmShortForm.cboDCNumber,
Forms!frmShortForm.txtProjectNo, date(), forms!frmShortForm.txtVendor,
forms!frmShortForm.txtPrice, forms!frmShortForm.txtDone,
forms!frmShortForm.txtScope, forms!frmShortForm.txtPC1,
forms!frmShortForm.txtPC2, forms!frmShortForm.txtPC3,
forms!frmShortForm.txtPC4, forms!frmShortForm.txtPC5,
forms!frmShortForm.txtPC6, forms!frmShortForm.txtPC7,
forms!frmShortForm.txtPC8, forms!frmShortForm.txtTerm1,
forms!frmShortForm.txtTerm2, forms!frmShortForm.txtTerm3,
forms!frmShortForm.txtTerm4, forms!frmShortForm.txtTerm5,
forms!frmShortForm.txtTerm6, forms!frmShortForm.txtTerm7,
forms!frmShortForm.txtTerm8"

Set MSwordApp = CreateObject("word.application")

MSwordApp.Visible = True

strwordfilename =
"v:\distcntr\dcdesign\projmgmt\Contract\Short_Forms\Short_FormB_Unprotected.doc"

Set MSwordDoc = MSwordApp.Documents.Open(strwordfilename)

savewordfilename = "v:\distcntr\dcdesign\projmgmt\contract\Short_Forms\" &
Forms!frmShortForm.cboDCNumber & Forms!frmShortForm.txtVendor & ".doc"


MSwordApp.ActiveDocument.MailMerge.Destination = wdSendToNewDocument

MSwordDoc.SaveAs (savewordfilename)

selectme = MsgBox("Do you want to print or email the Short Form Contract?
YES to Print and NO to email", vbYesNo, "YES to Print or NO to email")

Select Case selectme
Case 6
MSwordDoc.PrintOut , , , , , , , , , , , True
Case 7
MSwordApp.ActiveDocument.SendMail
End Select
MSwordDoc.Close
MSwordApp.Quit
Set MSwordDoc = Nothing
Set MSwordApp = Nothing
DoCmd.SelectObject acForm, "frmShortForm"
DoCmd.Close
End Select
End Sub
Click to expand...
 
D

Derek Wittman

Steve/Joe,
Yes, it did help. However, I'm on the next 'step' and in a bit of a jam.
My .execute merges the form into a document called "Form Letters1". Well, I
want to close my main document (the one that executes the merge) and save the
new "Form Letters1" as savefilename. Since I'm doing this (as you can tell)
over a network, might it be quicker to close the document and change the
filename using the filesystem code? Or should I do the saveas in the code
below?

Thanks!
Derek


Joe Smith said:
According to the MSDN, right after thie following line:

MSwordApp.ActiveDocument.MailMerge.Destination = wdSendToNewDocument

you then need

MSwordApp.ActiveDocument.MailMerge.Execute

I guess that executes the mail merge.

Here's the link I used as reference

http://msdn.microsoft.com/library/d...y/en-us/office97/html/output/F1/D4/S5A9DB.asp

Hope that helps

Steve
Click to expand...
Derek Wittman said:
Good afternoon,
I am working on controlling a Mail Merge from within MS Access using the
Word references. I have my main document and my datasource already linked
to
it. What I cannot figure out is how to do the equivalent of Merging to a
New
Document by way of the VBA code.

I want to merge to the new document, then save the document, then print or
email (depending on what the user selects).

Any and all help is greatly appreciated! Thank you!

Derek

My code is:

Private Sub cmdSubmit_Click()
Dim MSwordApp As word.Application
Dim MSwordDoc As word.Document
Dim strwordfilename As String
Dim savewordfilename As String
Dim selectme As Integer

DoCmd.RunSQL "Delete * from tblShortFormData"

Select Case txtRemaining

Case Is > 0.01, Is < -0.01

MsgBox "The total percentage MUST equal 100%. Please use the
'Remaining' field to check your math.", vbOKOnly, "Check your
calcuations:"

Case Else
DoCmd.RunSQL "INSERT INTO tblShortFormData ([Store#], ProjectID,
ContractDate, Vendorname, Price, CompletionDate, WorkDesc, ProgPC1,
ProgPC2,
ProgPC3, ProgPC4, ProgPC5, ProgPC6, ProgPC7, ProgPC8, Term1, Term2, Term3,
Term4, Term5, Term6, Term7, Term8) SELECT forms!frmShortForm.cboDCNumber,
Forms!frmShortForm.txtProjectNo, date(), forms!frmShortForm.txtVendor,
forms!frmShortForm.txtPrice, forms!frmShortForm.txtDone,
forms!frmShortForm.txtScope, forms!frmShortForm.txtPC1,
forms!frmShortForm.txtPC2, forms!frmShortForm.txtPC3,
forms!frmShortForm.txtPC4, forms!frmShortForm.txtPC5,
forms!frmShortForm.txtPC6, forms!frmShortForm.txtPC7,
forms!frmShortForm.txtPC8, forms!frmShortForm.txtTerm1,
forms!frmShortForm.txtTerm2, forms!frmShortForm.txtTerm3,
forms!frmShortForm.txtTerm4, forms!frmShortForm.txtTerm5,
forms!frmShortForm.txtTerm6, forms!frmShortForm.txtTerm7,
forms!frmShortForm.txtTerm8"

Set MSwordApp = CreateObject("word.application")

MSwordApp.Visible = True

strwordfilename =
"v:\distcntr\dcdesign\projmgmt\Contract\Short_Forms\Short_FormB_Unprotected.doc"

Set MSwordDoc = MSwordApp.Documents.Open(strwordfilename)

savewordfilename = "v:\distcntr\dcdesign\projmgmt\contract\Short_Forms\" &
Forms!frmShortForm.cboDCNumber & Forms!frmShortForm.txtVendor & ".doc"


MSwordApp.ActiveDocument.MailMerge.Destination = wdSendToNewDocument

MSwordDoc.SaveAs (savewordfilename)

selectme = MsgBox("Do you want to print or email the Short Form Contract?
YES to Print and NO to email", vbYesNo, "YES to Print or NO to email")

Select Case selectme
Case 6
MSwordDoc.PrintOut , , , , , , , , , , , True
Case 7
MSwordApp.ActiveDocument.SendMail
End Select
MSwordDoc.Close
MSwordApp.Quit
Set MSwordDoc = Nothing
Set MSwordApp = Nothing
DoCmd.SelectObject acForm, "frmShortForm"
DoCmd.Close
End Select
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Specify Word saving folder from access mail merge 9
Long qu about access to word mail merge coding 0
Access list box merged with Word 0
Problem with format when printing to Word from Access 2
A way to mail merge from excel into a word label template? 0
Automation: Sending data from Access to Word 3
Mail Merge to Word -Problem with Access Form Code... 1
Open a Word Template from Access 6

Top