Solved How to add a check on VBA row before proceeding

February 20, 2020 at 04:59:48
Specs: Windows 7
I have the following code below which loads emails including attachments. I would like to add a parameter in Column H which would indicate whether or not the email should be loaded. E.G if "Y" then load email if "N" then don't. My understanding of VBA however isn't high enough to know how or even where to put such code in.

 
Sub Send_Multiple_Email()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("RMC")

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("Outlook.Application")

Dim i As Integer
Dim last_row As Integer

last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

  For i = 2 To last_row

     Set msg = OA.CreateItem(0)

       msg.To = sh.Range("A" & i).Value
       msg.CC = sh.Range("B" & i).Value
       msg.Subject = sh.Range("c" & i).Value
       msg.Body = sh.Range("D" & i).Value
 
     If sh.Range("E" & i).Value <> "" Then
       msg.Attachments.Add sh.Range("e" & i).Value
     End If
  
     If sh.Range("F" & i).Value <> "" Then
       msg.Attachments.Add sh.Range("f" & i).Value
     End If

     If sh.Range("G" & i).Value <> "" Then
       msg.Attachments.Add sh.Range("g" & i).Value
     End If

       msg.Display

  Next i

  MsgBox "Save Emails to Folder"

  msg = OA.CreateItem(0)

End Sub


See More: How to add a check on VBA row before proceeding

Reply ↓  Report •

#1
February 20, 2020 at 05:07:32
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.

Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
February 20, 2020 at 05:12:41
Apologies, I've added the tags

Reply ↓  Report •

#3
February 20, 2020 at 06:30:49
✔ Best Answer
Yes, you added the tags to the code in your post, but I assuming that the code in your workbook has (or should have) indentations.

Indentations in VBA code make the code easier for read because they segregate different sections, similar to using paragraphs in a text document. I have edited your code to get it a little closer to how it should look in the workbook.

You should also consider adding comments (like I did below) that explain what the various segments do. This not only helps readers, but will also help you later on, like years from now, when you are trying to update your code and don't remember exactly what you were thinking when you first wrote it.

As far as your question, I believe that all you need to do is add an If instruction to check for Y in Column H, on a row by row basis, prior to the "Set msg" instruction. If the Y is present, run all the message creation instructions.

I have not tested this because I do not have a workbook set up to run the code against.

Try this and see if it works for you:

Sub Send_Multiple_Email()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("RMC")

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("Outlook.Application")

Dim i As Integer
Dim last_row As Integer

last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

  For i = 2 To last_row

'Check Column H, create email if Y
    If sh.Range("H" & i) = "Y" Then

'Create email 
     Set msg = OA.CreateItem(0)

       msg.To = sh.Range("A" & i).Value
       msg.CC = sh.Range("B" & i).Value
       msg.Subject = sh.Range("c" & i).Value
       msg.Body = sh.Range("D" & i).Value

'Add attachments
     If sh.Range("E" & i).Value <> "" Then
       msg.Attachments.Add sh.Range("e" & i).Value
     End If
  
     If sh.Range("F" & i).Value <> "" Then
       msg.Attachments.Add sh.Range("f" & i).Value
     End If

     If sh.Range("G" & i).Value <> "" Then
       msg.Attachments.Add sh.Range("g" & i).Value
     End If

       msg.Display

   End If

  Next i

'Save emails
  MsgBox "Save Emails to Folder"

  msg = OA.CreateItem(0)

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
February 20, 2020 at 06:41:53
Thank you. That works perfectly. You also make very good points in regards to having notes and indentations. The code had been pulled together.

Reply ↓  Report •

Ask Question