title: send email to Outlook group EXCLUDING one person
body: <p>I have an Access DB through which we email reports to a list of outlook email addresses. This list contains email groups.</p>
  
  <p>I want to be able to programatically send an email to a group, but NOT send the email to a specific member of the group.</p>
  
  <p>Unfortunately, this person cannot be removed from the group, because he still needs to be able to receive emails sent to him via the group email, from other departments.</p>
  
  <p>Also, I cannot simply split the group apart into its constituents because I cannot keep adding emails to the script each time a new member is added to the Outlook group.</p>
  
  <p>Can anyone advise a way to snipe the email being sent to this one specific address within an Outlook group? </p>
  
  <p>I prefer a VBA solution, but might also be able to implement with PowerShell.</p>
  
  <p><strong>Edit</strong> - here is my code so far.</p>
  
  <p>Here is the function that sends the email:</p>
  
  <pre><code>Function SendEmails(SystemID As String, _
                      DistributionGROUPID As String, _
                      subjectID As String, _
                      EmailIDBody As String, _
                      Optional Attachments As Variant)
  
      Dim Subject As String
      Dim SendTo As Variant
      Dim CopyTo As Variant
      Dim BlindCopyTo As Variant
      Dim ReplyTo As Variant
      Dim emailBody As String
      Dim Attachement As Variant
  
      Subject = GetEmailSubject(SystemID, subjectID)
      SendTo = GetEmailAddresses(SystemID, DistributionGROUPID, 1)
      CopyTo = GetEmailAddresses(SystemID, DistributionGROUPID, 2)
      BlindCopyTo = GetEmailAddresses(SystemID, DistributionGROUPID, 3)
      ReplyTo = GetEmailAddresses(SystemID, DistributionGROUPID, 4)
      emailBody = GetEmailBody(SystemID, EmailIDBody)
  
      If IsArray(Attachments) Then
          Attachement = Attachments
      End If
  
      ' Call Send Email
      SendMails _
          Subject, _
          Attachement, _
          SendTo, _
          CopyTo, _
          BlindCopyTo, _
          ReplyTo, _
          emailBody
  
  End Function
  </code></pre>
  
  <p>And here is the function that grabs email addresses from an Access table and loads them into a string. I added a comment showing where I might insert some code / reference to a function that will open each email address and determine whether it is a group. <strong>To those of you who know what they are doing</strong> - is this the right approach, or is this even possible? I will do a bit of googling and attempt to stumble through, but if someone can provide a solution, it will be much appreciated.</p>
  
  <pre><code>Function GetEmailAddresses(SystemID As String, Business As String, RecipientType As Integer) As Variant
  
      Dim arr() As String
      Dim lSubject As String
      Dim qdf As QueryDef
      Set qdf = CurrentDb().QueryDefs("qry_Email_Get_Email_Addresses")
  
      qdf.Parameters(0) = SystemID
      qdf.Parameters(1) = Business
      qdf.Parameters(2) = CDbl(RecipientType)
  
      Dim RS As Recordset
      Set RS = qdf.OpenRecordset
  
      If RS.RecordCount = 0 Then
  
          ReDim arr(1)
          arr(0) = ""
  
      Else
  
          RS.MoveLast
          ReDim arr(RS.RecordCount - 1)
          RS.MoveFirst
          Dim offset As Integer
          offset = 0
          Do While Not RS.EOF
          ' change here? open up outlook object, determine if email is a group.
          ' if group, then create inner loop that adds individual emails to 'arr' array, excluding specific address.
              arr(offset) = RS![Email_Address]
              offset = offset + 1
              RS.MoveNext
          Loop
  
      End If
  
      RS.Close
      Set RS = Nothing
  
      GetEmailAddresses = arr
  
  End Function
  </code></pre>