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

 

[j844929]

Where abouts do I put the code?

 

[jimboy]

If you have never saved any macros in Personal.xls, record a macro and in the store macro in box select Personal Macro Workbook, just select a couple of cells and stop recording. Then open the macro run box (alt F8) select the macro you just recorded and click on Step into. Delete what you just recorded and paste in the auto_open macro, when you shut down excel it will ask if you want to save the Personal Workbook, click on yes.

 

[j844929]

Thanks. I'm trying a couple of other avenues, but it works when I open excel, which is what I need for the moment.

Tim

 

[damian_r]

Hi folks,

I've picked up this old thread in the hope it's a starting point for what i'm thinking about doing.

Is it possible to alter the code so that it would send email to a specific person, based on a name in a cell. the formula would have to look else where for the email address (perhaps even a looup function to another spreadsheet such as the name being A Other and it would find the corresponding email address elsewhere.

(edit) i forgot to mention that the criteria for sending an email would be a value in a cell being ethier today or in teh past assuming the cell to the right of it was empty - that should confuse matters

Damian

 

[adavies]

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

How would I modify this for similar to what I needed?

My setup is

A1 = Name
C1 = number

I want to make it that if C1 is greater than or equal to 6 it sends an email to the same email group with the name of the person and the number beside their name. Might look like this


John 6
Diane 10
Suzie 12
Danny 7

Is this possible and my last question is how do I program it? Is it VB or macro?

Thanks

 

[UHsoccer]

Hope you guys are still connecting.....

I get an error "User defined type not found" at

Dim olApp As Outlook.Application

Thanks

 

[jimboy]

See this note in the code...

'Goto Tools>References and select Microsoft Outlook Libary 9.0 (or your version)

 

[UHsoccer]

Sorry, missed that.

Thanks

 

[richarddec]

a little tweak?

Say I have a online form for someone to fill out that is a workbook. Can I have that information sent via email anytime someone printed it in a format that could be easily imported in large amounts into a seperate workbook? I guess sthe story is this:

We use expense forms to submit any expenses we incur. They are usually filled out then printed and then sent to the finance department. To have a digital trail of it, could it in the background, not only print but auto send to 4 finance people's email addresses with a name based on 2 concatenated cells?

Im not sure if im being ridiculous but perhaps it is possible?

 

[Sandra1964]

Doesn't work for me

I am a new beginner but the code looks helpful to me so I thought I'd cut and paste it. However the code always hangs on the following line

Set olApp = New Outlook.Application

and I get an error message. I have referenced MSOutlook as it states in the notes.

Can anyone help?