sidm
07-16-2010, 02:06 AM
Hi
I use this code to email worksheets, as you can see that I have to
manually change the file name that I want to email , is there a way that I
can use the getopenfilename method and pass it instead of having to type it in again.
Application.ScreenUpdating = False
Dim tabname As String
Dim PaySchedName As String
Dim FinYear As String
FinYear = Range("FinYear").Text
Windows("Contacts " & FinYear & ".xls").Activate 'This is the file that I put
the email addresses in and Tabs that need to be emailed to each address
Sheets(" Email").Select
Range("index").Select
PaySchedName = "June " & FinYear & ".xls" ' I want to be able to pick up a file from a directory
Do While ActiveCell.Value <> "End"
If ActiveCell.Offset(0, 2) <> "" Then
If ActiveCell.Offset(0, -1) = "No" Then
tabname = ActiveCell.Offset(0, 4).Text
Windows(PaySchedName).Activate
Sheets(tabname).Select
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
ActiveWindow.Zoom = 100
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"L:\\ET\MT\FinY" & FinYear & "\Email Macro " & FinYear & "\Schailnts
" & FinYear & "\" & tabname & "-temp" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Windows("Contacts " & FinYear & ".xls").Activate
Call SendEmail
End If
End If
ActiveCell.Offset(1, 0).Select
Loop
Set EMail = Nothing ' release memory
Application.ScreenUpdating = True
Call DeleteOldFiles
End Sub
I use this code to email worksheets, as you can see that I have to
manually change the file name that I want to email , is there a way that I
can use the getopenfilename method and pass it instead of having to type it in again.
Application.ScreenUpdating = False
Dim tabname As String
Dim PaySchedName As String
Dim FinYear As String
FinYear = Range("FinYear").Text
Windows("Contacts " & FinYear & ".xls").Activate 'This is the file that I put
the email addresses in and Tabs that need to be emailed to each address
Sheets(" Email").Select
Range("index").Select
PaySchedName = "June " & FinYear & ".xls" ' I want to be able to pick up a file from a directory
Do While ActiveCell.Value <> "End"
If ActiveCell.Offset(0, 2) <> "" Then
If ActiveCell.Offset(0, -1) = "No" Then
tabname = ActiveCell.Offset(0, 4).Text
Windows(PaySchedName).Activate
Sheets(tabname).Select
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
ActiveWindow.Zoom = 100
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"L:\\ET\MT\FinY" & FinYear & "\Email Macro " & FinYear & "\Schailnts
" & FinYear & "\" & tabname & "-temp" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Windows("Contacts " & FinYear & ".xls").Activate
Call SendEmail
End If
End If
ActiveCell.Offset(1, 0).Select
Loop
Set EMail = Nothing ' release memory
Application.ScreenUpdating = True
Call DeleteOldFiles
End Sub