G
Guest
Hello all
I was looking at some of Ron's email code on his website. I tested a few and they worked great. I am interested in this code found here: Mail a message to each person in a range (Outlook only) http://www.rondebruin.nl/sendmail.htm#messag
I have a question though. The code looks in a column and emails everyone in it. I'm trying to have an automated emailing list but the problem I have is that in my spreadsheet we have names repeated a lot. I dont want the person getting 10-20 emails that say the same thing. Does anyone have any idea to make it so that the person on the list will get emailed only once? need some brainstorming
The code in the link above will just loop down the column and email people regardless of who they are. My code has been changed a bit and instead of looking for a yes, it looks for "5" (as in 5 days remaining till an action is due)
Sub SendEmailRoutine(
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("F").Cells.SpecialCells(xlCellTypeConstants
If cell.Offset(0, 1).Value <> "" The
If cell.Value Like "*" And cell.Offset(0, 1).Value = "5" The
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 I
Next cel
Set olApp = Nothin
Application.ScreenUpdating = Tru
End Su
Thanks
Lob
I was looking at some of Ron's email code on his website. I tested a few and they worked great. I am interested in this code found here: Mail a message to each person in a range (Outlook only) http://www.rondebruin.nl/sendmail.htm#messag
I have a question though. The code looks in a column and emails everyone in it. I'm trying to have an automated emailing list but the problem I have is that in my spreadsheet we have names repeated a lot. I dont want the person getting 10-20 emails that say the same thing. Does anyone have any idea to make it so that the person on the list will get emailed only once? need some brainstorming
The code in the link above will just loop down the column and email people regardless of who they are. My code has been changed a bit and instead of looking for a yes, it looks for "5" (as in 5 days remaining till an action is due)
Sub SendEmailRoutine(
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("F").Cells.SpecialCells(xlCellTypeConstants
If cell.Offset(0, 1).Value <> "" The
If cell.Value Like "*" And cell.Offset(0, 1).Value = "5" The
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 I
Next cel
Set olApp = Nothin
Application.ScreenUpdating = Tru
End Su
Thanks
Lob