Err sending WD emailthru XL VBA-F8 OK, F5 not??

E

Ed

I'm using the following code in Excel VBA to create a Word doc, paste
in an Excel range copied as Picture, and save and send the doc as an
attachment. Because of our email system, it must go as an attachment.

When I step through with F8, everything is fine. When I use F5,
though, I get down to the PageSetup lines and get this error:

Run Time Error 462
The remote server machine does not exist or is unavailable.

If anyone has any suggestions or a better method, I would appreciate
all input.

Thank you.
Ed

(PS - Yes, a real email address was used in the test and went through
fine - with F8, that is.)

**********************************

Sub SendUpdate()

Dim wb As Workbook
Dim wk As Worksheet
Dim rng As Range
Dim x As Long, y As Long

Set wb = ActiveWorkbook
Set wk = wb.Sheets(1)

y = wk.Range("E65536").End(xlUp).Row

x = 20
Do
x = x + 1
wk.Range("G" & x).Select
If wk.Range("G" & x).Text <> "" Then Exit Do
Loop

ActiveWindow.ScrollRow = x

wk.Range("A16:J" & y).Select
Set rng = Selection
rng.CopyPicture

Dim WD As Word.Application
Dim doc As Word.Document
Dim WDrng As Word.Range
Set WD = New Word.Application
Set doc = WD.Documents.Add
doc.ActiveWindow.Visible = True

With doc.PageSetup
.Orientation = wdOrientLandscape
.TopMargin = InchesToPoints(0.75)
.BottomMargin = InchesToPoints(0.75)
.LeftMargin = InchesToPoints(0.5)
.RightMargin = InchesToPoints(0.5)
End With

doc.Content = "These due as of " & _
Format(Date, "dd-mmm-yy") & "; " & _
Format(Time, "hhmm") & " MST" & vbCrLf & vbCrLf

Set WDrng = doc.Paragraphs(doc.Paragraphs.Count).Range
WDrng.Paste

doc.SaveAs "C:\test.doc"

' Turn off spell check to avoid errors
doc.SpellingChecked = True

doc.HasRoutingSlip = True
With doc.RoutingSlip
.Subject = "Update"
.message = "Here is the current status."
.AddRecipient "(e-mail address removed)"
.Delivery = wdAllAtOnce
End With
doc.Route

doc.Save
doc.Close
WD.Quit

wk.Range("C21").Select

End Sub
 
T

tylagusim

Ed,
Sounds like a timing issue between Word and Excel -- something I've
found in similar situations. I'm wondering putting the 'With
doc.PageSetup' after the "WDrng.Paste" code would help. They seem
independent operations, and in reverse order Word would (or should)
have fully instantiated things by the time you get to doing
the .PageSetup.
Just a thought.

/ Tyla /
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top