Closing a file newly used in order to only have one ActiveWorkbook

[Damian37]

Hello all,
I'm trying to modify my code in order to close a workbook after it's served its purpose. I've placed the code below. I'm currently receiving a Compile error: Type mismatch. The file I'm opening has "CHARTER_REPLEN" in the name, but it also has a date stamp before the portion of the consistent name "CHARTER_REPLEN" that is always in the name.

Sub OpenMostRecent()
    Dim fso As FileSystemObject, folder As Object
    Dim wPath As String, wMax As Long, wFile As Variant, wf As Variant
    
    wPath = "C:\Users\DVelez202\Desktop\VBA Code Files"
    
    Set fso = CreateObject("scripting.FileSystemObject")
    Set folder = fso.getfolder(wPath)
    Set wfiles = folder.Files
    wMax = 0
    wFile = ""
    For Each wf In wfiles
        ext = Mid(wf.Name, InStrRev(wf.Name, ".") + 1)
        If LCase(ext) Like "*xlsx*" Then
            If wf.DateLastModified > wMax Then
                wMax = wf.DateLastModified
                wFile = wf.Name
            End If
        End If
   Next
   If wFile <> "" Then Workbooks.Open wFile
End Sub

Sub CopyNeg()
    Dim OldWb As Workbook
    Dim NewWb As Workbook
    Dim NewWs As Worksheet
    Dim CurWs As Worksheet
    Set OldWb = "C:\Users\DVelez202\Desktop\VBA Code Files\_CHARTER_REPLEN"
    Set CurWs = ActiveWorkbook.Worksheets("Replen Report")
    Set NewWb = Workbooks.Add
    Set NewWs = NewWb.Sheets(1)
    CurWs.Range("A:T").AutoFilter Field:=20, Criteria1:="<0"
    CurWs.AutoFilter.Range.EntireRow.Copy
    NewWs.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    NewWb.SaveAs "C:\Users\DVelez202\Desktop\VBA Code Files\Negative Replenishment file_" _
    & Format(Date, "mm.dd.yyyy") & ".xlsx", FileFormat:=51
    OldWb.Close
        
    
End Sub

All help is always appreciated.
D.

 

[Steve_]

So do you have all of it figured out and working?

 

[Damian37]

So do you have all of it figured out and working?

Unfortunately not. The last part of my query that should be creating generic emails it finds isn't working properly. I got it to work in producing an email to every email address within the list, unfortunately, there are duplicate email addresses depending on how many stores that person's responsible for. I need that last bit of code to create only one email no matter how many times the email address may appear within the list. The code is below:

Sub OutlookEmail()
  
  Dim OutLookApp As Object
  Dim OutLookMailItem As Object
  Dim cell As Object
  Dim MailDest As String
  'Dim MailDest2 As String
  Set OutLookApp = CreateObject("Outlook.application")
  Set OutLookMailItem = OutLookApp.CreateItem(0)
  
'  Worksheets("Data").Activate
    'Set Rng = ActiveWorkbook.Worksheets("Data").Range("AF2:AF" & lrow)
'  For iCounter = 2 To WorksheetFunction.CountA(Columns(32))
  For Each cell In Worksheets("Data").Columns(32).Cells.SpecialCells(xlCellTypeConstants)
     MailDest = ""
     If Len(Cells(32).Offset(0, -31)) > 0 Then
     If MailDest = "" And Cells(32).Offset(0, -12) < 0 Then
     If cell.Value Like "*@*.*" And _
        Application.WorksheetFunction.CountA(Columns(32)) > 0 Then
'        If Not Contains(myColl, CStr(cell.Value)) Then
'                 myColl.Add CStr(cell.Value), CStr(cell.Value)
                 'Set OutLookMailItem = OutLookApp.CreateItem(0)
     With OutLookMailItem
     'MailDest = Cells(iCounter, 32).Value
     'MailDest2 = Cells(iCounter, 31).Value
        .To = "Damian_Velez@cable.comcast.com"
        .CC = "Davon_Johnston@cable.comcast.com; Casey_Montgomery@cable.comcast; Damian_Velez@cable.comcast.com"
        .Subject = "Negative Replenishment"
        .HTMLBody = "Hello, " & "MailDest<p>" _
            & "Your store(s) is/are reporting negative inventory on one or more SKUs. " _
            & "The SKUs that have negative counts will impact replenishment of that particular SKU(s). " _
            & "Please cycle count the below SKU(s) and enter the corrected on hand quantity into the system to prevent further impact to replenishment. " _
            & "Please remember a negative inventory count on 1 SKU will stop replenishment on that 1 SKU, " _
            & "more than 5 negative inventory counts on devices will impact all device replenishment, " _
            & "and more than 20 negatives on accessories will impact replenishment on all accessories until counts are corrected. " _
            & "If you are having an issue correcting your negative inventory please open a Service Now ticket for xStore issues." _
            & "For inventory related issues, please open a ticket in Spice Works for the Supply Chain Support Desk (SCSD).<p>" _
            & "Thank You,<p>" & "Davon Johnston<br>" _
            & "<font color=""red"">Manager, Supply Chain Support, Strategic Development</font><br>" _
            & "Cell #: 720-357-0303<br>" _
            & "Desk #: 303-658-7803"
            .Attachments.Add ActiveWorkbook.FullName
            
    .Display
    '.Send
    End With
            End If
        End If
    End If
'   End If
    Next cell
  Set OutLookMailItem = Nothing
  Set OutLookApp = Nothing
End Sub

I should also let you know I'm not returning any error messages. Thanks a lot Steve_
D.