What you propose sounds like mail merge. You can do all that you have mentioned using http://www.gmayor.com/ManyToOne.htm in one to one mode.
To create individual messages, using a macro to load your Outlook template and change 'Sirs' to say 'John' you must first decide where 'John' comes from. The substitution is the easy part of the exercise.
You must also decide whether the code is to be run from Outlook or Excel. Your code suggests Excel, but the forum is Outlook. If you want to do it from Outlook, you don't need to create an Outlook application as you are already in one., so you would use the following (I have changed some paths for testing, you must insert the correct paths).
Sub CreateAMessage()
Const CustomerName As String = "Dear John"
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object
Dim OutMail As Outlook.MailItem
Set OutMail = CreateItemFromTemplate("C:\Path\Test.oft")
With OutMail
.To = "someone@somewhere.com"
.Attachments.Add "c:\path\xyz.pdf"
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range
With oRng.Find
Do While .Execute(FindText:="Dear Sirs")
oRng.Text = CustomerName
Exit Do
Loop
End With
.Display
'.sEnd 'restore after testing
End With
lbl_Exit:
Set olInsp = Nothing
Set OutMail = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
Exit Sub
End Sub
If you are running from Excel, which would seem more logical then the code would be:
Sub CreateAMessage()
Const CustomerName As String = "Dear John"
Dim oOutlookApp As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object
Dim OutMail As Object
On Error Resume Next
'Get Outlook if it's running
Set oOutlookApp = GetObject(, "Outlook.Application")
'Outlook wasn't running, start it from code
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
End If
Set OutMail = oOutlookApp.CreateItemFromTemplate("C:\Path\Test.oft")
With OutMail
.To = "someone@somewhere.com"
.Attachments.Add "c:\path\xyz.pdf"
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range
With oRng.Find
Do While .Execute(FindText:="Dear Sirs")
oRng.Text = CustomerName
Exit Do
Loop
End With
.Display
'.sEnd 'restore after testing
End With
lbl_Exit:
Set oOutlookApp = Nothing
Set olInsp = Nothing
Set OutMail = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
Exit Sub
End Sub