Coping data from XL to Word Doc

[kklaus]

Below is my code to copy data from a worksheet to a Word Doc. The problem I'm having is that it seems to be copied as a picture or text box. Instead of it pulling in cells A1:F301 as stated in the code, only cells A1:F70 are showing in the .doc file.

My data takes up about 6 pages in Word so all the data is cut off after the 1st page.

Is there another way to copy it in to the .doc file so that it's not a text box or a better way to pull in rows through 301?

Also, can I set the margins for the .doc file in the code?

Sub Export_Word_Attach_Outlook() 
Dim source As Range 
Dim wdApp As Object 
Dim wdDoc As Object 
Dim strdate As String 
Dim OutApp As Object 
Dim OutMail As Object 
Dim strbody As String 

'Create a hidden instance of Word 
Set wdApp = CreateObject("Word.Application") 
'Add a document. 
Set wdDoc = wdApp.Documents.Add 
  
With ActiveSheet 
Set source = .Range("A1:F301").SpecialCells(xlCellTypeVisible) 
source.Copy 
End With 

'Here's where I named the .doc file from a cell on the sheet 
strname = ThisWorkbook.Sheets("Quote Letter").Range("F10").Value & " Quote Letter" 

With wdDoc 
'Paste the source-range into the active document 
.Range.PasteSpecial Link:=False, DataType:=3, _ 
Placement:=0, DisplayAsIcon:=False 

'Save & Close the document 
.SaveAs ThisWorkbook.Path & "\" & strname & ".doc" 
.Close 
End With 

'Close the hidden instance of Word 
wdApp.Quit 

Application.CutCopyMode = False 

'Here's where I'm emailing the .doc
'file as an attachment:
strbody = "Enter Text Here" 

Set OutApp = CreateObject("Outlook.Application") 
Set OutMail = OutApp.CreateItem(0) 
With OutMail 
.to = ThisWorkbook.Sheets("Quote Letter").Range("B16").Value 
.Subject = "Quote for " & ThisWorkbook.Sheets("Quote Letter").Range("F10").Value 
.Body = strbody 
.Attachments.Add ThisWorkbook.Path & "\" & strname & ".doc" 
.Display 
End With 

'Release objects from memory. 
Set wdDoc = Nothing 
Set wdApp = Nothing 
Set OutMail = Nothing 
Set OutApp = Nothing 

'Delete the Word-document. 
Kill ThisWorkbook.Path & "\" & strname & ".doc" 


End Sub

Thanks!
Kathy

 

[SkipVought]

Kathy,

You'ld be better off posting this in the VBA Forum Forum707.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[kklaus]

Thanks Skip!