Selected Answer
You need an email address to send a mail. The question mark you are asking about should be replaced with an email address.
The email address you want to use should be in a list of possible recipients, such as staff members. You say you have SSO numbers in your list. That is good because they are unique. No two staff members can have the same number. Therefore you can use the SSO number to find the row in which the email address is. Note that the name and first name will be in the same row, and you might use them to personalize the mail.
The following function can be modified to return the email address from your list, if you provide the SSO number.
Function EmailAddress(ByVal Sso As Variant) As String
' 182
Dim R As Long ' a row number
With Worksheets("Staff List") ' change name to suit
On Error Resume Next ' in case SSO is wrong
R = WorksheetFunction.Match(Sso, .Range("C:C"), 0)
If R Then
EmailAddress = .Cells(R, "D").Value
End If
End With
End Function
The function is demonstrated in the attached workbook. It looks for the SSO number in column C of the specified sheet. Then it returns the value found in column D. To adapt the function to your own project you should just change the name of the sheet, the look-up column ("C:C") and the SSO-column. Then you can connect the function to your email program with
emailItem.to = EmailAddress(123456789)
Run the trial function call in the attached workbook. It will take more code to create a list of recipients' SSO numbers but that is the subject of another question. If and when you ask that question please make sure to attach a copy of your workbook.
