Results 1 to 2 of 2
2013-12-19, 14:14 #1
- Join Date
- Dec 2013
- Thanked 0 Times in 0 Posts
Combining Subs / skip one if file not found
I am very new to this. I have this code working that will look for the correct file to be open, switch to that window, then email that workbook to a designated group of people (found on the Distribution List.xls).
This code works fine for what I need it to do.
Sub Send_AA() For Each w In Workbooks If w.Name Like "*AA*" Then Windows(w.Name).Activate Exit For End If Next w Dim OutlookApp As Object Dim Mess As Object, Recip For Each cell In Workbooks("Distribution List").Sheets("Sheet1").Range("F14:F17") If cell.Value Like "?*@?*.?*" Then Recip = Recip & cell.Value & ";" End If Next cell Set OutlookApp = CreateObject("Outlook.Application") Set Mess = OutlookApp.CreateItem(olMailItem) With Mess .Subject = ActiveWorkbook.Name .Body = "Attached is today's file" .To = Recip .Attachments.Add ActiveWorkbook.FullName .Display .Send End With End Sub
So basically what I want it to do is:
Look for file "AA"
If "AA" is found then Send_AA
If "AA" is not found then msgbox "AA" was not found" then
Look for file "BB"
If "BB" is found then Send_BB
If "BB" is not found then msgbox "BB" was not found" then
Look for file "CC"
I'm certain this can be done, I just don't know how to write it. Any help would be greatly appreciated.
I am using Excel 2003 for this project.
Last edited by Mimi2Konnor; 2013-12-20 at 07:15. Reason: Added Excel version.
2013-12-20, 08:29 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
I am presuming you want to work with the open workbooks, and that there is only 1 of each type. You can add more search items to the array if needed. Your "Send-xx" routines, can eliminate the looping through the workbooks that you have in your example as this code will do that.
Option Explicit Sub CheckWorkbooks() Dim wkb As Workbook Dim vArray As Variant Dim bFound As Boolean bFound = False Dim x As Integer vArray = Array("AA", "BB", "CC", "DD") For x = LBound(vArray) To UBound(vArray) For Each wkb In Workbooks If UCase(wkb.Name) = UCase(vArray(x) & ".xls") Then wkb.Activate bFound = True Application.Run ("Send_" & vArray(x)) Exit For End If Next If Not bFound Then MsgBox vArray(x) & " was not found" End If bFound = False Next End Sub