Add UserForm to Control ‘Go to Record’ on Mail Merge Toolbar

[ryguy7272]

I’m starting a new post; I think my previous post was going off on a tangent
that was not really where I wanted to go.

Below is the code that was generated by recording a macro in word, while
searching for a specific Excel file (Contacts.xls), to do a basic Mail Merge.


Sub Macro2()

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"J:\Contacts.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=J:\Contacts.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase
Password="""";Jet OLEDB:Engin" _
, SQLStatement:="SELECT * FROM `Contacts`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub

This works fine. I am now wondering if I can customize it a little by
adding a feature that prompts the user for an input, perhaps through a
UserForm, to find the client address that will be included on a specific form
letter. We have all clients listed in the Excel file and each is identified
by ID number. I can control which address is input through the Mail Merge
toolbar, by using the ‘Go to Record’. I just want to personalize it a little
more by adding a UserForm that pops up, prompts the user for a number, and
this loads into the ‘Go to Record’.

Can this be done?

Regards,
Ryan---

 

[Doug Robbins - Word MVP]

See the article "Load a ListBox from a Named Range in Excel using DAO" at:

http://www.word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Russ]

If you are going to query for one number than what I advised you before
would be sufficient. Use an InputBox.
"Use an inputbox(See Word VBA Help) to get the number from the user."

 

[RyGuy]

Doug, thanks for the link!! This is an awesome idea! I got the data to load
into the ListBox, and I added a CommandButton, but I can’t seem to get the
items selected in the ListBox to load into the word document.

I added the following line of text:
AddressBlock = db

This causes a Run-Time error '3420' and the message says, 'Object invalid or
no longer set.' I got AddressBlock from insert > Field > AddressBlock, and I
though this was required to identify the place that the address will be
linked to. Any ideas about what may be missing? I’d like to get this
working soon.


Russ, I like your idea very much! I’m going to have to research this
further. Do you know of any links that may be helpful for getting this set
up and working?

Thanks again guys!
Ryan--

 

[Doug Robbins - Word MVP]

You use the .BoundColumn attribute of the list box to get hold of the data
from each column of the selected record.

The following bit of code constructs an "address block" and inserts it into
the range of a bookmark in the document,

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm1.Hide
End Sub

If you want to scatter the information throughout the document, I would
write the information from each column to a document variable and use
{DOCVARIABLE } fields in the document to display the information

With ActiveDocument
Listbox1.BoundColumn=1
.Variables("varname1").Value = Listbox1
ListBox1.BoundColumn=2
.Variables("varname2").value = Listbox2
'etc
.Range.Fields.Update
End With



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[ryguy7272]

Unreal!!!!!!! This code does exactly what I want. I will have to modify my
source data a bit, but I’m very close to being done with this project now.
Thanks Doug!! Please tell me where I can learn more about this stuff. I
know Excel pretty well, but I don’t know much about integrating Excel with
Word (and vice versa). Where did you learn about this code? I would really
appreciate it if you can recommend a good book, or a few good URLs (besides
the one you gave me already). Hope you have a great Labor Day Weekend (I
know I will because this project is almost done now).

Kind regards,
Ryan---

 

[Doug Robbins - Word MVP]

To expand your knowledge, browse around the other pages at the Word MVP site
to which I refered you earlier. The only other reference sources that I
have are the VBA help file and using the Advanced Groups Search at Google
Groups

http://groups.google.com/groups/dir?sel=33606782&expand=1

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[ryguy7272]

I bookmarked it!
Thanks again!!
Ryan--
--
RyGuy

To expand your knowledge, browse around the other pages at the Word MVP site
to which I refered you earlier. The only other reference sources that I
have are the VBA help file and using the Advanced Groups Search at Google
Groups

http://groups.google.com/groups/dir?sel=33606782&expand=1

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP