setting criteria on automating email with if then else

[nkf53]

I've joined this thread hoping to expand my knowledge on visual basic and gain some understanding.
Currently I've been just learning off of other people's codes and I've been just trying to figure out to do something.

I've been trying to add a macro to my current one which would send an email out based upon a yes or no column. But my issue is What happens if i want to have an if then statement that correlates to another column based on its value. I'm thinking of starting my own company one day and was trying to make it so it's a min quantity order.

Ex) Min Quantity Quantity
10 5
5 10
so the first one won't be ordered but the 2nd one will.

Currently i have set it up as a yes or no, which i found online but I want to learn a bit more.
Thanks! you'll see me around the forum a lot more

[nkf53]

Ok so I'm going to try again
I know i need an if then statement, but I can't figure this out and have been stumped for a few days.

Option Explicit
'Needs Module 2 to set definitions for RAngeHTM so, both moduels needed
Sub Send_Row()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim rng As Range
    Dim Ash As Worksheet
 
    Set Ash = ActiveSheet
    On Error GoTo cleanup
    Set OutApp = CreateObject("Outlook.Application")
 
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
 'This determines the email cell, and the lcase cell offset is the distance in # of cells away from this column, therefore you must specify the distance if any changes in columns are made
    For Each cell In Ash.Columns("b").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 2).Value) = "yes" Then
        'Use this to set range of Cells, as in what columns to put in
 
            Ash.Range("A1:O100").AutoFilter Field:=2, Criteria1:=cell.Value
 
            With Ash.AutoFilter.Range
                On Error Resume Next
                Set rng = .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
            End With
 
            Set OutMail = OutApp.CreateItem(0)
 
            On Error Resume Next
            With OutMail
                .To = cell.Value
                 .Subject = "IAT-Blanket Order"
                .HTMLBody = RangetoHTML(rng)
                .Display  'Or use Send
            End With
            On Error GoTo 0
 
            Set OutMail = Nothing
            Ash.AutoFilterMode = False
        End If
    Next cell
 
cleanup:
    Set OutApp = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Here is my current code, but I was hoping to find a way to input a code that will allow me to either stop the email when lets say column f is greater thna column g, or even a notification of an error. Thanks, really appreciate it.

[vb5prgrmr]

If f > G Then
  'don't do it
Else
  'f is not greater than g
End If
 
If Err.Number > 0 Then
  'error occured
End If
 

Good Luck

[MMcCarthy]

My knowledge of VB is out of date as I'm afraid my expertise is in VBA.

However from a logic point of view I would create your if statement outside of this module. Set it up as criteria for calling this module.

So something like ...

If Ash.Columns("g").Cell.Value >= Ash.Columns("f").Cell.Value Then
 
    'send the email
    Send_Row
Else
    'go to next row
End If

Please forgive any syntax errors. From a logical point of view this will only create the email for those rows that satisfy the criteria.

Mary

[nkf53]

Thanks, I think I'm going to just settle it as 2 separate boxes where one is to check the qunantity and the last one is to send them all out. Much appreciated! now wish me luck on coding it lol

[nkf53]

My Brains getting fried right now -.-

Option Explicit
 
Sub example()
 
 
 
    Dim cell As Range
    Dim rng As Range
    Dim Ash As Worksheet
     Set Ash = ActiveSheet
      Dim x As Integer
 
      If Ash.Columns("g").cell.Value >= Ash.Columns("f").cell.Value Then
        Ash.Range("A1:O100").AutoFilter Field:=2, Criteria1:=cell.Value
 
            With Ash.AutoFilter.Range
                On Error Resume Next
                Set rng = .SpecialCells(xlCellTypeVisible)
    End With
    Select Case Ash.Columns
        MsgBox "Minimum Quantity is not met"
 
 
      End Select
 
 
   End Sub

-nkf

[nkf53]

I decided to make it much simpler, but is there something wrong with my logic statements here?

Sub msgbox()
 
Dim Source As Range
 
 
If ActiveSheet.Range("f1:f1000").Select > ActiveSheet.Range("g1:g1000").Select Then
 
msgbox ("Min Quantity order not met, Please Check")
End If
Exit Sub
 
End Sub

[MMcCarthy]

I don't think you can logically work a greater than comparison on a range.

[nkf53]

Yea I figured that out >.>, thanks for the help as always i figured out a way to make it work :)