help with email from excel

[Guest]

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(xlCellTypeConstants
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(xlCellTypeConstants
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

 

[Tom Ogilvy]

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

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(xlCellTypeConstants)
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

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...)

 

[Guest]

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?

 

[Tom Ogilvy]

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(xlCellTypeConstants)
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

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.

 

[Ron de Bruin]

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)

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.