none
Help macro for email with particular paramaters RRS feed

  • Question

  • hi guys i need your help i have to create a macro that analyze the data on my excel worksheet and if 3 paramaters are satisfied send me a mail, so the macro had to check line for line every 4 minute if there's a line where all my paramaters are satisfied it have to send me a mail with all is printed on this line, my parameters are 1) if in column H there is a number >= of 75, 2) in column I i got printed valours like 2-1.5 if the result of this difference is 1 or -1 3)search in columns C and G a valour < of 1.65. the other thing that this macro had to consider is that, because i got data printed one under one, if it sended me a mail with line 5 when it restart it will not send me the same mail with the line 5, i know it's difficult but can someone help me?
    Tuesday, October 13, 2015 9:13 AM

All replies

  • >>>the macro had to check line for line every 4 minute if there's a line where all my paramaters are satisfied it have to send me a mail with all is printed on this line

    If you want to do some things every 4 minute in macro, you could refer to below code:

    Public rTime As Date
    Sub CellValueAutoIncr()
    
        rTime = Now + TimeValue("00:04:00")
    
        Application.OnTime EarliestTime:=rTime, Procedure:="CellValueAutoIncr", schedule:=True
    
        Cells(1, 1).Value = Cells(1, 1).Value + 5
    
        If Cells(1, 1).Value > 25 Then
    
           Application.OnTime rTime, "CellValueAutoIncr", , False
    
        End If
    
    End Sub
    

    If you want to send mail when cells reaches certain values, you could refer to below code:

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
    
       If IsNumeric(Target.Value) And Target.Value > 200 Then
    
          Call Mail_Outlook
    
       End If
    
    End If
    
    Sub Mail_Outlook()
    
        Dim OutApp As Object
    
        Dim OutMail As Object
    
        Dim strbody As String
    
        Set OutApp = CreateObject("Outlook.Application")
    
        Set OutMail = OutApp.CreateItem(0)
    
        strbody = "Hi there" & vbNewLine & vbNewLine & _
    
                  "Cell A1 is changed" & vbNewLine & _
    
                  "This is line 2" & vbNewLine & _
    
                  "This is line 3" & vbNewLine & _
    
                  "This is line 4"
    
    
        On Error Resume Next
    
        With OutMail
    
            .To = ""
    
            .CC = ""
    
            .BCC = ""
    
            .Subject = "This is the Subject line"
    
            .Body = strbody
    
            'You can add a file like this
    
            '.Attachments.Add ("C:\test.txt")
    
            .Display   'or use .Send
    
        End With
    
        On Error GoTo 0
    
        Set OutMail = Nothing
    
        Set OutApp = Nothing
    
    End Sub
    

    Wednesday, October 14, 2015 8:57 AM
  • yes thanks but i need a bit more help maybe just some comments in the code because i don't know vba very well thanks
    Wednesday, October 14, 2015 4:28 PM
  • >>> i need a bit more help maybe just some comments in the code because i don't know vba very well thanks

    Application.OnTime Method (Excel)
    Schedules a procedure to be run at a specified time in the future (either at a specific time of day or after a specific amount of time has passed).

    Public rTime As Date
    Sub CellValueAutoIncr()
        rTime = Now + TimeValue("00:04:00")
        'schedules a procedure that add 5 to Cells(1,1).Value to be run at 4 minutes interval
        Application.OnTime EarliestTime:=rTime, Procedure:="CellValueAutoIncr", schedule:=True
        Cells(1, 1).Value = Cells(1, 1).Value + 5
    
        'when Cells(1,1).Value great than 25, stop schedules
        If Cells(1, 1).Value > 25 Then
           Application.OnTime rTime, "CellValueAutoIncr", , False
        End If
    End Sub
    

    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
       If IsNumeric(Target.Value) And Target.Value > 200 Then
          'send mail when cells reaches certain values, this example that sends mail when A1 Cell's value great than 200
          Call Mail_Outlook
       End If
    End If
    
    Sub Mail_Outlook()
        Dim OutApp As Object
        Dim OutMail As Object
        Dim strbody As String
    
        Set OutApp = CreateObject("Outlook.Application")
        'create MailItem
        Set OutMail = OutApp.CreateItem(0)
    
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                  "Cell A1 is changed" & vbNewLine & _
                  "This is line 2" & vbNewLine & _
                  "This is line 3" & vbNewLine & _
                  "This is line 4"
    
        On Error Resume Next
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = strbody
            'You can add a file like this
            '.Attachments.Add ("C:\test.txt")
            .Display   'or use .Send
        End With
        On Error GoTo 0
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    

    For more information about Excle VBA and Outlook VBA, I suggest that you could visit these helpful links about Excel VBA reference and Outlook VBA reference on MSDN
    https://msdn.microsoft.com/EN-US/library/office/ff820756.aspx
    https://msdn.microsoft.com/EN-US/library/office/ff863719.aspx

    Thursday, October 15, 2015 8:49 AM
  • nope man thank you very much but i need a code more specific for my problem if you can help me with something of more specific or i'll wait that someone can write a code more specific for my code, anyway thanks for your time
    Friday, October 16, 2015 11:00 AM
  • no one can help me?
    Sunday, October 18, 2015 8:59 AM