Using VBA to attach temp Excel sheet and email with Outlook

[TBrophy]

Hi all,

I've been using the VBA found on www.rondebruin.nl and it has been really helpful but I'm trying to combine a couple of the code segments without success. My goal is to email everyone on the "Email_List" tab with an attached Excel sheet called "Bed Board". I've been able to get the emails to work and I can get the sheet to show as a visible temp file but I can't seem to get the sheet attached to the emails for everyone listed on the "Email_List" tab.

Here is the code I'm working with:

'"Email" Button

Sub Button5_Click()

    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim TheActiveWindow As Window
    Dim TempWindow As Window
    Dim cell As Range

     With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    

    Set Sourcewb = ActiveWorkbook

    'Copy the sheets to a new workbook
    'We add a temporary Window to avoid the Copy problem
    'if there is a List or Table in one of the sheets and
    'if the sheets are grouped
    With Sourcewb
        Set TheActiveWindow = ActiveWindow
        Set TempWindow = .NewWindow
        .Sheets(Array("Bed Board")).Copy
    End With

    'Close temporary Window
    TempWindow.Close
    
  Set Destwb = ActiveWorkbook
  
   With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2013
            Select Case Sourcewb.FileFormat
            Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
            Case 52:
                If .HasVBProject Then
                    FileExtStr = ".xlsm": FileFormatNum = 52
                Else
                    FileExtStr = ".xlsx": FileFormatNum = 51
                End If
            Case 56: FileExtStr = ".xls": FileFormatNum = 56
            Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
            End Select
        End If
    End With
    
     'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

    

    On Error GoTo cleanup
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        Sheets("Email_List").Select
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "C").Value) = "yes" Then

            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
            With Destwb
              .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Bed Board Update" & " " & Format(Now, "dd-mmm-yy")
                .Body = "Hi " & Cells(cell.Row, "A").Value & "," _
                      & vbNewLine & vbNewLine & _
                        "Today's Bed Board data is attached and you will find estimated discharge times on the Bed Board tab. " & _
                        "With the rollout of DPOP, Hospital is aiming to discharge patients in an accurate and timely manner. Estimated discharge times are now shared at every Bed Board meeting." _
                      & vbNewLine & vbNewLine & _
                        "Thank you," _
                        & vbNewLine & _
                        "The Management"
                'You can add files also like this
                .Attachments.Add Destwb.FullName
                .Display
            End With
            On Error GoTo 0
            .Close savechanges:=False
            End With
            Set OutMail = Nothing
        End If
    Next cell

 'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True

End Sub

I've attached the Excel file I'm working with as well.
Any help offered is appreciated! Thanks!