Assuming that all of your information is in one Excel file.
I do this regularly using a mailmerge.
I have various templates with the mailmerge fields in them. There is an AutoNew macro in these templates that attaches the Excel file as a data source and calls up the built-in dialog to pick a record.
Code:
Sub AttachClients()
' Written by Charles Kenyon
' 19 April 2005 revised 15 December 2006
'
' Requires WorkGroupPath function
'
' Makes activedocument a mailmerge (letter) document and
' attaches Clients_Merge.xls from Parts folder of Workgroup Templates folder.
'
' Then displays search dialog and goes to client, makes sure merge info is
' displayed instead of merge codes.
'
On Error Resume Next
MergeFieldUnlockAllStory 'if merge fields locked, unlock
'
' Name of file
Dim strFileName As String
Dim strProvider As String
strFileName = WorkGroupPath & "Parts\Merge Data\Clients_Merge.xls"
'
' Attach Merge list
ActiveDocument.MailMerge.OpenDataSource strFileName, , , False, _
True, False, "", "", False, "", "", "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=strFileName;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:" _
, "SELECT * FROM `Clients$`", "", , wdMergeSubTypeAccess
'
' Show merge data
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
'
' Find client
Application.Dialogs(wdDialogMailMergeFindRecipient).Show
End Sub
The WorkgroupPath function gives a string for the Workgroup folder path.