Automatic Pop Up Message / Email When Condition Is Met

D

d3s1gn3d

New Member
Joined
Sep 3, 2010
Messages
14
Can you please help me about this one...
samplePic.jpg


I need to get two or more information depends on their end date because we know that there's a HUGE possibility that there are employees with same end of contract

someone, anyone please help me about this please....,
MsgBox should contain Emp Name, Position, End date, and Days Left

My problem is i don't know how to get the data's of the employee which end of contract date is less than or equal to 14 and I only get 1 Emp with this code.

This is my current code that only get 1 data:

Sub WorkSheet_Calculate()

If Range("F2") <= 14 Then
MsgBox "..CONTRACT ALMOST ENDED.." & vbLf & vbLf & _
"Employee Name : " & Range("B2")

ElseIf balance > 0 Then
MsgBox "No Renewal for today"

End If
End Sub
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Try

Code: 
Sub WorkSheet_Calculate()
Dim LR As Long, i As Long, j As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If Range("F" & i).Value <= 14 Then
        MsgBox "..CONTRACT ALMOST ENDED.." & vbLf & vbLf & "Employee Name : " & Range("B" & i).Value
        j = j + 1
    End If
Next i
If j = 0 Then MsgBox "No Renewal for today"
End Sub
 
Upvote 0
:) Cheers It Works Thank You Very Much thank you thank you and thank you Mr. Excel MVP

I'm having a hard time working on it, loops are just so arrgh haha thank you again
 
Upvote 0
- Your codes are very good, but i think what I wanted it to be is more likely as this:

Example: If a double click the excel file, a pop up message will automatically show all the employees info whose contract is >=14 days in 1 pop up message only

Is it also possible that employees info with contract >=14 days send directly to email? can you please teach me?

or if not, is it possible that i can set the excel fie into task scheduler so that i will not manually open the file everyday just to check it If i have renewal? can you please teach me about it too?

thanks in advance hope you help me again, im trying my best but i can't get it right
 
Upvote 0
Try in the ThisWorkbook module

Code: 
Private Sub Workbook_Open()
Dim LR As Long, i As Long, msg As String
With Sheets("Sheet1") ' <-------------------change to suit
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        If .Range("F" & i).Value <= 14 Then
            msg = msg & "Employee Name : " & .Range("B" & i).Value & vbNewLine
            j = j + 1
        End If
    Next i
End With
If msg = "" Then msg = "No Renewal for today"
MsgBox prompt:=msg, Buttons:=vbInformation, Title:="Contract Status"
End Sub
For information about e-mailing from Excel see http://www.rondebruin.nl/sendmail.htm
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
442
KRice
K
N
  • Locked
  • Question
Turn .BODY outlook email into HTMLBODY using VBA
Replies
2
Views
318
RoryA
RoryA
W
  • Locked
  • Question
additional pop up msgbox dependent on specific text entered into userform combobox, and other questions
Replies
2
Views
262
Peter_SSs
Peter_SSs
N
  • Question
Turn .BODY subject body in outlook into HTMLBODY using VBA
2
Replies
10
Views
610
nealmaher
N
W
  • Solved
pop up msgbox dependent on specific text entered into userform combobox
Replies
3
Views
274
Wishful Thinking
W

Forum statistics

Threads
1,214,983
Messages
6,122,592
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top