Don't display email if cell A2 is blank

jalrs

Active Member
Joined
Apr 6, 2022
Messages
300
Office Version
  1. 365
Platform
  1. Windows
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:

VBA 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

Active Member
Joined
Apr 6, 2022
Messages
300
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jalrs

Active Member
Joined
Apr 6, 2022
Messages
300
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
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

Well-known Member
Joined
Jan 27, 2010
Messages
2,537
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
Rich (BB code):
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
 
Solution

jalrs

Active Member
Joined
Apr 6, 2022
Messages
300
Office Version
  1. 365
Platform
  1. Windows
Here you go. Such is the risk of writing code without a workbook to test it in.
Rich (BB code):
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,627
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
       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.

Rich (BB code):
       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

Active Member
Joined
Apr 6, 2022
Messages
300
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
       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.

Rich (BB code):
       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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,627
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Apr 6, 2022
Messages
300
Office Version
  1. 365
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,181,635
Messages
5,931,130
Members
436,776
Latest member
kranda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top