mail item problem

[PeterNZ]

Hi Guys (excel 2003, win xp, outlook 2003)

So when the loop is finished and it so happens that no items meet all the criteria in the loop I need to have a msgbox pop-up saying " no email messages for the area picked "

All help greatly appreciated

Option Explicit


Public areaPicked As String


Sub Picture77_Click()
frmSendToArea.Show

End Sub

Sub AreaEmailAction()

Dim irow As Integer
Dim prow As Integer
Dim Who As String
Dim messagebody As String
Dim objmsg
Dim objOutlook As Object

If areaPicked = vbNullString Then
MsgBox ("Please select and area first!")
Exit Sub
End If

irow = 12
Do Until irow = 3000 Or Sheets("Action").Cells(irow, 1) = ""
Sheets("Action").Cells(irow, 17) = ""
irow = irow + 1
Loop

messagebody = "Here are your actions from the problem Solve register. Please update your actions or email to your relevant OE resource:" & Chr(10)

irow = 12
Do Until irow = 3000 Or Sheets("Action").Cells(irow, 1) = ""
Who = Sheets("Action").Cells(irow, 6)
prow = irow
Do Until prow = 3000 Or Sheets("Action").Cells(prow, 1) = ""
If Sheets("Action").Cells(prow, 17) = "" Then
If Sheets("Action").Cells(prow, 6) = Who Then
If Sheets("Action").Cells(prow, 8) = "" Then
If Sheets("Action").Cells(prow, 3) = areaPicked Then

messagebody = messagebody _
& Chr(10) _
& Sheets("Action").Cells(prow, 1) & ". " _
& " Problem Solve: " & Sheets("Action").Cells(prow, 2) & " Department: " _
& Sheets("Action").Cells(prow, 3) _
& Chr(10) & "Issue: " _
& Sheets("Action").Cells(prow, 4) _
& Chr(10) & "Action: " _
& Sheets("Action").Cells(prow, 5) _
& Chr(10) & "Due Date: " & Sheets("Action").Cells(prow, 7) _
& Chr(10) & "-------------------------------------//-------------------------------------" & Chr(10)

Sheets("Action").Cells(prow, 17) = False
End If
End If
End If
End If
prow = prow + 1
Loop
If messagebody <> "Here are your actions from the problem Solve register. Please update your actions or email to your relevant OE resource:" & Chr(10) Then

Set objOutlook = CreateObject("Outlook.application")
Set objmsg = objOutlook.CreateItem(0)
objmsg.Subject = "Issue - Actions "
objmsg.Display

objmsg.To = Who
objmsg.body = messagebody
'objmsg.Send

messagebody = "Here are your actions from the problem Solve register. Please update your actions or email to your relevant OE resource:" & Chr(10)
End If

irow = irow + 1
Loop

areaPicked = vbNullString

Set objOutlook = Nothing
Set objmsg = Nothing



End Sub

[PeterNZ]

Hi Guys

Well I have solved this one myself. no need to respond fellas.

How? Well quite simply really I created a Boolean variable set to false at top of procedure, then in the loop set it to true. On the theory that if no mail messages are created then the loop would not complete and the boolean would remain false if no mail. I just then used if statement to pop up msgbox if the bool was indeed false.

Thanks anyway Guys.

PS: The programming logic is starting to rub off on me, this forum is a great training resource!:yes

[p45cal]

Yolu can set a flag to False before you enter the loop, only set it to True if something is found within the loop, then re-examine that flag outside the loop.
Your code abbreviated (I may not have set the flag to False in the right place):AnyMessagesToSend = False 'flag
Do Until irow = 3000 Or Sheets("Action").Cells(irow, 1) = ""
Do Until prow = 3000 Or Sheets("Action").Cells(prow, 1) = ""
If Sheets("Action").Cells(prow, 17) = "" Then
If Sheets("Action").Cells(prow, 6) = Who Then
If Sheets("Action").Cells(prow, 8) = "" Then
If Sheets("Action").Cells(prow, 3) = areaPicked Then
AnyMessagesToSend = True
'messagebody = messagebody...
End If
End If
End If
End If
prow = prow + 1
Loop
If messagebody <> "Here are your actions from the problem Solve register. Please update your actions or email to your relevant OE resource:" & Chr(10) Then
'prepare email
messagebody = "Here are your actions from the problem Solve register. Please update your actions or email to your relevant OE resource:" & Chr(10)
End If
irow = irow + 1
Loop
If Not AnyMessagesToSend Then MsgBox "None to send!"
It doesn't have to be true/false, you could increment a count instead and check whether it = 0 or not. You could use the count to finish up with another informative statement instead.

You've actually already done this sort of thing when you check for the message body contents. You might use a (different) flag here too perhaps.


oh well… two minutes late.

[PeterNZ]

Thanks for your reply p45cal
I had worked it out just before you posted but I do appriciate your reply. You make a good point about the counter instead of a bool,:friends: could be useful for other projects in future.