Special Offer: Buy 1 course, get 2nd free! Buy the 'Managing Office 365 Identities & Requirements' course w/ Accelerated TestPrep, and automatically receive the 'Enabling Office 365 Services' course FREE!
Sheets("Transmittal").Select ' 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 myDict.RemoveAll Else Set myDict = CreateObject("Scripting.Dictionary") End If 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 Else MsgBox "No Contact Details found for " & r.Value & ". Mail Not Sent" Exit Sub End If Distrib2 = Distrib2 & ";" & Range("D" & iDistRow).Value 'Add the email address of that contact' Workbooks("Transmittal Log.xlsm").Activate End If End If Next r myDict.RemoveAll Set myDict = Nothing
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|I would like to delete a cell in excel from which I think it has no use||2||33|
|SCOM - Service Restart||1||25|
|VBA to clear/put no-fill the cells background color for all sheets of active workbook||5||32|
Join the community of 500,000 technology professionals and ask your questions.