If else statement in VB

[Sha]

I am really new at VBA. Would someone be able to assist me. I am try to create an if else statement to trigger an email to contain values from certain cells.
My target is to have the email body to contain CaseNo (located from col C) and Case date (located in Col D) if a number more than 1 is keyed in any cell in Col R of that specific row.

example: C3 = 345, D3 = A and a number 2 is keyed into R3.

Below is the code I have so far. My current if else statement only able to retrieve the values in cell C1 and D1 only.

'Update by Extendoffice 2018/3/7
'code to check if cell range in col R is more than 0 then trigger email

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Intersect(Range("R2 :R3000"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value > 0 Then
        Call Mail_small_Text_Outlook
    End If


'code to launch email app (outlook)
Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
   'Dim strbody As String

    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
   
      'Code for email content

    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
              "You have a query from: " & Range("E2,E2000") & vbNewLine & _
              "Case Number: " & Range("c2,C3000") & Range("D2,D2000") & vbNewLine & _
              "Thank you"
        
    'Code to auto fill addressess's email address
    On Error Resume Next
    With xOutMail
        .To = "[EMAIL]ssjdksjf@gmail.com[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "Pending query processing"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
   
End Sub

End Sub

 

[cmowla]

I don't know if you have the email sub in a standard code module (e.g. the default names are Module1, Module2, etc.), but it should be (good practice).

Option Explicit

Sub Mail_small_Text_Outlook(sheetName As String, changed_RowNumber As Long)

'Code to launch email app (outlook)
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim strbody As String

Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)

'Code for email content
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"You have a query from: " & Sheets(sheetName).Range("E" & changed_RowNumber).Value & vbNewLine & _
"Case Number: " & Sheets(sheetName).Range("C" & changed_RowNumber).Value & " (" & Sheets(sheetName).Range("D" & changed_RowNumber).Value & ")" & vbNewLine & _
"Thank you"

'Code to auto fill addressess's email address
On Error Resume Next
With xOutMail
.To = "ssjdksjf@gmail.com"
.CC = ""
.BCC = ""
.Subject = "Pending query processing"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing

End Sub

Comment:
I don't know why you have On Error Resume Next. I don't believe it's needed . . . unless you are planning to read in emails too . . . so that means that you have the email addresses in another column and you want to pass it to the email sub. So the On Error Resume Next would probably take care of if there was no email in the row for which you would try to send an email but couldn't because of no email address in the current row of focus.


And here is the updated Worksheet_Change sub (which should of course be where you have it now . . . in the sheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Range("R2:R3000"), Target) Is Nothing Then
    If IsNumeric(Target.Value) Then
        If Target.Value > 1 Then
            Call Mail_small_Text_Outlook(ActiveSheet.Name, Target.Row)
        End If
    End If
End If

End Sub

Comment:

• You cannot use an If_Else here as you initially thought, because, for example, if you have a string variable type (and therefore a possibility would be this):
  

  If " " > 1 Then MsgBox "heY"

  That throws an error. You need to check if it's numeric first. Then if it is numeric, then check if it's > 1. (You had >0 in your original code, but you requested if a number > 1 is entered in column R.)
  
  
• I have If Not Intersect instead of just plain Intersect to avoid the (unneeded) xRg variable that you initially had.
  
  
  
• I pass the sheetName as well as the current row number to the email sub.
  • The row number is definitely needed.
  • The sheet name is not needed per se (unless you call this email sub with something other than just the worksheet change sub from an interactive sheet).
    
    But it's very good to get in to! (Since you are new to VBA, I wanted to give you a first exposure to the idea that sheets are objects in Excel. Each cell in a sheet are objects (and are "properties" of the sheet object). And each cell has properties: .value (which is why I added in .value in the email sub, despite that the default is, if you don't put anything after Range("A1"), it assumes Range("A1").value), .Formula, .NumberFormat, etc.)

 

[Sha]

OMG! that was such a great help! the code works!

 

[Sha]

HI there,

Sorry again. I'm trying to change the code to trigger when anything is typed into the cells in col R. I could use this am I correct?


If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Range("R2:R3000"), Target) Is Nothing Then
If Target.Value <> "" Then
Call Mail_small_Text_Outlook(ActiveSheet.Name, Target.Row)
End If
End If
End If

 

[cmowla]

HI there,

Sorry again. I'm trying to change the code to trigger when anything is typed into the cells in col R. I could use this am I correct?


If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Range("R2:R3000"), Target) Is Nothing Then
If Target.Value <> "" Then
Call Mail_small_Text_Outlook(ActiveSheet.Name, Target.Row)
End If
End If
End If

You need to remove the last End If, if that is your code. (If you just meant cells in rows 2 to 3000 in column R.)

If you meant the entire column R, you could use this instead:

If Target.Cells.Count > 1 Then Exit Sub
If (Target.Column = Range("R1").Column) And (Target.Value <> "") Then
    Call Mail_small_Text_Outlook(ActiveSheet.Name, Target.Row)
End If

 

[Sha]

Right....ok. I do want it to for the entire column R.

Thank you so much for the help.