Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! Macro to send email from Excel and add other attachments | Excel Programming | |||
Send an email, some with/without attachments | Excel Discussion (Misc queries) | |||
Auto Send email from IF command | Excel Worksheet Functions | |||
Can Excel auto-send an email on a given date? | Excel Discussion (Misc queries) | |||
how can i send email with attachments paths (3 workbooks) located in the cell a1 to a3 | Excel Programming |