Auto send spreadsheets as email attachments
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? |
Auto send spreadsheets as email attachments
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. -- regards, Tom Ogilvy "JDaywalt" wrote: 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? |
All times are GMT +1. The time now is 12:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com