I have a user form with several combo boxes that is populated with names that is read from a worksheet in excel. After a user selects a name from each combo box and clicks the submit button it will open Outlook to send an email to those people. The code works fine, but I need help reformatting the names.
How can I reformat the first and last name into a valid email? First and last name are read from a single cell.
Example: John Smith (what is selected in combo box) convert to --> [email protected]
.To = comboA.Value & ", " & _
(This is the line that works to ready name, but does not format into an email)
Also, if someone does not select a name from a combo box, is it possible to ignore this line and not try to enter something into Outlook?
Dim WS As Worksheet Dim LastRow As Long Dim bColumn As Range Dim cColumn As Range Dim dColumn As Range Dim eColumn As Range Dim fColumn As Range Dim gColumn As Range Dim hColumn As Range Dim iColumn As Range Dim jColumn As Range Dim kColumn As Range Set WS = ActiveWorkbook.Worksheets("UserData") With WS LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row For Each bColumn In .Range("B3:B" & LastRow) If bColumn.Value <> "" Then Me.comboA.AddItem bColumn.Value End If Next End With With WS LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For Each cColumn In .Range("C3:C" & LastRow) If cColumn.Value <> "" Then Me.comboB.AddItem cColumn.Value End If Next End With Dim objOutlook As Object Dim objEmail As Object Set objOutlook = CreateObject("Outlook.Application") objOutlook.Session.Logon Set objEmail = objOutlook.CreateItem(0) objEmail.Display On Error Resume Next With objEmail .To = comboA.Value & ", " & _ comboB.Value & ", " & _ comboC.Value & ", " & _ comboD.Value & ", " & _ comboE.Value & ", " & _ comboF.Value & ", " & _ comboG.Value End With