Make Memos Using Automation

[dunkyn]

I'm trying to modify some VBA to create automated memos.
I want to save the memos in one file...and if the file name already exists, save the new file on top of it.

Any thoughts or assistance is greatly appreciated.

TIA!!!


Option Explicit

Sub MakeMemos()
' Creates memos in word using Automation (late binding)
Dim WordApp As Object
Dim Data As Range, message As String
Dim Records As Integer, i As Integer
Dim AccountName As String, Officer As String, Mydate As String, AccountNum As String, Recipient As String, Address1 As String
Dim Address2 As String, Address3 As String, Address4 As String, Closing As String, LetterRe As String, SaveasName As String


' Start Word and create an object
Set WordApp = CreateObject("Word.Application&quot

' Information from worksheet
Set Data = Sheets("Data&quot.Range("A1&quot
message = Sheets("Letter&quot.Range("Message&quot
Officer = Sheets("Letter&quot.Range("AccountOfficer&quot
Mydate = Sheets("Letter&quot.Range("LetterDate&quot
Closing = Sheets("Letter&quot.Range("Closing&quot

' Cycle through all records in Sheet1
Records = Application.CountA(Sheets("Data&quot.Range("A:A&quot)
For i = 1 To Records
' Update status bar progress message
Application.StatusBar = "Processing Record " & i

' Assign current data to variables
AccountName = Data.Cells(i, 1).Value
AccountNum = Data.Cells(i, 2).Value
Recipient = Data.Cells(i, 3).Value
Address1 = Data.Cells(i, 4).Value
Address2 = Data.Cells(i, 5).Value
Address3 = Data.Cells(i, 6).Value
Address4 = Data.Cells(i, 7).Value
LetterRe = Data.Cells(i, 1).Value & " " & Data.Cells(i, 2).Value


' Send commands to Word
With WordApp
.Documents.Add
With .Selection
.Font.Size = 14
.Font.Bold = True
'.TopMargin=2"
.ParagraphFormat.Alignment = 0
.TypeText Text:="N O T I C E O F A N N U A L A C C O U N T I N G"
.typeparagraph
.typeparagraph
.Font.Size = 13
.ParagraphFormat.Alignment = 0
.Font.Bold = False
.TypeText Text:=Mydate
.typeparagraph
.typeparagraph
.TypeText Text:=Recipient
.typeparagraph
.TypeText Text:=Address1
.typeparagraph
.TypeText Text:=Address2
.typeparagraph
.TypeText Text:=Address3
.typeparagraph
.TypeText Text:=Address4
.typeparagraph
.typeparagraph
.TypeText Text:=LetterRe
.typeparagraph
.typeparagraph
.TypeText message
.typeparagraph
.typeparagraph
.TypeText Text:=Closing
.typeparagraph
.typeparagraph
.typeparagraph
.typeparagraph
.TypeText Text:=Officer
.typeparagraph
.typeparagraph
.typeparagraph
.typeparagraph
.typeparagraph
.Font.Size = 7
.TypeText Text:=Application.UserName
.TypeText Text:="Date:" & vbTab & _
Format(Date, "mmmmm, yyyy&quot

End With
End With
Next i


' Determine the file name
SaveasName = ThisWorkbook.Path & AccountName & "beneficiaries 2002.doc"
' ActiveDocument.AddtoRecentFiles:=True, SaveFormsData:=False




'Kill the object
WordApp.Quit
Set WordApp = Nothing

' Reset status bar
Application.StatusBar = ""
MsgBox Records & " memos were created and saved in " & ThisWorkbook.Path
End Sub

 

[Automaton]

Perhaps you could use the Shell command in VBA to trigger a DOS batch file. Pass the batch file the filename as an argument. If it already exists, DOS will be sure to paste over it with abandon... DOS is kinda unforgiving that way.

 

[dunkyn]

Thanks. Good idea.

Can you give me direction on where I might find some sample code to help with the programming? VBA is rather new to me. I understand what you are telling me, I just don't know the syntax.

Thank you very much.