0

I have an excel workbook that contains columns for name and a decision. If the value of the decision column equals "keep", I want to create a macro that auto-populates the name into a word template that will eventually become a letter mailed to somebody. Here is the table and my code so far:

Name Decision
John Doe Keep
Jane Dow Remove 
Sub letter()

Dim rng As Range
Dim row As Range
Dim cell As Range
Dim i As Integer
Set wApp = CreateObject("Word.Application")
wApp.Visible = True


Set wDoc = wApp.Documents.Add(Template:="file name here", NewTemplate:=False, DocumentType:=0)
ActiveSheet.Select
For i = 2 To 3
    If Cells(i, 3) = "Keep" Then
    
With wDoc
    .Application.Selection.Find.Text = "..name.."
    .Application.Selection.Find.Execute
    .Application.Selection = Cells(i, 1)
    .Application.Selection.EndOf
    
 ChangeFileOpenDirectory "Directory goes here"
 ActiveDocument.SaveAs2 Filename:="file name here" _
        , FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
        AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
        EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
        :=False, SaveAsAOCELetter:=False, CompatibilityMode:=15
End With
End If
Next i
End Sub

The excel workbook looks something like this:

For each row, if the 3rd column value equals "keep", I would like a letter to be generated and saved as "letter_customername.docx", where "customer name" is autopopulated based on the workbook. Additionally, I would like the letter to begin with "Dear 'Customer Name'", where the customer name is again generated from the workbook. What I have as a template is something like this:

Dear ..Name..

Body

Closing

I am envisioning a macro where "..name.." is replaced with the customer name from the workbook.

The code above does not seem to accomplish this. I am stuck and not sure how to proceed. Any assistance would be greatly appreciated!

Note my code is borrowed from here

BigBen
  • 46,229
  • 7
  • 24
  • 40
z_11122
  • 153
  • 8
  • 1
    Might be more straightforward to use bookmarks to locate the text to be replaced. Eg see https://stackoverflow.com/a/68564550/478884 – Tim Williams Aug 18 '22 at 20:41
  • 1
    Why not to create a template word document, replace the necesary stuff and then save as? It would be so much easier than creating it from scratch every time, the logic would be, open up the document, replace what you need by customer and then save as elsewhere, leaving the template always ready for the next one. PS: this is what I personally do with mailing for example. Note that when I say template I don't refer to the office function template, I am speaking about having a regular document that gets opened, replaces the stuff and then it's saved as – Sgdva Aug 18 '22 at 22:01
  • @Sgdva That is exactly what I want. I am new to VBA so I'm not sure I know how I can do this. My code above does do that I believe- it takes a template and switches the fields. The problem is that I can't figure out how to change the field names. – z_11122 Aug 19 '22 at 12:09
  • @TimWilliams That seems to be working, but I am having issues. I have written the code similar to the example, but when I run the macro, a lot of the text gets removed. Any thoughts? Would it be better to ask a new question? – z_11122 Aug 19 '22 at 16:26
  • Yes - post a new question with your current code and an example of what's going wrong when you run it. – Tim Williams Aug 19 '22 at 16:34

0 Answers0