help with email from excel
Yesterday, i was given help with my code. I have a spreadsheet with many rows, each row representing a task. Each row has a person in its column. When a task comes due (when days left = 5), I want the spreadsheet to email the person involved. Now my problem is that one person may have about 20 tasks to do in one day. I dont want them to receieve 20 emails. I just want them to receive one per day regardless of the number of tasks they have due that day. For example, 5 things due march 2nd so it sends one email and then detects that it has sent one and doesnt anymore. Then on march 3rd I run the macro and the person has 7 tasks due and it sends the person another email reminding him that he has tasks due in 5 days. Any ideas on how i could do this
I started with ron de bruin's cod
-------------------------------------------
Sub TestFile2(
Dim olApp As Outlook.Applicatio
Dim olMail As MailIte
Dim cell As Rang
Application.ScreenUpdating = Fals
Set olApp = New Outlook.Applicatio
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants
If cell.Offset(0, 1).Value < "" The
If cell.Value Like "*@*" And cell.Offset(0, 1).Value = "yes" The
Set olMail = olApp.CreateItem(olMailItem
With olMai
.To = cell.Valu
.Subject = "Reminder
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine &
"Please contact us to discuss bringing your account up to date
.Send 'Or use Displa
End Wit
Set olMail = Nothin
End I
End I
Next cel
Set olApp = Nothin
Application.ScreenUpdating = Tru
End Su
-------------------------------------------
Then I got help yesterday but I dont think this code below works as I wanted it to... It doesnt let me email the person ever again after doing it once (and i dont think it sent me an email to begin with when i tested it at all...
-------------------------------------------
Sub SendEmailRoutine(
Dim olApp As Outlook.Applicatio
Dim olMail As MailIte
Dim cell As Rang
Dim addresslist As Scripting.Dictionary ' NE
Dim sAddress As String 'NE
Set addresslist = New Scripting.Dictionary 'NE
Application.ScreenUpdating = Fals
Set olApp = New Outlook.Applicatio
For Each cell In Sheets("Sheet1").Columns("F").Cells.SpecialCells(x lCellTypeConstants
sAddress = cell.Valu
If cell.Offset(0, 1).Value < "" The
If sAddress Like "*" And cell.Offset(0, 1).Value = "5" Then ' CHANG
addresslist.Add sAddress, sAddress 'NE
If Not addresslist.Exists(sAddress) Then 'NE
Set olMail = olApp.CreateItem(olMailItem
With olMai
.To = cell.Valu
.Subject = "Reminder
.Body = "Dear " & cell.Value & vbNewLine & vbNewLine &
"You have an action due in 5 days! Please contact us.
.Send 'Or use Displa
End Wit
Set olMail = Nothin
End I
End If ' NE
End I
Next cel
Set olApp = Nothin
Application.ScreenUpdating = Tru
End Su
-------------------------------------------
Are there any other methods I could use to approach this problem
Any help would be greatly appreciate
Thanks
Lob
|