Hi
Thanks for providing me with the code in your last response. It does work,
however, I was wondering if you can help me to take it one step further by
allowing the e-mail to be sent automatically, without me creating a button
and manually pressing that button to get the e-mail sent. I don't want to
open up the workbook at all.
Will it be possible to send the e-mail in this manner?
Also, how can I write the code to add the value of cell E within the body of
the e-mail?
Also, I dont want to limit the formula to say to check the value of cell F3.
I just want it to loop through column F - how can I change the formula to
reflect this? I have tried a few things but the the #Name? error in the
cell. The formula in cell C is - =IF((F3-TODAY())>7,"No","Yes")
Here is my code as it is now. Please let me know if you can help me?
Sub TestFile_2()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
On Error GoTo cleanup
For Each cell In
Sheets("RSReleaseDates").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" _
And LCase(cell.Offset(0, 2).Value) <> "Sent" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder - New RhymeSIGHT Release Coming Soon"
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
"A new version of RhymeSIGHT is due to be released 7
days from the receipt of this e-mail." & vbNewLine & vbNewLine & _
"Please e-mail me to arrange a date to upgrade your
laptop." & vbNewLine & vbNewLine & _
"Thank You." & vbNewLine & vbNewLine & _
"(YOUR NAME)" & vbNewLine & vbNewLine & _
"On Behalf of Support Services"
'You can add files also like this
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
cell.Offset(0, 2).Value = "Sent"
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
