I am looking to write code that will send an out an email automatically if 2 conditions are met in excel. The first condition being, is this a repeat design “Y or N” and the second is the number of days shown in another column. The criteria is, if “Y & over 42 days then send email” or if “N and over 14 days then send email” otherwise do nothing.<\/p>\n
I have my repeat design in Col G & Number of days in Col K. I have been trying to adapt the code below that I found online earlier on. Unfortunately, it uses a limit instead of the IF function I would like. It is currently set to send out an email as soon as any number in Col K goes over a 200 day limit, that’s the bit I would like to change.<\/p>\n
Private Sub Worksheet_Calculate()
\nDim FormulaRange As Range
\nDim NotSentMsg As String
\nDim MyMsg As String
\nDim SentMsg As String
\nDim MyLimit As Double<\/p>\n
NotSentMsg = “Not Sent”
\nSentMsg = “Sent”<\/p>\n
‘Above the MyLimit value it will run the macro
\nMyLimit = 200<\/b><\/p>\n
‘Set the range with Formulas that you want to check
\nSet FormulaRange = Me.Range(“K8:K100”)<\/p>\n
On Error GoTo EndMacro:
\nFor Each FormulaCell In FormulaRange.Cells
\nWith FormulaCell
\nIf IsNumeric(.Value) = False Then
\nMyMsg = “Not numeric”
\nElse
\nIf .Value > MyLimit Then
\nMyMsg = SentMsg
\nIf .Offset(0, 1).Value = NotSentMsg Then
\nCall Mail_with_outlook2
\nEnd If
\nElse
\nMyMsg = NotSentMsg
\nEnd If
\nEnd If
\nApplication.EnableEvents = False
\n.Offset(0, 1).Value = MyMsg
\nApplication.EnableEvents = True
\nEnd With
\nNext FormulaCell<\/p>\n
ExitMacro:
\nExit Sub<\/p>\n
EndMacro:
\nApplication.EnableEvents = True<\/p>\n
MsgBox “Some Error occurred.” _
\n& vbLf & Err.Number _
\n& vbLf & Err.Description<\/p>\n
End Sub<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"inline_featured_image":false,"iawp_total_views":8},"question-category":[53],"question_tags":[],"class_list":["post-5500","question","type-question","status-publish","hentry","question-category-office"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question\/5500","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question"}],"about":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/types\/question"}],"author":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/comments?post=5500"}],"wp:attachment":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/media?parent=5500"}],"wp:term":[{"taxonomy":"question-category","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question-category?post=5500"},{"taxonomy":"question_tags","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question_tags?post=5500"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}