Copy four worksheets from one workbook into a new workbook.e-mail

[Guest]

Hello and thanks for any help in advance.

I Have been having a little difficulty coding the following.

I Have a workbook that creates a number of sheets. Each sheet is independent
with no formulas. They are all created programattically.

I Need the code to loop through the cells C2 to C17 on a sheet called
MainPage.
-I'm Quite happy with For Each concept.

These contain e-mail addresses.

I need to copy 4 work sheets called alpha, beta, gamma, zulu for arguments
sake from this main work book.

create a new work book. paste these sheets into this book. Then e-mail to
the addresses in the range above. I quite happy to limit this to send mail as
no content is needed to the e-mail. Automation can wait till later.

Also Can I ask on an independent issue If you use the RowSource property for
a list box is it a known problem that refrencing worksheets that have names
with spaces is a problem. I Have noticed that using a global range variable
can overcome this within the workbook.

Regards

Francis

 

[Tom Ogilvy]

Dim rng as Range
Dim v as Variant
With Workbooks("ABC.xls")
set rng = .Worksheets("MainPage").Range("C2:C17")
End With
v = Application.Transpose(rng.value)
Worksheets(Array("alpha", "beta", "gamma", "zulu")).Copy
ActiveWorkbook.Sendmail Recipients:=v, Subject:="Current Status"
ActiveWorkbook.Close SaveChanges:=False