Autorun VBA, send specific values to Outlook

[Elvin A]

Good day everyone.

I am trying to make this code run so every time I open it so excel automatically checks for expired equipment, put message about it and then send it to Outlook.
Ideally i want all outdated equipment to be listed in email too.
For example if equipment flagged already as "ALERT!", excel upon openning file just do not do anything

i tried to copy the code to Workbook section and insert it there but it is not working.

Private Sub Workbook_Open()

' I put my code there

End Sub

Dim exRg As String
Dim xRg As Range

'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("E2:E10000"), Target)
    If xRg Is Nothing Then Exit Sub
exRg = Target.Offset(0, -4)
    If IsNumeric(Target.Value) And Target.Value < 14 And Target.Value > 0 Then
        MsgBox exRg & " is Expired"
        
        Call Mail_small_Text_Outlook
    End If
End Sub
Sub Mail_small_Text_Outlook()
    'Dim xWorkSheet As Worksheet
 
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Dear All," & "<br/><br/>" & "This is Autogenerated email. Please note " & exRg & " soon will be expired <br/><br/>" & "Regards,"
    
       
    On Error Resume Next
    With xOutMail
        .to = ""
        .CC = ""
        .BCC = ""
        .Subject = "[AUTO] " & exRg & " expiration"
        .HTMLBody = xMailBody
        .Display   'or use .Send
        
        'MsgBox xOutMail.Subject 
        
        
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Equipment Calibration Email Send Test - Copy.xlsm
	A	B	C	D	E	F
1	Equipment	Cert date	Expiration	TODAY Date	Days left	Status
2	Equipment N1	29.06.2022	29.06.2023	02.06.2023	27	Warning
3	Equipment N2	13.06.2022	13.06.2023	02.06.2023	11	ALERT!
4	Equipment N3	14.06.2022	14.06.2023	02.06.2023	12	ALERT!
5	Equipment N4	15.06.2022	15.06.2023	02.06.2023	13	ALERT!
6	Equipment N5				34	GOOD
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=B2+365
D2:D5	D2	=TODAY()
E2:E5	E2	=C2-D2
F2:F6	F2	=IF(E2<14,"ALERT!",IF(AND(E2>=14,E2<30),"Warning","GOOD"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E:E	Other Type	Icon set		NO
F:F	Cell Value	contains "ALERT!"	text	NO
F:F	Cell Value	contains "Warning"	text	NO
F:F	Cell Value	contains "GOOD"	text	NO

your help is appreciated

 

[Elvin A]

the reason i added exrg variable I could not indicate it in outlook mail body. that was way around, i believe it can be replaced with offset of xrg variable but i could not figure it out

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Auto update VBA on opening. send to Outlook
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Elvin A]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Auto update VBA on opening. send to Outlook
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

noted, thanks, though i could post any links or any attachements in the other excel forum, so i did not do that here too.

any ideas related to the matter?

 

[Peter_SSs]

though i could post any links or any attachements in the other excel forum, so i did not do that here too.

any ideas related to the matter?

Did you mean that you could not post attachments here?
If so, there is a 'Guidelines' link in my signature block below. Follow that and look specifically at section B.

 

[Elvin A]

Did you mean that you could not post attachments here?
If so, there is a 'Guidelines' link in my signature block below. Follow that and look specifically at section B.

I do not know if it visible for anybody else but I managed to add minisheet via excell addin here.
Do not know if i can attach excel file itself though.
p.s. can i edit my posts here?

 

[Peter_SSs]

I do not know if it visible for anybody else but I managed to add minisheet via excell addin here.

Yes, it is visible, your mini sheet is fine.

Do not know if i can attach excel file itself though.

If you followed the link I suggested above and looked at point 1 of the section B that I referred you to, you would know the answer to that.

p.s. can i edit my posts here?

When you first join you cannot (it is an anti-spam measure). However, very soon (after more posts) you should see an edit link under your new posts. You will then have 10 minutes in which time you will be able to edit the post.

 

[Elvin A]

k1dr0ck helped me to do it

Auto update VBA on opening. send to Outlook

Good day to everybody. new user here. i am sharing file here, since for some reason i cannot attach it docs.google.com/spreadsheets/d/1OVY6q8PU2hl27alBVxeDcDKyVfxE1hfb/edit?usp=sharing&ouid=103656479342250674299&rtpof=true&sd=true Dim exRg As String Dim xRg As Range 'Update by...

www.excelforum.com