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

 

[jalrs]

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

Hey Jeff,

Thanks for ammending the code for me.

I'll try that out tomorrow, since I don't have the company computer with me anymore today. Hopefully I will mark it as solution.

Thanks for your time on this Jeff

 

[jalrs]

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

Hey Jeff,

Code works, there was a typo, you missed ws2.cells on attname, therefore I ammended it on my end.

Could you add it and post back the working code, so I can mark your post as solution? I could do it myself, but credit where credit is due

Thanks for your help Jeff!

 

[6StringJazzer]

Hey Jeff,

Code works, there was a typo, you missed ws2.cells on attname, therefore I ammended it on my end.

Could you add it and post back the working code, so I can mark your post as solution? I could do it myself, but credit where credit is due

Thanks for your help Jeff!

Here you go. Such is the risk of writing code without a workbook to test it in.

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\" & ws2.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

 

[jalrs]

Here you go. Such is the risk of writing code without a workbook to test it in.

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\" & ws2.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

Indeed Jeff. Marked as solution!

Thank you

 

[Alex Blakenburg]

Just in case you find that opening each attachment is slowing the macro too much you could try this, which doesn't open the workbook.

Add this to the top.

       Dim attA2Value As String

And replace the first 2 lines in the below with the 2nd 2 lines
I couldn't get an empty cell to return anything other than 0 but as long at that is not what you will have in A2 of the attachment it should work.

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

       attA2Value = ExecuteExcel4Macro("'" & ThisWorkbook.Path & "\Controlo e Difusão\Partilhas e Regularizações\[" & ws2.Cells(i, 5).Value & ".xlsx]" & "Pendentes'!R2C1")
       If attA2Value <> "0" Then

 

[jalrs]

Just in case you find that opening each attachment is slowing the macro too much you could try this, which doesn't open the workbook.

Add this to the top.

       Dim attA2Value As String

And replace the first 2 lines in the below with the 2nd 2 lines
I couldn't get an empty cell to return anything other than 0 but as long at that is not what you will have in A2 of the attachment it should work.

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

       attA2Value = ExecuteExcel4Macro("'" & ThisWorkbook.Path & "\Controlo e Difusão\Partilhas e Regularizações\[" & ws2.Cells(i, 5).Value & ".xlsx]" & "Pendentes'!R2C1")
       If attA2Value <> "0" Then

Hey Alex,

Thanks for the suggestion.

I tried and that seems faster, I wouldn't mind staying with this approach if I'm able to understand what exactly means the first line added as I'm not familiar with the concept.

Thank you very much!

 

[Alex Blakenburg]

understand what exactly means the first line added as I'm not familiar with the concept.

In principle you can link a workbook to a closed workbook using a formula. Although not all formulas support accessing a closed workbook obviously just using "=" does.
So open another workbook in the folder all your attachments are in, pick a cell in the Macro workbook. Type in "=" and point it to A2 in the other workbook,
Close the workbook you are accessing. Your formula will now change to the full file path & name.

This will then give you the exact format required to access the closed workbook.

Now instead of putting that formula in a cell, if you drop the leading "=" and put the rest inside the brackets of ExecuteExcel4Macro(), you are calling a function from Excel 4 macros which effectively add the "=" back in and evaluates the expression returning the value.
You can replace parts as required with variables.

I am sure it can do a lot more than this but I am no expert in this.
Application.ExecuteExcel4Macro method (Excel)

 

[jalrs]

In principle you can link a workbook to a closed workbook using a formula. Although not all formulas support accessing a closed workbook obviously just using "=" does.
So open another workbook in the folder all your attachments are in, pick a cell in the Macro workbook. Type in "=" and point it to A2 in the other workbook,
Close the workbook you are accessing. Your formula will now change to the full file path & name.

This will then give you the exact format required to access the closed workbook.

Now instead of putting that formula in a cell, if you drop the leading "=" and put the rest inside the brackets of ExecuteExcel4Macro(), you are calling a function from Excel 4 macros which effectively add the "=" back in and evaluates the expression returning the value.
You can replace parts as required with variables.

I am sure it can do a lot more than this but I am no expert in this.
Application.ExecuteExcel4Macro method (Excel)

Hey Alex,

Thanks for your explanation, I will definitely check that link out in order to understand it better and replace those two lines for your two lines on all of my macros that serve my purpose.

Thank you!