mail merge-open data source 1

[hellohello1]

Hi,
I have a 2003 Word document that contains Mail Merge fields from an Excel document.

I am trying to make 2 buttons in Word.
-A button that opens the "Open Data Source" dialog box. (I will let the users browse to where they save the Excel file).
-A button that opens the "Mail Merge Recipients" dialog box. (I will let the user choose the recipients).

I was able to run a macro to get the code for the third button I need: a button that launchs the "Merge to New Document".

Is it also possible to delete those 3 buttons once the New document is created? The New document doesn't need the buttons, only the main mail merge document.

Also, I was trying to protect my mail merge document, but when I go to Tools, Protect Document, everything is grayed out.

Help!

Thanks,

 

[SkipVought]

Hi,

Is there a reason that you don't want to use the buttons that are already there in the MailMerge Toolbar?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hellohello1]

Hi Skip!

The Mail Merge toolbar buttons are kind of hard to see and not very obvious.

The excel file name should always be the same, but the path will vary depending on where users save the excel file.

I wanted to have 3 buttons at the top of my merge document:
Step 1: Open data source
Step 2: Select recipients
Step 3: Merge data

But then make those 3 buttons disappear on the newly created document.

Thanks

 

[SkipVought]

Then turn on your macro recorder and have at it. Post your recorded code for help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hellohello1]

I turned on the macro recorder, opened the 'Select Recipients' box, chose a name and clicked OK. Then I stopped the macro and went to look at it.

But the code is blank:
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 6/2/2009 by user
'
End Sub

Through trial and error and reading other internet posts, this is what I have for my button 1 (Open Data Source):

Private Sub cmdSelectDataSource_Click()
'select data source-step 1
Dim DataDoc As String
Dim MergeDoc As Document

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.Title = "Select data source and click OK"
.InitialFileName = "Rpt- Governance Reporting Projects.xls"
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
.Filters.Clear
.Filters.Add "Excel", "*.xl*", 1
If .Show <> -1 Then
MsgBox "Cancelled By User", , Title
Exit Sub
End If
DataDoc = fDialog.SelectedItems.Item(1)
End With
ActiveDocument.MailMerge.OpenDataSource Name:=DataDoc, ConfirmConversions:=False, Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SubType:=wdMergeSubTypeWord2000
End Sub

This is what I have for button 3 (Merge New Document)
Private Sub cmdMergeDocument_Click()
'merge document-step 3
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
Me.cmdSelectDataSource.Enabled = False
Me.cmdMergeDocument.Enabled = False
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Me.cmdSelectDataSource.Enabled = True
Me.cmdMergeDocument.Enabled = True
End Sub

I don't know how to hide the command buttons, but I disabled them.

So I still need help with creating a button to open the Choose Recipients dialog box. Also,I still need help with protecting the form.

Thanks!

 

[hellohello1]

I found how to show the Recipients dialog box!

It is simply:
WordBasic.MailMergeRecipients

So now I just need help protecting the form....

 

[hellohello1]

I'm halfway there by protecting my form:

When the document opens, I have:
Private Sub Document_Open()
'protect document
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="hi"
End Sub

Then for each of my 3 buttons (Open Data Source, Select Recipients and Merge Document), at the beginning of each of the 3 codes, i have:

'first check if the document if protected. If so, unprotect it.
If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect "hi"
End If

Then at the end of each of the 3 codes, I re-activate the protection:
'protect document again
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="hi"

This works fine except for my Merge Document command button. I need the newly created document to *not* be protected.

This is the code for my Merge Document button:
'first check if the document if protected. If so, unprotect it. Opening the document triggers code to protect it (see the Document_Open() event)
If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect "hi"
End If

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
Me.cmdSelectDataSource.Enabled = False
Me.cmdMergeDocument.Enabled = False
Me.cmdSelectRecipients.Enabled = False

With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Me.cmdSelectDataSource.Enabled = True
Me.cmdMergeDocument.Enabled = True
Me.cmdSelectRecipients.Enabled = True

'protect document again
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="hi"

Is there a way to specify the original document should be protected, not the newly created document?

Sorry for the long post.
Thanks,

 

[PHV]

Have you considered an UserForm instead ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hellohello1]

Thanks for writing.

I'm not too familiar with UserForms. My Merge Document is 20 pages long with over 100 merge fields, so I don't really want to re-create it...


All I need to know if how to specify the line below as the original document, not the newly created document.

ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="hi"

thanks

 

[PHV]

Do you know the difference between ActiveDocument and ThisDocument ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hellohello1]

I do now!! Thanks PHV.

It works perfectly. My original document is protected, but not my new document, which is what I wanted.

ThisDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="hi"

thank you thank youthank you thank youthank you thank youthank you thank youthank you thank youthank you thank youthank you thank youthank you thank youthank you thank youthank you thank youthank you thank youthank you thank you

 

[hellohello1]

Can I add ToolTips to my command buttons?

I don't see a ToolTip Property.

Is it because I'm not using a UserForm?