VBA Send All if

[Ruca13]

Hello everyone,

I'm making a macro that will send to a list of contacts a custom file, but with a criteria.

The idea is that when when I click a button (Send Mail), it will send an email with an attachment to each of the contacts, but only if if the corresponding row in column F is bigger than zero (number of ocurrences), so that I do not send an empty attachment.

For now what I was able to do is select the contact (column B is the company, C is e-mail adress, D first name and E surname) and click the button, and send the attachment. That part works fine. Now the problem is changing the code to only press the button, instead of repeating the process manually for each contact at the time, and to only send if it meets the criteria.

The code follows below:

Sub Mail_ActiveSheet()
    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
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    Set Sourcewb = ActiveWorkbook


    'Copy the ActiveSheet to a new workbook
      
    Dim sup As Range
    Set sup = ActiveCell
            
    Sheets("N_Master").Activate
    
    If ActiveSheet.AutoFilterMode Then
    ActiveSheet.AutoFilter.ShowAllData
    Else
    End If
       
    ActiveSheet.Cells.EntireColumn.Hidden = False
    ActiveSheet.Cells.EntireRow.Hidden = False
      
    Selection.AutoFilter Field:=7, Criteria1:=sup.Value
    ActiveSheet.Range("$A$3:$S$2000").AutoFilter Field:=10, Criteria1:="Disputed"
      
    Rows("1:2").Select
    Selection.EntireRow.Hidden = True
     
    Application.DisplayFormulaBar = False
    With ActiveWindow
        .DisplayGridlines = False
    End With
             
    Sheets("N_Master").Copy
    
    Set Destwb = ActiveWorkbook
      
    'Determine the Excel version and file extension/format
    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2016
            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
    
    Dim mysheet As Worksheet, lp As Long, PrevCalc As Variant
 
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    PrevCalc = .Calculation
    .Calculation = xlCalculationManual
End With
 
For Each mysheet In Worksheets
    
    For lp = Rows.Count To 1 Step -1 'loop through all rows
         If mysheet.Rows(lp).EntireRow.Hidden = True Then mysheet.Rows(lp).EntireRow.Delete
    Next lp
        
Next
 
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .Calculation = PrevCalc
End With


Application.CutCopyMode = False


Columns(10).EntireColumn.Delete


Columns(7).EntireColumn.Delete

    'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Disputed Claims " & sup.Value & " " & Format(Now, "dd-mmm-yy")


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
 row_number = sup.Row
    Dim mail_mail_body_message As String
    Dim full_name As String
    Dim contact As String
    contact = Workbooks("AAM Claims Master.xlsm").Sheets("Settings").Range("C" & row_number)
    mail_body_message = Workbooks("AAM Claims Master.xlsm").Sheets("Settings").Range("H3")
    full_name = Workbooks("AAM Claims Master.xlsm").Sheets("Settings").Range("D" & row_number) & " " & Workbooks("AAM Claims Master.xlsm").Sheets("Settings").Range("E" & row_number)
    mail_body_message = Replace(mail_body_message, "subs_nome", full_name)
        
    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            .To = contact
            .CC = ""
            .BCC = ""
            .Subject = TempFileName
            .Body = mail_body_message
            .Attachments.Add Destwb.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            .Display   'or use .Send
        End With
        On Error GoTo 0
        .Close savechanges:=False
    End With


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


    Set OutMail = Nothing
    Set OutApp = Nothing


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    Application.DisplayFormulaBar = True
    With ActiveWindow
        .DisplayGridlines = True
    End With
         
    If ActiveSheet.AutoFilterMode Then
    ActiveSheet.AutoFilter.ShowAllData
    Else
    End If
    ActiveSheet.Cells.EntireColumn.Hidden = False
    ActiveSheet.Cells.EntireRow.Hidden = False
    
    Application.CutCopyMode = False
      
    Sheets("Settings").Activate
        
End Sub

Thank you for the help

 

[paulxmw]

you could put a filter in first so when you press the button it delets all the zero entrys, try this

'remove 0 and blanks
ActiveSheet.Range("$A$2:$X$15000").AutoFilter Field:=5, Criteria1:= _
"0", Operator:=xlOr, Criteria2:=" "
Range("A2:I5000").SpecialCells(xlCellTypeVisible).EntireRow.Delete 'This delets the range

 

[Ruca13]

I can try but I do not think it's the best idea because the entries should not be removed permanently, since the number of occurrences will change in the future.

Anyway, that's a start.

Thank you.