Automatically Send Mail From Cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a sheet with a list of names, and one of the columns these names refer
to contain a formula that pops us the word DUE, depending on dates entered in
columns before it.

What I need is for a cell next to the DUE cell for each name to contain a
'mailto: ?name' so when DUE pops up the cell will send a mail to the name in
the hyperlink.

I am not good with VB code, and I have checked out the ron de bruin site
too, but the other complication is that when the mail has been sent , it
won't send it again every time the applicaton is opened.

Obviously, the automatic sending of mail to an individual based on the value
in the cell is the first stage, the loop would need to contain something to
check that if mail has been sent already, then not do it again.

very grateful

Thanks

Excel Newbie.
 
Hi Suzanne

Using this example macro
http://www.rondebruin.nl/mail/folder3/message.htm

You can also use the D column like this to fill in "send when the mail is send

Sub TestFile()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And LCase(cell.Offset(0, 2).Value) <> "send" Then
cell.Offset(0, 2).Value = "send"
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.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"
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
 
Back
Top