Results 1 to 2 of 2
  1. #1
    New Lounger
    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
    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
        End With
    End Sub
    I have several of these for several different files (Send_AA, Send_BB, Send_CC, Send_DD, etc.) I would like to create one macro that would run ALL of these and send out the emails, however, some days not every file has data to be sent. Those that do not have data would not be open, therefore you could not find the file to switch to the active window.

    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"
    etc etc

    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.

    Thank you!
    Last edited by Mimi2Konnor; 2013-12-20 at 07:15. Reason: Added Excel version.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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
            bFound = True
            Application.Run ("Send_" & vArray(x))
            Exit For
          End If
        If Not bFound Then
          MsgBox vArray(x) & " was not found"
        End If
        bFound = False
    End Sub
    The methodology of a separate routine (each Send-xx) routines suggests to me that the "Send-xx"s are substantially different routines with little overlapping code. If that is not the case, I would combine the routines into 1 routine and just modify those different sections to accomodate the differences for each file name.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts