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.Range.Paste
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
'.Attachments.Add
.Display 'or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub