VBA Calculate Event Issue

[Vigash]

Hi,

I am trying to call macro If i run reorder mail macro it's working fine as I expected. But when I tried to call the same macro using worksheet calculate it executed macro in a different manner. It triggers mail for all the values irrespective of IF condition.

Kindly someone helps me on this.. Thanks in advance.

Private Sub Worksheet_Calculate()
  Call Module2.Reorder_mail
End Sub


Sub Reorder_mail()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Replenishment")
Dim i,j  As Integer
Dim OA As Object
Dim msg As Object

Set OA = CreateObject("outlook.application")

For i = 6 To 64

    For j = 5 To 17
      If Cells(i, j).Value > 0 Then
  
           Set msg = OA.CreateItem(0)
           msg.To = "[EMAIL]XXXX@gmail.com[/EMAIL]"
           'msg.CC = sh.Range("B" & i).Value
           msg.Subject = "Reorder for" & " " & sh.Range("B" & i).Value & "_" & sh.Cells(5, j).Value & "_" & _
                        sh.Cells(3, j).Value
           msg.Body = "Hi Team," & vbNewLine & vbNewLine & _
                "This is to inform you that " & "Item no : " & sh.Cells(5, j) & " " & "is going to be out of stock soon." & vbNewLine & vbNewLine & _
                "So Kindly order the below mentioned items for the following location." & vbNewLine & vbNewLine & _
                "Hub Name            : " & sh.Range("B" & i).Value & vbNewLine & _
                "Item no                  : " & sh.Cells(5, j).Value & vbNewLine & _
                "Item Description : " & sh.Cells(3, j).Value & vbNewLine & _
                "Order Qty              : " & sh.Cells(i, j).Value & vbNewLine & vbNewLine & _
                "Kindly Check." & vbNewLine & vbNewLine & _
                "Regards," & vbNewLine & _
                "Auto Generated Mail"

           msg.Display
               Application.Wait (Now + TimeValue("0:00:01"))
               Application.SendKeys "%s"
       
           
 
      End If
      Next j
Next i


End Sub

 

[RoryA]

If Cells(i, j).Value > 0 Then refers to cells on whatever the active sheet is at the time the code runs. You should specify which sheet you want it to look at.

 

[Vigash]

Hi Rory,

I have changed the code to if sh.cells(i,j) > 0 .. It's working fine thank you so much......

 

[RoryA]

Glad we could help.