Microsoft Excel - VBA Coding for Copying data from excel sheet to word document and then attaching to mail

Asked By Paul on 04-Apr-13 11:59 AM
I have a code below which copies data from an excel worksheet to a new word document saves the document and generates an email. the only problem i am getting is trying to adjust the size of the pasted image in word which i need to adjust the scale so all the data shows, and i cant seem to find the code which will attach the file to the email. i dont mind a new code, but the copying of data needs to be so that it all fits on one page in word, Thanks

Sub PasteToWord()
    Dim wdApp  As Object
    Dim wd   As Object
    Dim sFil As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
      Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    Set wd = wdApp.Documents.Add ' create a new document
    wdApp.Visible = True
    sFil = Range("c5").Value
    Range("A1:n196").CopyPicture xlScreen, xlPicture

wd.SaveAs ThisWorkbook.Path & Application.PathSeparator & sFil & "ASM Monthly Report" & ".doc"

 Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Hi there" & vbNewLine & vbNewLine & _
        "Cell A1 is changed" & vbNewLine & _
        "This is line 2" & vbNewLine & _
        "This is line 3" & vbNewLine & _
        "This is line 4"

    On Error Resume Next
    With OutMail
      .To = ""
      .CC = ""
      .BCC = ""
      .Subject = "This is the Subject line"
      .Body = strbody
.Display           'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

Harry Boughen replied to Paul on 04-Apr-13 05:19 PM
Hi Paul,
Something like:

    ActiveDocument.Shapes("Object 3").Select
    Selection.ShapeRange.Height = 185.1
    Selection.ShapeRange.Width = 293.65

can be used to resize an object in a word document.  You will need to know/name the object and the pixel sizes that you want to adjust it to.