Automatically send emails to specific addreses within the worksheet based on a value within a cell

[lewlew]

I currently have an automatic email parsing system where it splits a recieved email into important sections such as name email and date email was sent.
I need to send an email to the address in that row when the days lapsed cell in the same row reaches a value of 180.
This needs to be done in every row and prefferably without needing to even open the excel sheet other wise it would be useless as we want this to be fully automatic.
I will be gratefull for any help i can get. Thanks in advance!

 

[KOKOSEK]

I've got something which sending email automatically on my pc, based on xl where I've got emails / names and deadline dates for people.
With some tweaks, it looks like this:

Sub SendThat()
    Dim picName As String
    Dim c As Range
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim i As Integer
    For Each c In Range("H3:H" & Cells(Rows.Count, "G").End(xlUp).Row).Cells
    If (c.Value) >= 180 Then
        Set OutLookApp = CreateObject("Outlook.application")
        Set OutLookMailItem = OutLookApp.CreateItem(0)
        With OutLookMailItem
               .To = c.Offset(0, -4).Value
               .Subject = "180 passed"
               .HTMLBody = "Hi, " & c.Offset(0, -6).Value
               .Send
        End With
    End If
    Next c
End Sub

then when you save xl file, you have to create text file with extension VBS and put this into:

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'Full path into excel file\ExcelFileNameWhereYouGotTheseDataAndVBA.xlsm'!Module1.SendThat"
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing
WScript.Quit

then you have to create rule in you Task Schedule to run this VBS file, as you need/wish to, daily morning etc.

 

[lewlew]

I've got something which sending email automatically on my pc, based on xl where I've got emails / names and deadline dates for people.
With some tweaks, it looks like this:

Sub SendThat()
    Dim picName As String
    Dim c As Range
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim i As Integer
    For Each c In Range("H3:H" & Cells(Rows.Count, "G").End(xlUp).Row).Cells
    If (c.Value) >= 180 Then
        Set OutLookApp = CreateObject("Outlook.application")
        Set OutLookMailItem = OutLookApp.CreateItem(0)
        With OutLookMailItem
               .To = c.Offset(0, -4).Value
               .Subject = "180 passed"
               .HTMLBody = "Hi, " & c.Offset(0, -6).Value
               .Send
        End With
    End If
    Next c
End Sub

then when you save xl file, you have to create text file with extension VBS and put this into:

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'Full path into excel file\ExcelFileNameWhereYouGotTheseDataAndVBA.xlsm'!Module1.SendThat"
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing
WScript.Quit

then you have to create rule in you Task Schedule to run this VBS file, as you need/wish to, daily morning etc.

This looks great!
Will this work only for new added rows or will it loop through and repeat ones above that have already had an email sent out?

 

[KOKOSEK]

It looping through col H, so if you want to avoid sending emails to whom you already sent, maybe you should consider additional column where you can put info about it.
For ex. something like:

    For Each c In Range("H3:H" & Cells(Rows.Count, "G").End(xlUp).Row).Cells
    If (c.Value) >= 180 and c.offset(0,3).value<>"sent" Then        ' if value in col K is different that 'sent'
        
        Set OutLookApp = CreateObject("Outlook.application")
        Set OutLookMailItem = OutLookApp.CreateItem(0)
        With OutLookMailItem
               .To = c.Offset(0, -4).Value
               .Subject = "180 passed"
               .HTMLBody = "Hi, " & c.Offset(0, -6).Value
               .Send
               c.offset(0,3).value="sent"    'put info 'sent' into column K in particular row
        End With
    End If
    Next c

p.s. Just written without any tests.

 

[lewlew]

It looping through col H, so if you want to avoid sending emails to whom you already sent, maybe you should consider additional column where you can put info about it.
For ex. something like:

    For Each c In Range("H3:H" & Cells(Rows.Count, "G").End(xlUp).Row).Cells
    If (c.Value) >= 180 and c.offset(0,3).value<>"sent" Then        ' if value in col K is different that 'sent'
       
        Set OutLookApp = CreateObject("Outlook.application")
        Set OutLookMailItem = OutLookApp.CreateItem(0)
        With OutLookMailItem
               .To = c.Offset(0, -4).Value
               .Subject = "180 passed"
               .HTMLBody = "Hi, " & c.Offset(0, -6).Value
               .Send
               c.offset(0,3).value="sent"    'put info 'sent' into column K in particular row
        End With
    End If
    Next c

p.s. Just written without any tests.

Great! Thanks alot

 

[KOKOSEK]

Happy to help.