Using VBA to automate printing with mail merge

[meysha]

Hi Everyone,

I'm a real novice, but thanks to the knowledge on these forums I've been able to create a spreadsheet to manage the volunteers at my work. It's very basic but uses a UserForm to input data on the sheet. This data is mainly contact info and what they are involved with, what training they have done and what documentatio we have (have they signed the code of conduct etc). All these are checkboxes...

I've been pretty lucky and have coded to be able "Find A volunteer" which then fills the user form to edit a volunteers details and then save updates etc.

This is working really well...but my next issue is I need to be able to print..something usable. So I devised, what I thought was fool proof, but alas something goes wrong.

I thought I'll see up a word document that looks something like the Userform in terms of design (with IF to show a checkbox for all the activities/training etc). I'll then mail merge with the data from the spreadsheet...this gives me something to print!!

This works really well when I set the mail merge up through word. I can scroll through and see all the volunteers, all the IF fields work well with the mail merge. It looks good and it works...

Now the problem is I'm not the end user of this "system"...the people who will use are very digital unsavvy. So I want to code a "Print" function on the userform that..
1.Saves the update (as I foresee users will forget to click save update before clicking print
2.Opens the word document that i've set up (i've heard this should be a template, but I'm not sure why etc...Need a simple english explanation)
3. Opens the source data for the mail merge
4. Select the correct entry
5.PrintPreview or Straight to print...(Does that print up the print dialog box?)

I've had some other thoughts, such as, selecting the correct entry might be tricky, so how about the print function doing a save update, then saving to a duplicate sheet with headings (so there is only one entry) then using that to mail merge...

I'm having some major problems with the coding...
I was doing what I found on this and other forums with things like

dim wdApp As Application.Word
dim wdDoc As Word.Application
 
wdApp.Documents.OpenFileName = ("F:\PrintVolunteerInformation.doc")
Mailmerge.OpenDataSource = ("F:\Volunteer Information.xls")

N.B - This obviously isn't from my document, I've come home from work, but this is still bugging me and I don't have remote access...

FYI - When my initial OpenData Source stuff wasnt' working, I recorded a macro in Word to see what it came up with....I then copied this into my Excel VB but it still didn't work...

I can get the document to open, but it doesn't seem connected with the data for mailmerge (can't scroll through the volunteer records)

Please help !! - Am I going down the wrong path? Could copying to a new sheet help with the selecting one record.. Once all that - how do I code to print the result? The more I've thought about it since writing this post the less I think we need print preview, just the print dialog box to come up so we can select the colour printer..

You're help is really appreciated! Thank you!
Meysha

 

[meysha]

I've done some more macro recording to look at things like swithing between entries. Here is the code

Private Sub CommandButton1_Click()
ThisWorkbook.Save
Dim wdApp As Word.Application
Dim WordWasNotRunning As Boolean
Dim wdDoc As Word.Document
'Get existing instance of Word if it's open; otherwise create a new one
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err Then
    Set wdApp = New Word.Application
    WordWasNotRunning = True
End If
wdApp.Visible = True
wdDoc.OpenFileName ("F:\PrintVolunteerInformation.doc")
    wdDoc.MailMerge.MainDocumentType = wdFormLetters
    wdDoc.MailMerge.OpenDataSource Name:= _
        "F:\Volunteer Information.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=F:\Volunteer Information.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Pa" _
        , SQLStatement:="SELECT * FROM `Data$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
    ActiveDocument.MailMerge.DataSource.ActiveRecord = RowNumber.Value - 1
    ActiveDocument.PrintPreview
 
 
End Sub

This code opens word, but doesn't open any document...Could it be something weird with spaces in the filename?

Also this section

    ActiveDocument.MailMerge.DataSource.ActiveRecord = RowNumber.Value - 1

RowNumber is the text box which displays the row number of the entry (this is their local volunteer ID)....But because mail merge uses row 1 as the headers, my local ID numbers are off by one for the printing...

This has brought me back to work on the weekend. Sigh.
Meysha