Hello, I am having problems with excel VBA. I have a spreadsheet which will return a cell value of "Over Due" via a formula when certain information is entered into another cell. The VBA will identify when "Over Due" is present and send an email with a message. I can get everything to work except i cannot get it to look at the cell value only for "Over Due", it sends mail for all cells as it looks at the formula. I would be really grateful if someone could help out, i have looked to see if there are any other queries regarding this issue but cannot find anything. Code follows, many thanks. Sub sendemail() Dim OutlookApp As Object Dim myBodyText As String Dim myLoop As Integer Dim myRow As Integer Dim myColumn As Integer Dim myRecipient As String Dim myFirstCellAdd Dim myCounter As Integer Dim myActiveCell As String myCounter = 0 Cells.Find(What:="Over Due", After:=ActiveCell, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ True).Activate Do Until ActiveCell.Address = myFirstCellAdd myCounter = myCounter + 1 myCurrAdd = ActiveCell.Address If myCounter = 1 Then myFirstCellAdd = ActiveCell.Address myRow = ActiveCell.Row ActiveSheet.Range("A" & myRow).Select Application.ScreenUpdating = False For myLoop = 1 To 255 If ActiveCell.Value = "" Then myBodyText = myBodyText & "" & ActiveCell.Value Else myBodyText = myBodyText & " " & ActiveCell.Value If ActiveCell.Column = 1 Then myRecipient = ActiveCell.Value If ActiveCell.Column = 256 Then myBodyText = myBodyText Else ActiveCell.Offset(0, 1).Select Next ActiveSheet.Range(myCurrAdd).Select Set OutlookApp = CreateObject("Outlook.Application") With OutlookApp.CreateItem(olMailItem) .Subject = "Overdue Patient Clinical Records (PCRs)" .Body = "Dear Colleague, our records show that it has been " & ActiveCell.Offset(0, -1).Value & " over a month since the Clinical Information and Records Office (CIRO) recieved any Patient Clinical Records (PCRs) from your station. Please send all completed and checked records ASAP. Many thanks" .To = myRecipient .Send myBodyText = "" End With Cells.Find(What:="Over Due", After:=ActiveCell, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ True).Activate Loop MsgBox (myCounter) Application.ScreenUpdating = False End Sub

I like your code and can see some possible uses. I'll answer your question, then I'll ask one of my own. As I'm sure you have figured out, .Find is finding "Over Due" as part of the formula, so it's sending the email. Try this, which should only send the email when the value of the cell is "Over Due". You'll need to place the appropriate End If and End With where required - With Cells Set c = .Find(What:="Over Due", After:=ActiveCell, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ True) If c.Value = "Over Due" Then Here's my question: What are you doing with myLoop? I only ask because whenever I see a loop through an entire Row or Column, I have to think that there is a more efficient way to accomplish the goal.

Some other thoughts... Are you getting a pop-up from Outlook asking if you want to allow the email to be sent from another progam? If not, how have you disabled it? I looked at your code a little deeper and see that you use the Cells.Find method again at the bottom of your Do loop. I wonder if you could eliminate that with the FindNext method as shown in this (modified) example from the VBA Help files. With cells Set c = .Find("Over Due", lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do 'Your Do Loop Stuff Goes Here Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With