Output Multiple Reports to One Word File Automatically
You have a list of reports you have to send to a customer. This has to be done every so often and takes too long. As well as that the reports need to be in word format, which takes even more time, and there are 50 of them.
Create a new form. On this form put a list box called LIST0. Either fill the list box via code (see
List all Reports example) or from a table via a query. Create a button called SENDIT. Go into the code for the form by clicking the code button on the toolbar and paste the following code.
|Private Sub SENDIT_Click()
On Error GoTo ERRORHANDLER
Dim varItem As Variant
If List0.ItemsSelected.Count>0 Then
Dim AppWord As New Word.Application
Dim DocWrd As Word.Document
Dim i As Integer
Dim Progress as String
Dim EventTitle as String
AppWord.Visible = True
Set DocWrd= AppWord.Documents.Add
DocWrd.PageSetup.TopMargin = 36
DocWrd.PageSetup.BottomMargin = 36
DocWrd.PageSetup.LeftMargin = 36
DocWrd.PageSetup.RightMargin = 18
i = 0
For Each varItem In List0.ItemsSelected
i = i + 1
Progress = "Processing... " & List0.ItemData(varItem)
DoCmd.OutputTo acOutputReport, List0.Column(0, varItem), acFormatRTF,
"c:\temp\" & List0.ItemData(varItem) & ".rtf",
AppWord.Selection.InsertFile "c:\temp\" & List0.ItemData(varItem)
& ".rtf", "", False, False, False
If i < List0.ItemsSelected.Count Then
Progress = "Generating Email"
& EventTitle & ".doc", wdFormatDocument
="Set the title of the combined reports - " & Date
AppWord.Options.SendMailAttach = True
Set DocWrd = Nothing
Set AppWord = Nothing
Progress = ""
Progress = ""
If MsgBox("Do you want to start over?", vbCritical
+ vbYesNo) = vbYes Then
Save the form and run it. Select the
reports you want to send in the list and click the button. All the reports will
be run and joined into one nice long word document. Then bring up your mail client with the document attached.