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!

GrahamSkan

It sounds as if you need your 'standard' document to be a template. You can turn any document into a template by saving it as such.

To create you new document just do:

Set MyNewDoc = wdApplication.Documents.Add(MyTEmplatepath)

Inciteful

ASKER

Could you please elaborate. I still cant get it to load...
thanks!

Inciteful

ASKER

I am indeed looking to use a template..the code starts dumping in all the contents onto that template.

GrahamSkan

Show your code and I'll try to plug my line into it.

Inciteful

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

Open in new window

GrahamSkan

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

Open in new window

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Inciteful

ASKER

Thanks a lot man! adds a lot of character to the the program :)

Inciteful

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?

Inciteful

ASKER

thats ok...i figured it out :)