Hi guys, I have a transmittal system, that automatically emails transmittal information out to appropriate users. It gets the users names from a list specific to the active project, then looks that user up in a contacts matrix that lists all contacts across all projects.
My issue is that there may come a time when 2 contacts in that list have the same name. The contacts list contains their names, email, phone, employer and a unique id number for each entry.
I need my code to go away look at this contacts matrix and find a user by name to retrieve his email, this I have working, but, I want it to FIRST, check if there are multiple entries for this name and if so prompt the user to select which is the contact they want via a drop down listing the two identical user names plus their company name.
The code I'm using to get the Transmittal sheet go and find the contact details is as follows:
' Internal Distribution
Workbooks.Open Filename:="\\xxxxxxxx\Projects\_Project Contacts Matrix\Projects Contacts Matrix.xlsm" 'Contacts list with full details for everyone
If Not myDict Is Nothing Then
Set myDict = CreateObject("Scripting.Dictionary")
For Each r In Workbooks("Transmittal Log.xlsm").Sheets("Transmittal").Range("A9:A25") 'Lists the Full names of the contacts I need to email to'
If r.Value <> "" Then
If Not myDict.exists(r.Value) Then
myDict.Add r.Value, i
i = i + 1
Workbooks("Projects Contacts Matrix.xlsm").Activate 'the workbook containing the full contact details'
Range("G:G").Select 'Column where full name is found'
Set RangeToSearch = Selection
Set FoundCell = RangeToSearch.Find(r.Value)
If Not FoundCell Is Nothing Then
iDistRow = RangeToSearch.Find(r.Value).Row
MsgBox "No Contact Details found for " & r.Value & ". Mail Not Sent"
Distrib2 = Distrib2 & ";" & Range("D" & iDistRow).Value 'Add the email address of that contact'
Set myDict = Nothing
As you can see, with the search function, I'll by default just get the first entry with that name. I need if there are 2 entries with the same name have them both pop up (showing name and Company name [ column C] with a select option for the user to decide which on he wants.
Any idea's? Have I given enough information for anyone to figure it out?