Send mail

J

Jim

I know that somewhere some has written code to send email to email links in
my file if they do not meet a certain criteria. For example, all people who
have not paid their bills in this billing period.

TIA,
Jim
 
J

Jim

Thanks for the response. I am not well-versed enough in VBA to adapt Ron de
Bruin's code to my needs. I do not want to send any of the worksheets or
anything else, only a message to each person who has not paid their bill
saying,"Please contact us to discuss bringing your account up to date." or
something like that.

--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
jIM

Ron de Bruin has some fine examples and a couple of add-ins which deal with
sending mail, filtered and otherwise.

http://www.rondebruin.nl/sendmail.htm

Gord Dibben XL2002
Click to expand...
 
R

Ron de Bruin

Hi Jim

Which Mail program you use?


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




Jim said:
Thanks for the response. I am not well-versed enough in VBA to adapt Ron de
Bruin's code to my needs. I do not want to send any of the worksheets or
anything else, only a message to each person who has not paid their bill
saying,"Please contact us to discuss bringing your account up to date." or
something like that.
Click to expand...
 
R

Ron de Bruin

Hi Jim

I have make this example for you
you can change .Send to .Display to test the code first

You must add a reference to the Microsoft outlook Library.

1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number

I use the sheetname Sheet1 in this example
In column A the names of the people
In column B the Addresses
In column C yes or no , if no a mail is send


Sub TestFile()
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 = "no" Then
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Hello " & 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
 
J

Jim

That looks terrific! I can't wait to plug it in, make a couple of reference
changes and see how it works. Thanks a million, Ron.
 
Top