Auto send spreadsheets as email attachments

[JDaywalt]

I have a list of names in cells A1:A10 on a sheet tab called 'Names'. On
another sheet tab called 'Data', I have a range of data with an autofilter,
where column A includes these names, and columns B thru Z contains other
data. What I'd like to do is write code that will take each name on the
list, filter the data for that name, then automatically send the filtered
data as an email attachment to that person. It would then cycle through
until the whole list is completed. Can this be done?

 

[Tom Ogilvy]

sub ProcessData()
Dim cell as Range, bk as Workbook
Dim rng as Range, bNewSheet as Boolean
for each cell in worksheets("Names").Range("A1:A10")
if not bNewSheet then
set bk = Workbook.Add(Template:=xlWBATWorksheet)
bNewSheet = True
end if
With worksheets("Data")
bk.worksheets(1).Cells.Clear
set rng = .Autofilter.Range
.Autofilter Field:=1, Criteria1:=cell.Value
rng.copy bk.Worksheets(1).Range("A1")
bk.Sendmail Recipients:=cell.Value, Subject:="Your Data"
End With
Next
bk.Close Savechanges:=False
End Sub

You might have to mess with this:

Recipients:=cell.Value

to make sure it is a valid email address. maybe put the email address in
column B of names then

Recipients:=cell.Offset(0,1).Value

The above code is untested and may contain typos or require tweaking.