excel to paste to word then attach word document to an email

Hi,

Basically I'm trying to create a form in excel which can be filled in by the user, the content of the form isn't an issue and I am able to tell my macro to paste the excel data into a word document but now I want the same macro to attach the active word document to an email which I can then add body and subject too, again via the macro, I have previously been able to do an email macro but I cannot figure out how to combine them both, can anyone help, here is what I've got so far:

Excel to word macro: (it currently saves it to my documents)

Sub ControlWord()
    Dim appWD As Word.Application
    ' Create a new instance of Word & make it visible
    Set appWD = CreateObject("Word.Application.8")
    appWD.Visible = True

        Sheets("Template").Select
        Range("A1:F15").Copy
        ' Tell Word to create a new document
        appWD.Documents.Add
        ' Tell Word to paste the contents of the clipboard into the new document
        appWD.Selection.paste
        ' Save the new document with a sequential file name
        appWD.ActiveDocument.SaveAs Filename:="File" & i
        ' Close this new word document
        appWD.ActiveDocument.Close

    ' Close the Word application
    appWD.Quit
End Sub

Then this one creates a blank email with the suvject and body that I want:

 Dim Email_Subject, Email_Send_From, Email_Send_To, _
 Email_Cc, Email_Bcc, Email_Body As String
 Dim Mail_Object, Mail_single As Variant
 Email_Subject = "The subject of the email goes in here"
 Email_Send_From = ""
 Email_Send_To = "Randon@email.com"
 Email_Cc = ""
 Email_Bcc = ""
 Email_Body = "The body of the email would go in here"
 Set Mail_Object = CreateObject("Outlook.Application")
 Set Mail_single = Mail_Object.CreateItem(0)
 With Mail_single
 .Subject = Email_Subject
 .To = Email_Send_To
 .cc = Email_Cc
 .BCC = Email_Bcc
 .Body = Email_Body
 .send
 End With
debugs:
 If Err.Description <> "" Then MsgBox Err.Description

How do a smush these two together in one beautifully created macro?

Originally Posted by macropod

Whatever you want to include in the document from the Excel workbook, you either have to copy & paste from there or insert via another method. On reviewing the code I posted, it seems a line got dropped out somewhere along the way. You should insert:
Sheets("Template").Range("A1:F15").Copy
immediately before or after:
Set wdDoc = wdApp.Documents.Add()
Given that you're already copying Row 1, I'd have thought that would include whatever 'header' data you want.

PS: The code provides, too, for you to pass your i variable to it; it's nowhere specified in the code you posted, so I have no idea where you're getting it from.

Thanks, yeah I think I left my i variable in by mistake, I chopped it from some previous work I had done where the I stood for something lol

I'll let you know how I get on, that's for your help!

Hi Macropod,

I appear to be getting an error at the

Dim olApp As Outlook.Application, olMail As Outlook.MailItem

section, doesn't seem to want to progress past it and I keep getting a compile error, user defined type not defined.

Do you know what could be causing this?

Originally Posted by ahalliwell

Hi Macropod,

I appear to be getting an error at the

Dim olApp As Outlook.Application, olMail As Outlook.MailItem

section, doesn't seem to want to progress past it and I keep getting a compile error, user defined type not defined.

Do you know what could be causing this?

ignore me

I didn't reference outlook, doh!!