Excel Button sends Email to List - But id like an extra flag - VBA help

  • Hi Guys,


    Hoping someone can help point me in the right direction with the following. My skills are still growing so some advice would be welcomed. I have the attached code... working fine... basically uses a table of contact info populated by the user... and when you click on a button... creates a mail including all their addresses.. your email signature, etc. I've also attached a snapshot of the table as it appears on Sheet 15 of the workbook.



    For reference.. L4 to L26 is set as a named range (MailAddys). What I would like to do is have the Q column be a Y/N flag on whether to include in the mail or not. So if there is an address in Column L.. but Q says "N" or "No" then its not included in the mail when you click the button.



    Now if its overly difficult ill just tell the users to delete the people they dont want before they send as a workaround lol. But any help would be very gratefully appreciated.


    Thanks

  • Maybe change the FOR loop - you could check for a "Y" value in col Q (or modify the code to see if it's not an "N" value)


    From:

    Code
        For Each cell In myDataRng
            If Trim(sEmail_ids) = "" Then
                sEmail_ids = cell.Offset(0, 0).Value        ' keep the Offset (0, 0)
            Else
                sEmail_ids = sEmail_ids & vbCrLf & ";" & cell.Offset(0, 0).Value
            End If
        Next cell

    To:

    Code
        For Each cell In myDataRng
          If UCase(cell.Offset(0, 2)) = "Y" Then   'check if there is a "Y" in column Q
            If Trim(sEmail_ids) = "" Then
                sEmail_ids = cell.Offset(0, 0).Value        ' keep the Offset (0, 0)
            Else
                sEmail_ids = sEmail_ids & vbCrLf & ";" & cell.Offset(0, 0).Value
            End If
          End If
        Next cell

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!