How to Automatically Create Word Memos from Excel

[JJ]

I am trying to create a memo in word from excel. I have the following code in
the excel sheet:

Sub NewMemo()
Dim WordApp As Object

Set WordApp = CreateObject("Word.Application")

Set Data = Sheets("Sheet1").Range("A:A")
Message = Sheets("Sheet1").Range("Message")

Records = Application.CountA(Sheets("Sheet1").Range("A:A"))
For i = 1 To Records

Application.StatusBar = "Processing Record " & i

Region = Data.Cells(i, 1).Value
SalesNum = Data.Cells(i, 2).Value
SalesAmt = Format(Data.Cells(i, 3).Value, "#,000")

SaveAsName = ThisWorkbook.Path & "/" & Region & ".doc"

With WordApp
.Documents.Add
With .Selection
.FontSize = 14
.Font.Bold = True
.ParagraphFormat.Alignment = 1
.TypeText Text:="M E M O"
.Type Paragraph
.Type Paragraph
.Font.Size = 12
.ParagraphFormat.Alignment = 0
.Font.Bold = False
.TypeText Text:="Date:" & vbTab & Format(Date, "mm d, yyyy")
.TypeParagraph
.TypeText Text:="To:" & vbTab & Region & " Associate"
.TypeParagraph
.TypeText Text:="From:" & vbTab & Application.Uername
.TypeParagraph
.TypeParagraph
.TypeText Message
.TypeParagraph
.TypeParagraph
.TypeText Text:="Units Sold;" & vbTab & SalesNum
.TypeParagraph
.TypeText Text:="Amount:" & vbTab & Format(SalesAmt, "$#,##0")
End With
.ActiveDocument.SaveAs Filename:=SaveAsName
End With
Next i

WordApp.Quit
Set WordApp = Nothing

Appliation.StatusBar = ""
MsgBox Records & " created this memo in " & ThisWorkbook.Path
End Sub

 

[NickHK]

JJ,
Any reason that you have to use Word at all ?
Assuming that you must, can you use early binding and benefit from
Intellisense ?

NickHK