Inciteful
asked on
How to use a letterhead to prepare a memo from data in an excel sheet?
Expert,
I am using excel to input some data and prepare a memo in word from the input data. i am NOT looking to mail merge. i am coding it in excel vba instead.
I am able to most of the above, and able to get my output onto a word document.
....however, what i want the code to do is open a standard document with the company letterhead (in the same folder containing the workbook) and print the same information onto that. How can this be done?
thanks in advance!
I am using excel to input some data and prepare a memo in word from the input data. i am NOT looking to mail merge. i am coding it in excel vba instead.
I am able to most of the above, and able to get my output onto a word document.
....however, what i want the code to do is open a standard document with the company letterhead (in the same folder containing the workbook) and print the same information onto that. How can this be done?
thanks in advance!
ASKER
Could you please elaborate. I still cant get it to load...
thanks!
thanks!
ASKER
I am indeed looking to use a template..the code starts dumping in all the contents onto that template.
Show your code and I'll try to plug my line into it.
ASKER
here you go...
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 SaveAsName As String
Set WordApp = CreateObject("Word.Application")
SaveAsName = ThisWorkbook.Path & "\" & Start.clientname & ".doc"
With WordApp
.Documents.Add
With .Selection
.Font.Size = 18
.Font.Bold = False
.ParagraphFormat.Alignment = 0
.typetext "Memo " & vbCrLf & vbCrLf
.Font.Size = 12
.typetext "From: " & vbCrLf & Start.reviewer1 & vbCrLf & Start.reviewer2 & vbCrLf & Start.reviewer3 & vbCrLf & vbCrLf
.typetext "Date: " & Format$(Worksheets("questions").Range("g2"), "mmmm d, yyyy") & vbCrLf & vbCrLf
End With
End With
WordApp.Quit
Set WordApp = Nothing
MsgBox Records & " memos were created and saved in " & ThisWorkbook.Path
End Sub
I usually like to capture the document in an object and work directly with that. I have also added a SaveAs line
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 SaveAsName As String
Dim MyNewDoc as Word.Document
Set WordApp = CreateObject("Word.Application")
SaveAsName = ThisWorkbook.Path & "\" & Start.clientname & ".doc"
With WordApp
Set MyNewDoc = .Documents.Add(MyTEmplatepath)
With .Selection
.Font.Size = 18
.Font.Bold = False
.ParagraphFormat.Alignment = 0
.typetext "Memo " & vbCrLf & vbCrLf
.Font.Size = 12
.typetext "From: " & vbCrLf & Start.reviewer1 & vbCrLf & Start.reviewer2 & vbCrLf & Start.reviewer3 & vbCrLf & vbCrLf
.typetext "Date: " & Format$(Worksheets("questions").Range("g2"), "mmmm d, yyyy") & vbCrLf & vbCrLf
End With
End With
MyNewDoc.SaveAs SaveAsName
'MyNewDoc.Close
WordApp.Quit
Set WordApp = Nothing
MsgBox Records & " memos were created and saved in " & ThisWorkbook.Path
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot man! adds a lot of character to the the program :)
ASKER
thanks for your help!
just one more thing....
the document is saved using the template as it's starting point..however the header disappears off of the first page..and just appears on the second and third pages..would you know what's up with that?
just one more thing....
the document is saved using the template as it's starting point..however the header disappears off of the first page..and just appears on the second and third pages..would you know what's up with that?
ASKER
thats ok...i figured it out :)
To create you new document just do:
Set MyNewDoc = wdApplication.Documents.Ad