Don't display email if cell A2 is blank

[jalrs]

Good morning guys,

I want to avoid email displaying if cell A2 is blank, because if cell A2 is blank then the sheet is blank and there's no need to display it if I'm not going to send it.
I still have difficulties handling this type of stuff therefore I search for help.

Here is my code:

Option Explicit
Sub mailmacro1()

Dim OutApp As Object
Dim OutMail As Object
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lr As Long, i As Long

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Set ws1 = ThisWorkbook.Worksheets("PainelControlo")
Set ws2 = ThisWorkbook.Worksheets("MACRO 1")

ws2.Activate

lr = Cells(Rows.Count, "A").End(xlUp).Row

    On Error Resume Next

        For i = 2 To lr
       
            Set OutMail = OutApp.CreateItem(0)

                With OutMail
   
                    .To = Cells(i, 2).Value
                    .CC = Cells(i, 3).Value
                    .Subject = Cells(i, 4).Value
                    .Body = Cells(i, 7).Value
                    .Display
                    .Attachments.Add ThisWorkbook.Path & "\Controlo e Difusão\Partilhas e Regularizações\" & Cells(i, 5).Value & ".xlsx"
       
                End With
   
        Next i

    On Error GoTo 0
   
    Set OutMail = Nothing

ws1.Activate

End Sub

Any help is greatly appreciated.

Thanks

EDIT: Cell A2 from attachment

 

[6StringJazzer]

Cell A2 in what worksheet?

You will have to open the file and check that cell in that worksheet, then close the file. This logic should occur before you create the email object, since it sounds like you do not want to create that email at all if you are not going to send it.

' Add declarations at top of sub
            Dim Att As Workbook
            Dim AttName As String
 
' Modifications to existing code

            AttName = ThisWorkbook.Path & "\Controlo e Difusão\Partilhas e Regularizações\" & Cells(i, 5).Value & ".xlsx"

            Set Att = Workbooks.Open(AttName)
            If Att.Worksheets("Worksheet Name Goes Here").Range("A2") <> "" Then

                Set OutMail = OutApp.CreateItem(0)

                With OutMail
  
                    .To = Cells(i, 2).Value
                    .CC = Cells(i, 3).Value
                    .Subject = Cells(i, 4).Value
                    .Body = Cells(i, 7).Value
                    .Display
                    .Attachments.Add AttName
      
                End With
 
            End IF
            Att.Close

 

[jalrs]

Cell A2 in what worksheet?

Hey Jeff,

Thanks for answering.

Cell A2 from attachment workbook. Worksheet there is called "Pendentes". Missed on the edit, sorry.

Thank you

 

[Micron]

I'm assuming that A2 that you want to check is in ws2.
You could wrap the counter part (i.e For i = 2 to lr) inside of an If block

If Not ws2.Range("A2") = "" Then
which won't loop to send emails if A2 in ws2 has no value. However, I think it would be better to move ws2.Activate before the Set Outlook object statements, then immediately check A2 and exit if it = "".

Set ws1 = ThisWorkbook.Worksheets("PainelControlo")
Set ws2 = ThisWorkbook.Worksheets("MACRO 1")
ws2.Activate
If Not ws2.Range("A2") = "" Then
   Set OutApp = CreateObject("Outlook.Application")
   Set OutMail = OutApp.CreateItem(0)
   etc.

 

[jalrs]

I'm assuming that A2 that you want to check is in ws2.
You could wrap the counter part (i.e For i = 2 to lr) inside of an If block

If Not ws2.Range("A2") = "" Then
which won't loop to send emails if A2 in ws2 has no value. However, I think it would be better to move ws2.Activate before the Set Outlook object statements, then immediately check A2 and exit if it = "".

Set ws1 = ThisWorkbook.Worksheets("PainelControlo")
Set ws2 = ThisWorkbook.Worksheets("MACRO 1")
ws2.Activate
If Not ws2.Range("A2") = "" Then
   Set OutApp = CreateObject("Outlook.Application")
   Set OutMail = OutApp.CreateItem(0)
   etc.

Hi Micron,

Thanks for your answer and thanks for your feedback.

As I said to Jeff, missed it on the edit on the OP, the A2 cell refers to the attachment value file, sheet called "Pendentes". Is this possible?

Thank you

 

[6StringJazzer]

I'm assuming that A2 that you want to check is in ws2.

A2 is in the attachment workbook, which is determined dynamically from the name in Cells(i, 5).

 

[6StringJazzer]

Worksheet there is called "Pendentes". Missed on the edit, sorry.

I provided all the code. Just change "Worksheet Name Goes Here" to "Pendentes".

 

[jalrs]

I provided all the code. Just change "Worksheet Name Goes Here" to "Pendentes".

Hey Jeff,

I missed your edit, caught up to it just now.

Thanks for the alert, will test it out.

 

[jalrs]

Cell A2 in what worksheet?

You will have to open the file and check that cell in that worksheet, then close the file. This logic should occur before you create the email object, since it sounds like you do not want to create that email at all if you are not going to send it.

' Add declarations at top of sub
            Dim Att As Workbook
            Dim AttName As String
 
' Modifications to existing code

            AttName = ThisWorkbook.Path & "\Controlo e Difusão\Partilhas e Regularizações\" & Cells(i, 5).Value & ".xlsx"

            Set Att = Workbooks.Open(AttName)
            If Att.Worksheets("Worksheet Name Goes Here").Range("A2") <> "" Then

                Set OutMail = OutApp.CreateItem(0)

                With OutMail
 
                    .To = Cells(i, 2).Value
                    .CC = Cells(i, 3).Value
                    .Subject = Cells(i, 4).Value
                    .Body = Cells(i, 7).Value
                    .Display
                    .Attachments.Add AttName
     
                End With
 
            End IF
            Att.Close

Hi Jeff,

This works but now my .to and the other criteria doesnt match. They are returning strange values.

Wonder what could it be?

Code is now as follows:

Option Explicit
Sub mailmacro3()

Dim OutApp As Object
Dim OutMail As Object
Dim attwb As Workbook
Dim attname As String
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lr As Long, i As Long

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Set ws1 = ThisWorkbook.Worksheets("PainelControlo")
Set ws2 = ThisWorkbook.Worksheets("MACRO 3")

ws2.Activate

lr = Cells(Rows.Count, "A").End(xlUp).Row

    On Error Resume Next

        For i = 2 To lr
        
            attname = ThisWorkbook.Path & "\Controlo e Difusão\Partilhas e Regularizações\" & Cells(i, 5).Value & ".xlsx"

            Set attwb = Workbooks.Open(attname)
            If attwb.Worksheets("Pendentes").Range("A2") <> "" Then
            
            Set OutMail = OutApp.CreateItem(0)

                With OutMail
    
                    .To = Cells(i, 2).Value
                    .CC = Cells(i, 3).Value
                    .Subject = Cells(i, 4).Value
                    .Body = Cells(i, 7).Value
                    .Display
                    .Attachments.Add attname
        
                End With
                
            End If
            
            attwb.Close
    
        Next i

    On Error GoTo 0
    
    Set OutMail = Nothing

ws1.Activate

End Sub

Thanks Jeff

 

[6StringJazzer]

When you open the attachment to read A2, that becomes ActiveWorkbook. Your code is written to default references to be to ActiveWorkbook, which is not the best programming practice. I would recommend removing the activation of ws2 and making these changes:

Sub mailmacro3()

   Dim OutApp As Object
   Dim OutMail As Object
   Dim attwb As Workbook
   Dim attname As String
   Dim ws1 As Worksheet, ws2 As Worksheet
   Dim lr As Long, i As Long
   
   Set OutApp = CreateObject("Outlook.Application")
   Set OutMail = OutApp.CreateItem(0)
   
   Set ws1 = ThisWorkbook.Worksheets("PainelControlo")
   Set ws2 = ThisWorkbook.Worksheets("MACRO 3")
   
   lr = Cells(Rows.Count, "A").End(xlUp).Row
   
   On Error Resume Next

   For i = 2 To lr
   
       attname = ThisWorkbook.Path & "\Controlo e Difusão\Partilhas e Regularizações\" & Cells(i, 5).Value & ".xlsx"

       Set attwb = Workbooks.Open(attname)
       If attwb.Worksheets("Pendentes").Range("A2") <> "" Then
       
           Set OutMail = OutApp.CreateItem(0)

           With OutMail

               .To = ws2.Cells(i, 2).Value
               .CC = ws2.Cells(i, 3).Value
               .Subject = ws2.Cells(i, 4).Value
               .Body = ws2.Cells(i, 7).Value
               .Display
               .Attachments.Add attname
   
           End With
           
       End If
       
       attwb.Close

   Next i

   On Error GoTo 0
   
   Set OutMail = Nothing
   
   ws1.Activate

End Sub