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

 

[jimboy]

to the same email address or an email address based on the row?

 

[j844929]

Always the same list of email addresses. I've created a mailing list in Outlook already so, ideally, I'd like the mail to be sent to that...

 

[jimboy]

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

 

[j844929]

I am forever in your debt. It works like a dream... this is a life-saving device!!!

Tim

 

[jimboy]

I am forever in your debt. It works like a dream... this is a life-saving device!!!

Tim

Life saving as in a lot of work or life saving as in NHS type thing?

 

[j844929]

A lot of work...

One thing, actually. I just wondered how to get the script to run every day, whether my workbook is opened or not...?

(In actual fact, it would be an NHS life-saving thing as well, because I've already done the usual and told people that "Yes, or course I can do that!...no problem!!!" - you know the score...)


Tim

 

[jimboy]

I don't know of a way to run it when the workbook isn't opened, try search the board...


You could put a auto_open macro in personal.xls to ask if you want to open the workbook...

 

[j844929]

Opening the wookrbook is a good idea, because the script runs when the workbook is opened.

How exactly do I do it?

 

[jimboy]

Try this (change the drive and wb name)

Sub auto_open()
If MsgBox("Do you want to open Book1.xls", vbYesNo) = vbYes Then
Workbooks.Open "C:\Book1.xls"
End If
End Sub