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

Your second procedure appears to do what you ask. It should send an email
only the first time it finds a particular address in Column F that has the 5
in column G. Unless you store the dictionary in a public variable, it
should be empty each time you run the macro and have no memory of previous
emails sent.

--
Regards,
Tom Ogilvy

"Lobo" wrote in message
...
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 code
--------------------------------------------
Sub TestFile2()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim cell As Range
Application.ScreenUpdating = False
Set olApp = New Outlook.Application
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Offset(0, 1).Value < "" Then
If cell.Value Like "*@*" And cell.Offset(0, 1).Value = "yes"
Then
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = cell.Value
.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 Display
End With
Set olMail = Nothing
End If
End If
Next cell
Set olApp = Nothing
Application.ScreenUpdating = True
End Sub
--------------------------------------------
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.Application
Dim olMail As MailItem
Dim cell As Range
Dim addresslist As Scripting.Dictionary ' NEW
Dim sAddress As String 'NEW
Set addresslist = New Scripting.Dictionary 'NEW


Application.ScreenUpdating = False
Set olApp = New Outlook.Application
For Each cell In
Sheets("Sheet1").Columns("F").Cells.SpecialCells(x lCellTypeConstants)
sAddress = cell.Value
If cell.Offset(0, 1).Value < "" Then

If sAddress Like "*" And cell.Offset(0, 1).Value = "5" Then '
CHANGE
addresslist.Add sAddress, sAddress 'NEW

If Not addresslist.Exists(sAddress) Then 'NEW
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear " & cell.Value & vbNewLine &
vbNewLine & _
"You have an action due in 5 days! Please
contact us."
.Send 'Or use Display
End With
Set olMail = Nothing
End If

End If ' NEW

End If
Next cell
Set olApp = Nothing
Application.ScreenUpdating = True
End Sub
--------------------------------------------


Are there any other methods I could use to approach this problem?
Any help would be greatly appreciated

Thanks,
Lobo

Hey Tom

I'm currently working with a miniature spreadsheet that loosk just like this below

F
Name Days Lef
Terrel Lobo
Terrel Lobo 1
Steve Lobo 1
Terrel Lobo
Karen Lobo
Terrel Lobo

When i run the macro with the second code, I get "This key is already associated with an element of this collection" and no emails are sent to myself or karen

If i change the spreadsheet to

Name Days Lef
Terrel Lobo
Terrel Lobo 1
Steve Lobo 1
Terrel Lobo 1
Karen Lobo 1
Terrel Lobo 1

and then run the macro, absolutely nothing happens. No email gets sent to Terrel Lobo

I have no idea what to do to solve this. However if i use the basic code on ron de bruin's site, it sends the email out

Any ideas tom?

Sub SendEmailRoutine()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim cell As Range
Dim addresslist As Scripting.Dictionary ' NEW
Dim sAddress As String 'NEW
Set addresslist = New Scripting.Dictionary 'NEW


Application.ScreenUpdating = False
Set olApp = New Outlook.Application
For Each cell In
Sheets("Sheet1").Columns("F").Cells.SpecialCells(x lCellTypeConstants)
sAddress = cell.Value
If cell.Offset(0, 1).Value < "" Then

If sAddress Like "*" And cell.Offset(0, 1).Value = "5" Then '
CHANGE
on Error Resume Next
addresslist.Add sAddress, sAddress 'NEW
if err.Number = 0 then 'New
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear " & cell.Value & vbNewLine & vbNewLine
& _
"You have an action due in 5 days! Please
contact us."
.Send 'Or use Display
End With
Set olMail = Nothing
Else
err.clear
End If
On error goto 0
End If ' NEW

End If
Next cell
Set olApp = Nothing
Application.ScreenUpdating = True
End Sub

Untested

--
Regards,
Tom Ogilvy



"Lobo" wrote in message
...
Hey Tom,

I'm currently working with a miniature spreadsheet that loosk just like
this below:

F G
Name Days Left
Terrel Lobo 5
Terrel Lobo 15
Steve Lobo 14
Terrel Lobo 5
Karen Lobo 5
Terrel Lobo 5

When i run the macro with the second code, I get "This key is already
associated with an element of this collection" and no emails are sent to
myself or karen.

If i change the spreadsheet to:

Name Days Left
Terrel Lobo 5
Terrel Lobo 15
Steve Lobo 14
Terrel Lobo 10
Karen Lobo 10
Terrel Lobo 10

and then run the macro, absolutely nothing happens. No email gets sent to
Terrel Lobo.

I have no idea what to do to solve this. However if i use the basic code
on ron de bruin's site, it sends the email out.

Any ideas tom?

Hi Lobo

Tom's changed macro is working for me

Thanks for posting this Patrick

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Lobo" wrote in message ...
Hey Tom,

I'm currently working with a miniature spreadsheet that loosk just like this below:

F G
Name Days Left
Terrel Lobo 5
Terrel Lobo 15
Steve Lobo 14
Terrel Lobo 5
Karen Lobo 5
Terrel Lobo 5

When i run the macro with the second code, I get "This key is already associated with an element of this collection" and no
emails are sent to myself or karen.

If i change the spreadsheet to:

Name Days Left
Terrel Lobo 5
Terrel Lobo 15
Steve Lobo 14
Terrel Lobo 10
Karen Lobo 10
Terrel Lobo 10

and then run the macro, absolutely nothing happens. No email gets sent to Terrel Lobo.

I have no idea what to do to solve this. However if i use the basic code on ron de bruin's site, it sends the email out.

Any ideas tom?