I was able to figure this out. Just in case anyone ever needs it... I am adding the code to this page and closing this ticket.
Option Base 1
Option Explicit
Sub Send_Email(cc As String, body As String)
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "emailaddress@whereever.com"
.cc = cc
.Subject = "Inventory Due Today"
.body = body
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Sub Email_CC_Body()
' Dim strbody As String
Dim ccTo As String
Dim cell As Range
Dim numRows As Integer
' A 2D-array where the first column will be the assignee and the second column will be their body for the email:
Dim Assignee_And_Body() As String
Dim personAssigned As String
Dim numAssignees As Integer
Dim sizeArray As Integer
Dim i As Integer
numAssignees = 0
' -- CREATE BODY OF EMAIL PORTION START -----------------------
' strbody = "The Items listed below are expected to be returned to inventory today , " & Date & vbNewLine & vbNewLine
' Find out number of rows:
numRows = Range("B6", Range("B6").End(xlDown)).Rows.Count
' Set the size of the array:
sizeArray = Application.WorksheetFunction.CountIf(Range("M6:M" & numRows), Date)
ReDim Preserve Assignee_And_Body(sizeArray, 2)
' Check each cell in Return Due Date and see if it matches today
For Each cell In Range("M6:M" & numRows)
If cell.Value = Date Then
' Find out the person assigned
personAssigned = cell.Offset(0, -4).Value
' Check if personAssigned already exists in the array
If IndexInArray(personAssigned, Assignee_And_Body) > 0 Then
' If they already exist, add onto the body:
Assignee_And_Body(IndexInArray(personAssigned, Assignee_And_Body), 2) = Assignee_And_Body(IndexInArray(personAssigned, Assignee_And_Body), 2) & cell.Offset(0, -11).Value & " - " & cell.Offset(0, -10).Value & vbNewLine
Else
' Increase the number of assignees by 1
numAssignees = numAssignees + 1
' Add the new person assigned and build the initial body:
Assignee_And_Body(numAssignees, 1) = personAssigned
Assignee_And_Body(numAssignees, 2) = "The Items listed below are expected to be returned to inventory today , " & Date & vbNewLine & vbNewLine & cell.Offset(0, -11).Value & " - " & cell.Offset(0, -10).Value & vbNewLine
End If
End If
Next
' -- CREATE BODY OF EMAIL PORTION END ------------------------
' Loop through the array to send out the email:
For i = 1 To UBound(Assignee_And_Body)
If Assignee_And_Body(i, 1) <> "" Then
' Generate the CC email address
ccTo = WorksheetFunction.index(Sheets("Indexes").Range("AG2:AG500"), WorksheetFunction.Match(Assignee_And_Body(i, 1), Sheets("Indexes").Range("AF2:AF500"), 0))
' Send the email out for that CC and the body:
Call Send_Email(ccTo, Assignee_And_Body(i, 2))
End If
Next
' Update date on sheet:
shtInventoryList.Range("K3").Value = Date & " at " & Time
End Sub
Function IndexInArray(stringToBeFound1 As String, arr As Variant) As Integer
Dim x As Integer
Dim index As Integer
index = 0
For x = 1 To UBound(arr)
If stringToBeFound1 = arr(x, 1) Then
index = x
Exit For
End If
Next
IndexInArray = index
End Function
Bookmarks