Loop + Data validation list + e-mail problem

Hi all,

I've got a spreadsheet which I want to e-mail extracts out to people in a list.

I was thinking of running a advance filter on the field but not sure how to loop through all the names from a data validation list in A5 plus only e-mail the ones who have a entry great than zero (Formula i'm using for filter currently - SUBTOTAL(3,A14:A133).

Below is the code I've got so far which will e-mail one sheet at a time.

Please Login or Register  to view this content.

Thanks

VBA Noob

VBA Noob napisal(a):
> Hi all,
>
> I've got a spreadsheet which I want to e-mail extracts out to people in
> a list.
>
> I was thinking of running a advance filter on the field but not sure
> how to loop through all the names from a data validation list in A5
> plus only e-mail the ones who have a entry great than zero (Formula i'm
> using for filter currently - SUBTOTAL(3,A14:A133).
>
> Below is the code I've got so far which will e-mail one sheet at a
> time.
>
>
> Code:
> --------------------
>
> Sub Auto_E_Mail()
>
> On Error Resume Next
>
> Dim Oldsheet As Worksheet
> Set Oldsheet = ActiveSheet
>
> Application.ScreenUpdating = False
> Range("A5").Select
> ActiveSheet.Unprotect
> Columns("S:AI").Select
> Selection.EntireColumn.Hidden = False
> Range("A13").Select
> Selection.CurrentRegion.Select
> Sheets("Control panel").Select
> Sheets.Add
> ActiveSheet.Move Before:=Sheets(1)
> Sheets("Control panel").Select
> Selection.Copy
> Sheets(1).Select
> Range("A13").Select
> ActiveSheet.Paste
> Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
> Application.CutCopyMode = False
> Sheets("Control panel").Select
> Range("A10:AW12").Copy
> Sheets(1).Select
> Range("A10").Select
> ActiveSheet.Paste
> Columns("T:AH").Select
> Selection.EntireColumn.Hidden = True
> ActiveSheet.Name = Range("A2").Value
> Range("A2").Select
> Sheets("Control panel").Select
> Columns("T:AH").Select
> Selection.EntireColumn.Hidden = True
> Range("A13").Select
> Sheets(1).Move
>
> Set wb = ActiveWorkbook
> With wb
> .SaveAs ActiveSheet.Range("A2").Value & ".xls"
> End With
> Application.Dialogs(xlDialogSendMail).Show
> 'ActiveWindow.Close
> With wb
> .ChangeFileAccess xlReadOnly
> Kill .FullName
> .Close False
> End With
>
> Oldsheet.Select
> Application.ScreenUpdating = True
> End Sub
>
> --------------------
>
>
> Thanks
>
> VBA Noob


for each cell in range(your list range)
if your condition is met then
your aboce code
end if
next

mcg

mcg,

I don't know how to code this for a data validation list ??

Any help or is the data validation list a red herron ??

Should I use a named range ??

Any tips with the looping the next e-mail address

VBA Noob