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
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
End With

Dim objOutlook As Object
Dim objEmail As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(0)
On Error Resume Next

With objEmail
    .To = comboA.Value & ", " & _
          comboB.Value & ", " & _
          comboC.Value & ", " & _
          comboD.Value & ", " & _
          comboE.Value & ", " & _
          comboF.Value & ", " & _
End With

0 Answers