Peter;
After correcting a couple word wraps from the cut and paste, I'm on bended
knees to you. Thank you very much, it works like a charm. Now all I need is
someone to help me get up off my knees. g
--
Regards
Michael Koerner
"Peter Jamieson" wrote in message
...
See how you get on with this (it's a completely separate process from
the last one)
FWIW I try to keep the amount of "coding to order" I do to an absolute
minimum. This is more than I've done for some time.
Sub SelectPhoneNumberAndMerge()
' error trapping to be added
Dim bDone As Boolean
Dim objMMMD As Word.Document
Dim strColumnName As String
Dim lngCount As Long
Dim strSheetName As String
Dim strPhoneNumber As String
bDone = False
strPhoneNumber = ""
Do
strPhoneNumber = InputBox("Enter the phone number -" & _
" only use 0-9, '-' and '+'," & _
" or blank to quit", _
"Phone number", strStartLetter)
strPhoneNumber = Replace(UCase(Trim(strPhoneNumber)), " ", "")
If Len(strPhoneNumber) = 0 Then
bDone = True
Else
If invalidPhoneNumber(strPhoneNumber) Then
MsgBox "Don't put anything except 0-9," & _
"'-' and '+' in the phone number", _
vbOKOnly
Else
' Set this to the name of the worksheet or to the range name
strSheetName = "Nominal Roll$"
' Set this to the exact name of the column containing the phone
number
' (upper/lower case is probably significant
strColumnName = "PhoneNumber"
Set objMMMD = ActiveDocument
With objMMMD.MailMerge
' I would like to use count(*) to get the
' record count but cannot make it work
.DataSource.QueryString = _
" SELECT *" & _
" FROM [" & strSheetName & "]" & _
" WHERE " & CStr(strColumnName) & " like '" & _
strPhoneNumber & "%'"
.DataSource.ActiveRecord = wdLastRecord
lngCount = .DataSource.ActiveRecord
End With
Set objMMMD = Nothing
Select Case lngCount
Case 0
MsgBox "No records matched the number you entered", vbOKOnly
Case 1 ' OK
bDone = True
Case Else
MsgBox "More than 1 record matched the number you entered",
vbOKOnly
End Select
End If
End If
Loop Until bDone
If strPhoneNumber "" Then
Set objMMMD = ActiveDocument
With objMMMD.MailMerge
' remove or change this as necessary
.Destination = wdSendToNewDocument
.Execute Pause:=False
End With
Set objMMMD = Nothing
End If
End Sub
Function invalidPhoneNumber(ByRef strPhone As String) As Boolean
' Shouldn't really return a parameter
' using byref in a function but
' there you go
Dim c As Long
Dim s As String
s = ""
invalidPhoneNumber = False
For c = 1 To Len(strPhone)
Select Case Mid(strPhone, c, 1)
Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "+", "-"
s = s & Mid(strPhone, c, 1)
Case Else
invalidPhoneNumber = True
End Select
Next
strPhone = s
End Function
Peter Jamieson
http://tips.pjmsn.me.uk
Visit Londinium at
http://www.ralphwatson.tv
Michael Koerner wrote:
Peter;
The phone number is only in one data source which is in this case is Excel
Col " O" with a header row called PhoneNumber and appear as xxx-xxx-xxxx