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

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

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