Pick a Workbook and mail its worksheets

[sidm]

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

[GTO]

Greetings sidm,

Welcome to vbaexpress!

I do not see where you are opening a workbook. I see where you activate a window (Windows(PaySchedName).Activate).

Are you wanting to open a workbook, or pick it from the workbooks currently open?

Mark

[sidm]

Hi GTO,
Thanks for looking into this,sorry let me rephrase my question:
I already have the workbook PaySchedName = "June " & FinYear & ".xls open and Windows("Contacts " & FinYear & ".xls").Activate activated when I run the macro.I want to be able to pick any file instead of having to manually change the file name " June & FinYear" in this case such that whichever file I pick up it opens and the file name gets fed into the PayschedName.
Thanks,
sidm

[GTO]

Okay - here's a stab at the start.


Option Explicit

Sub test()
Dim tabname As String
Dim PaySchedName As String
Dim FinYear As String
Dim wbToMail As Workbook

PaySchedName = Application.GetOpenFilename(FileFilter:="ExcelFiles(*.xls), *.xls", _
MultiSelect:=False)

On Error Resume Next
Set wbToMail = Workbooks(Mid(PaySchedName, InStrRev(PaySchedName, "\") + 1))
On Error GoTo 0

If wbToMail Is Nothing Then
Set wbToMail = Workbooks.Open(PaySchedName)
End If

'... remainder of code...

First it should check to see if the wb is already opened, and if not, open it. After that, you should be able to get rid of all the Active/Activate/Selection/Select and work with the Objects.

Hope that helps,
Mark

[sidm]

Thanks GTO , I'll try that and let you know how it went.