Send email depending on cell values

[j844929]

Hi,

I thought I had solved this, but actually, I was nowhere even close...and the entire success of my spreadsheet's effectiveness depends on it....

Does anyone know the code to send an email to a set list of six recipients when cells in a certain column in a worksheet reach a certain value greater than another. Let me explain a little - The action to be performed is date-based.

For Example:

When the date cell L2 is 30 days later than the date in K2, send an email with subject containing data from cells B2 and C3.

However, this has to work for columns K and L in their entirety, as both columns contain a list of dates...

Hope someone can help.

Tim

 

[JPHuman]

Try this, change the email address, not sure about a mailing list, I've never tried it, let me know if it works;

Sub Over_30_days_Mail()

'You need to Reference Outlook
'Goto Tools>References and select Microsoft Outlook Libary 9.0 (or your version)
    Dim olApp As Outlook.Application
    Dim olMail As MailItem
    Dim c As Range
    
    For Each c In Range("L2:L" & Range("L65536").End(xlUp).Row)
    
'Do you want to change the formula to > 30
    If c - c.Offset(0, -1) >= 30 Then
    
        Set olApp = New Outlook.Application
        Set olMail = olApp.CreateItem(olMailItem)
                With olMail
'Change email address here **********************************
                    .To = "jim.boy@emailhere.com"
                    .Subject = Range("B" & c.Row).Value & " " & Range("C" & c.Row).Value
'Change name ****************
                    .Body = "Hello" & vbNewLine & vbNewLine & _
                            "Message here"
                    .Send  'Or use Display
                Set olMail = Nothing
        End With
        Set olApp = Nothing
        Application.ScreenUpdating = True
End If
Next c
End Sub

Morning,

How do I do this on Excel 2010, I can find the tools tab. I have the same request basically, if the value in a cell is equal or less than 30 days, I need to send the e-mail automatically, always to the same adress. I have been struggling for a while now, and posted threat on the group but can't seem to get an answer or in fact a solution.

I'm still learning and trying to anderstand the above, but I'm as lost as a flea on a cat.

Please assist