Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop + Data validation list + e-mail problem
Hi all, I've got a spreadsheet which I want to e-mail extracts out to people i a list. I was thinking of running a advance filter on the field but not sur how to loop through all the names from a data validation list in A plus only e-mail the ones who have a entry great than zero (Formula i' using for filter currently - SUBTOTAL(3,A14:A133). Below is the code I've got so far which will e-mail one sheet at 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 Befo=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 Noo -- VBA Noo ----------------------------------------------------------------------- VBA Noob's Profile: http://www.excelforum.com/member.php...fo&userid=3383 View this thread: http://www.excelforum.com/showthread.php?threadid=57085 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop + Data validation list + e-mail problem
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 Befo=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop + Data validation list + e-mail problem
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 -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=570854 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit problem for data validation drop down list | Excel Worksheet Functions | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
Problem with Data Validation Dropdown List / Worksheet_Change Event | Excel Programming | |||
Data Validation Drop-Down List Problem | Excel Programming | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) |