Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
send email to each customer email in excel sheet. | Excel Discussion (Misc queries) | |||
Email addresses in Excel need to format for mass email | Excel Worksheet Functions | |||
Email editor closes when forwarding Excel-embedded email | Setting up and Configuration of Excel | |||
working on excel document in email saved changes in email not in . | Excel Discussion (Misc queries) | |||
body of email disappears when I send an email from Excel | Excel Discussion (Misc queries) |