Macro to create and save a word document with text

[diogene]

Hi everyone,

I would need some help: I have an excel spreadsheet which, based on the name of my potential customers, automatically creates the text to insert in the letter I will send them. To further automatise the process, I would like to have a macro that takes the text in the second cell of column E (the text of my letter), creates a new word document, paste the text in it and saves the word document in a specified folder (possibly named as cell B2 + cell C2, with a space in the middle).

Is there anyone who can help me with this?

It would really be a life saver!

Many thanks

 

[Macropod]

What you're describing sounds like a macro driven mailmerge, for which see Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks thread at:
http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
or:
http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks

 

[diogene]

Not really, actually. I am not looking to modify the text of the email but just to create a word document containing the text from Excel, which will be later sent as an attachment (no need for that to be automated, I can send it, I just need the word docs to be created and saved in a folder).

 

[Macropod]

Your first post makes no mention of an email. You need to be clearer about what you're trying to achieve.

 

[Xo]

Hi diogene
Does not do exactly what you asked for but its not difficult to change so that text, adresses etc are grabbed from a sheet instead of the simple lines in my example below.

Public Sub MailIT()
Dim applOL As Outlook.Application
Dim miOL As Outlook.MailItem
Dim wApp As Word.Application
Dim wDoc As Word.Document

    ' Lets create a doc with some simple content
    '
    Set wApp = CreateObject("Word.Application")
    wApp.Visible = True
    Set wDoc = wApp.Documents.Add
    wDoc.Content.InsertAfter "TEST TEXT IN WORD DOC"
    wDoc.SaveAs ("H:\MyNewWordDoc.doc")
    wDoc.Close
    wApp.Quit
    Set wDoc = Nothing
    Set wApp = Nothing


    ' Lets create an email, add attachement
    '
    Set applOL = New Outlook.Application
    Set miOL = applOL.CreateItem(olMailItem)

    With miOL
        .To = "target@targetsadress.se"
        .CC = ""
        .Subject = "TESTSUBJECT"
        .Body = "TEST BODY TEXT"
        .Attachments.Add "H:\MyNewWordDoc.doc"
        .Display
        '.Send
    End With
  
    Set applOL = Nothing
    Set miOL = Nothing
End Sub

Good luck