Macro for Auto Mail from excel, with espect to a target date

[V.G.PADMANABHAN]

Dear Friends,

Can any one help me for my below requirement,

I am preparing a Minutes of the Meeting in XL 2007,
I want a macro which has to send a mail (I am using Microsoft Outlook mail) to the mail id in column ‘F’ with the subject mentioned in the column ‘G’, if the status of column ‘H’ is ‘Inprogress’.
No mail should be sent if the status of column ‘H’ is ‘Completed’. Please check the attached xl sheet, which will clearly explain you my requirement.

Please support me. Thanks in advance.

[humdingaling]

what do you want in the body of email?

[V.G.PADMANABHAN]

Thanks for your responce
The body of the mail should have "One of your action has reached the target date, Pls. do the needful at the earliest".

[humdingaling]

co-incidentally was working on outlook macro so had it ready, made some adjustments for your needs
the send command is stopped for debugging purposes
delete the ' in your final macro to enable autosend


see attached

**EDIT
need to amend the TO: field to be

strto = Cells(i, 6).Value

was picking up RESP in spreadsheet

[V.G.PADMANABHAN]

Boss,

I tried modified what you said, but it is not working.

Not getting any mail generated.

Pls. support. Thanks.

[humdingaling]

blah read your description other way round
i made it only email if completed

    
    Dim i, lRow As Long
    Dim OutApp, OutMail As Object
    Dim strto, strcc, strbcc, strsub, strbody As String
    
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    i = 2
    lRow = Range("A65536").End(xlUp).Row
    
    For i = 1 To lRow
    
        If Cells(i, 8).Value = "Inprogress" Then
    
            With Sheets("sheet 1")
                strto = Cells(i, 6).Value
                strcc = ""
                strbcc = ""
                strsub = Cells(i, 7).Value
                strbody = "One of your action has reached the target date, Pls. do the needful at the earliest"
            End With
            
            With OutMail
                .to = strto
                .CC = strcc
                .BCC = strbcc
                .Subject = strsub
                .Body = strbody
                '.Send
                .display
                        
            End With
        
        End If
    
    Next i
        Set OutMail = Nothing
        Set OutApp = Nothing
End Sub

amended code with your subject

[humdingaling]

Missing top line
Sub EmailVBA()

[V.G.PADMANABHAN]

Thanks Boss,

Working fine.

[humdingaling]

not a problem

If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Thanks