Email distribution list

S

Steve

Hello
How can I reference a distribution list Sheet1!A5:A15
using ActiveWorkbook.SendMail

Thanks
 
S

Steve

Thanks Ron I had overlooked that when browsing your site. However I am
getting a compile error. Here is my code

Sub Export()

Dim MyArr As Variant

ScreenUpdating = False
Worksheets("Forecast").Copy
ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" &
Range("B56") & ".xls"
ActiveWorkbook.SaveAs "\\c:\rforecast" & Range("B56") & ".xls"
'Dim MyArr As Variant
MyArr = Sheets("distribution").Range("a5:a6")
.SendMail MyArr, "Forecast"
ActiveSheet.Copy
ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\forecast" &
Range("B56") & ".csv", FileFormat:=xlCSV
ActiveWorkbook.Close SaveChanges:=False
ScreenUpdating = True

End Sub
 
R

Ron de Bruin

Hi

Worksheets("Forecast").Copy
You copy only 1 sheet to a new workbook

MyArr = Sheets("distribution").Range("a5:a6")
It can't find the sheet in the activeworkbook because the workbook
have only a sheet named Forecast

Use this
MyArr = Workbooks("test.xls").Sheets("distribution").Range("a5:a6")

Change the workbook name
 
S

Steve

"Worksheets("Forecast").Copy
You copy only 1 sheet to a new workbook"

What do you mean?
 
S

Steve

Changed as suggested
MyArr = Workbooks("mysheetname.xls").Sheets("distribution").Range("a5:a6")
.SendMail MyArr, "Forecast"
Still giving compile error invalid or unqulified....

thanks
 
R

Ron de Bruin

You copy the sheet Forecast into a new workbook with this line
Worksheets("Forecast").Copy

This is the Activeworbook now with 1 sheet
Sheets("distribution") is not in that workbook
 
S

Steve

Now also getting an error here (Method "SaveAs of object'_Workbook' failed)

ActiveWorkbook.SaveAs "\\c:\rforecast\lfcst" & Range("B56") & ".xls"
 
R

Ron de Bruin

Try this example

Sub Mail_Sheet()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant

strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Workbooks("mysheetname.xls").Sheets("Forecast").Copy
MyArr = Workbooks("mysheetname.xls").Sheets("distribution").Range("a5:a6")

Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub
 
Top