Solved: VBA to send mails based on cell value

[khaledocom]

A very good morning to you my dear friends,

Is it possible to send outgoing mails to customers based on delay cell value?

Please open the attached and help ASAP.

I'm very thankful for your help.

[Zack Barresse]

Hi there, welcome to the board!

Yes, to answer your question. But as far as attachments, I only see the attached picture. I don't know how you want to trigger this action. Could it be by cell change automatically? By a button? In any case, when you generate your code which sends an email, just have it append the corresponding cell in column M. Without more details I'm sorry, but there are dozens of solutions which could be possible, and we're not sure of which would apply to you.

[khaledocom]

Thanks Zack for reply,
I'll insert one command button to check for all, for rows those have delay period more than 10 days,It'll send them reminder mail.Rows less than that nothing will be sent to them.
This is what exactly I need my dear brother.

Again I'm very thankful for your interest and help.

[Zack Barresse]

Hi,

A very rough outline of code. I have NOT had a chance to test this, so save your work first...
Sub SendEmail()
Dim olApp As Object, olMail As Object
Dim c As Range, bOLOpen As Boolean
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
bOLOpen = True
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
bOLOpen = False
End If
On Error GoTo 0
With Sheets("SHEETNAME")
For Each c In .Range("M3:M" & .Cells(.Rows.Count, "M").End(xlUp).Row)
If c.Value = "" And c.Offset(0, -1).Value > 10 Then
Set olMail = olApp.CreateItem(0)
With olMail
.To = c.Offset(0, 8).Value
.Cc = c.Offset(0, 6).Value
.Subject = "This is the Subject line"
.Body = "Hi there"
.Display '.Send
c.Value = "Sent"
End With
End If
Next c
End With
If bOLOpen = False Then olApp.Quit
End Sub

Make sure you change your "SHEETNAME" to your actual sheets name. You can assign this to a button if you want.

[khaledocom]

Thanks a lot Zack, I'll test it then I'll let you know the result.

Have a very nice day.

[khaledocom]

Zack,

My dear friend...Oh my God, It's working at last !!!!. I really don't believe that.

Thanks a lot Zack, I do appreciate your gr8 help brother.


Have a very good night.

[Zack Barresse]

You are very welcome indeed. Glad it's working for you. :)

Could you please mark your thread as Solved, by clicking Thread Tools, Mark Thread Solved, Perform Action? Thanks.

[khaledocom]

Done