Send emails Outlook using or statement

[Oprichnick]

Hello,
I have this piece of code that is not delivering the result I intend, but I can't figure out what is wrong.

Using OR Statement, in the first line of the piece of code below, I thought that it would send one email even if both YYY and ZZZ were present. Instead it sends two emails if both conditions exist.

I would like to send email if one or another condition is true, but not send two if both are true.

ws is worksheets.

             If ((ws.Name = "YYY") Or (ws.Name = "ZZZ")) Then                
                
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)

                    With OutMail
                        .SentOnBehalfOfName = "man@blabla.com"
                        .to = "man2@blabla.com"
                        .cc = ""
                        .bcc = ""
                        .Subject = "Reports"
                        .body = "blablabla"
                        .Attachments.Add "C:\foldeaddress\BBB.xlsx"
                        .display

 

[Logit]

Please post your entire code.

 

[Oprichnick]

Sorry.
Of course. Here it goes:

Sub Mail_small_Text_Outlook()



    Dim Destwb As Workbook, Sourcewb As Workbook
    Dim ws As Worksheet
    Dim OutApp As Object
    Dim OutMail As Object
    Dim FileExtStr As String, TempFilePath As String, TempFileName As String
    Dim FileFormatNum As Long
    
On Error Resume Next
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


   With Workbooks("File.xls")
   
   For Each ws In .sheets
        
       
                IF ((ws.name = "YYY") or (ws.name= "ZZZ") Then[COLOR=#333333] [/COLOR]
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)


                    With OutMail
                        .SentOnBehalfOfName = "man@blabla.com"
                        .to = "man2@blabla.com"
                        .cc = ""
                        .bcc = ""
                        .Subject = "reports"
                        .body = "blabla"
                        .Attachments.Add "C:\folderadress\YYY.xls"
                        .Attachments.Add "C:\folderadress\ZZZ.xls"
                        .display
                    End With
                    
                    On Error GoTo 0
        
            End If
        Next ws
    End With


cleanup:
    Set OutMail = Nothing
    Set OutApp = Nothing
    With Application
        .ScreenUpdating = False
        .EnableEvents = True
    End With
    


MsgBox ("All Reports were sent")


Exit Sub


ErrMsg: MsgBox ("Something went wrong" & vbNewLine & "Please try again")




End Sub

 

[stumac]

What is happening is that you are encountering one sheet - the condition is met, then you are encountering the next sheet where the condition is also met. If you goto cleanup after your end with then it will exit after the first time the condition is met.

   For Each ws In .sheets
        
       
                IF ((ws.name = "YYY") or (ws.name= "ZZZ") Then
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)


                    With OutMail
                        .SentOnBehalfOfName = "man@blabla.com"
                        .to = "man2@blabla.com"
                        .cc = ""
                        .bcc = ""
                        .Subject = "reports"
                        .body = "blabla"
                        .Attachments.Add "C:\folderadress\YYY.xls"
                        .Attachments.Add "C:\folderadress\ZZZ.xls"
                        .display
                    End With
                    Goto Cleanup

                    On Error GoTo 0
        
            End If
        Next ws
    End With

 

[Logit]

Or .. another way :

Do away with

 For Each ws In .Sheets

Option Explicit


Sub Mail_small_Text_Outlook()


    Dim Destwb As Workbook, Sourcewb As Workbook
    Dim ws As Worksheet
    Dim OutApp As Object
    Dim OutMail As Object
    Dim FileExtStr As String, TempFilePath As String, TempFileName As String
    Dim FileFormatNum As Long
    
On Error Resume Next
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


   With Workbooks("File.xlsm")
   
  ' For Each ws In .Sheets
       
                If (ws.Name = "YYY") Or (ws.Name = "ZZZ") Then
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)




                    With OutMail
                        .SentOnBehalfOfName = "man@blabla.com"
                        .to = "man2@blabla.com"
                        .cc = ""
                        .bcc = ""
                        .Subject = "reports"
                        .body = "blabla"
                        .Attachments.Add "C:\Users\My\Desktop\YYY.xls"
                        .Attachments.Add "C:\Users\My\Desktop\ZZZ.xls"
                        .display
                    End With
                    
                    On Error GoTo 0
        
            End If
    'Next ws
    End With


cleanup:
    Set OutMail = Nothing
    Set OutApp = Nothing
    With Application
        .ScreenUpdating = False
        .EnableEvents = True
    End With
MsgBox ("All Reports were sent")


Exit Sub




ErrMsg: MsgBox ("Something went wrong" & vbNewLine & "Please try again")


End Sub

 

[Oprichnick]

I understand what you mean and indeed it solves the problem.
However I use the For Each because I guess that in the future I will have the need to add several more if conditions. All depending on the sheet name.

If hereafter I have the need to add conditions... I guess it will stop in the first condition met.
Of course using a If Statement structure like If xxx Then_
Elseif yyy Then_
Else zzz then

Which is the only one I know...

How can I overcome this?

 

[Logit]

Not certain I understand the logic/reasoning for using an IF statement with the sheets.

Perhaps a different approach might be a SELECT CASE statement ?

 

[Oprichnick]

Ok good Logit
It makes certainly more sense.

I'm trying your suggestion, but can´t make it work using Select Case.

I guess it would be like the following... but it doesn't work:

Select Case ws.Name           
Case Is = "yyy" or = "zzz"            
            
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)


                    With OutMail
                        .SentOnBehalfOfName = "man@blabla.com"
                        .to = "man2@blabla.com"
                        .cc = ""
                        .bcc = ""
                        .Subject = "Reports"
                        .body = "blablabla"
                        .Attachments.Add "C:\folderpath\yyy.xlsx"
                        .Attachments.Add "C:\folderpath\zzz.xlsx"
                        .display
                    End With
                    

End select
End with


                    On Error GoTo 0

Sorry my newbieness

 

[Logit]

Explain in more detail:

However I use the For Each because I guess that in the future I will have the need to add several more if conditions. All depending on the sheet name.