E-Mailing from Excel

[John]

I am trying to e-mail via code from Excel using a combination of Outlook
Express and Outlook. I have read Ron De Bruins excellent site but I have 2
things I want to incorporate and I can't see anything within the site, at
least not on the OE examples

1) I want to e-mail a workbook to email addresses that reside in AA1 and AA2
2) I want the Subject line of the e-mail to be whats in AA3


Thanks

 

[Ron de Bruin]

Hi John

See the Tips link on each example page
http://www.rondebruin.nl/mail/tips1.htm

 

[John]

Thanks Ron, exactly what I need, except now I have 2 problems

1) Excel97 crashes out with an exception error each time I run below,
doesn't with ExcelXP though

2) On one ExcelXP machine I get a "method sendmail object" error on line
MyArr = Sheets("E-Schedule").Range("AG1:AG3"), but on another machine
running XP its fine - damn!


Sub Mail_Reports()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm")
Application.ScreenUpdating = False
Sheets("E-Schedule").Visible = True
Sheets("E-Import").Visible = True
Sheets(Array("E-Schedule", "E-Import")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""
MyArr = Sheets("E-Schedule").Range("AG1:AG3")
.SendMail MyArr, Sheets("E-Schedule").Range("AG4").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Sheets(Array("E-Schedule", "E-Import")).Visible = False
Sheets("Home").Select
Range("A1").Select
End Sub

 

[Ron de Bruin]

Hi John

I check out your problems this evening

 

[Ron de Bruin]

No problems for me in Excel 97
Is the simple example1 macro working on that machine
http://www.rondebruin.nl/mail/folder1/mail1.htm