Save and Send Userform data to a specific email address based on Combobox value

Good morning All,

I made a userform containing a couple of textboxes/comboboxes etc... is it possible to send to a specific person in the combobox list an outlook-email (silently) containing the userform data ?
Example: if i select the name "Gabriel" from the combobox and i click the Send button the data will be saved and send to Gabriel email address. Not the entire workbook but only the new data entry.
Thank you for helping me.
Cheers
Gabriel

Hello GMU

Try this file with USF

@+

Issue Log v1.xlsmIt works thank you so much! I just need to figure out how can i add also the Labels of the comboboxes/textboxes to the email body.

Thanks again Brian :-)

Thank you Brian45 :-)
Do you know also how to capture Label1 to the email ?

Private Sub UserForm_Initialize()
Dim Lig As Long
Me.ComboBox1.Clear
With Worksheets("Param")
For Lig = 2 To 10
If .Range("A" & Lig) <> "" Then
Me.ComboBox1.AddItem .Range("A" & Lig).Value

End If
Next Lig
End With

Re,

Try this

Private Sub Cbn_Send_Click()
  Dim OutObj As Object  ' Late binding
  Dim Email As Object
  Dim Msg As String
  Dim Ind As Integer
  Dim sReceiver As String
  ' Find mail adress
  sReceiver = Worksheets("Param").Range("A:A").Find(What:=Me.ComboBox1.Value).Offset(0, 1).Value
  ' Create OUTLOOK instance
  Set OutObj = CreateObject("Outlook.Application")
  ' Create container object for mail
  Set Email = OutObj.CreateItem(0)
  ' Create email : Object, corp post, receiver
  Email.Subject = "Notification of Issue"
  ' Initialize message
  Msg = ""
  ' For control 1 to 5 in USF
  For Ind = 1 To 5
    If Ind < 5 Then
      Msg = Msg & Me("Label" & Ind).Caption & " : " & Me("ComboBox" & Ind).Value & vbCrLf
    Else
      Msg = Msg & Me("Label" & Ind).Caption & " : " & Me("TextBox1").Value & vbCrLf
    End If
  Next Ind
  ' Register message in body
  Email.Body = Msg
  ' Possible to add attachment
  'Email.Attachments.Add sPath & sFic
  '
  Email.to = sReceiver
  ' Send mail directly
  'Email.Send
  ' Or see mail
  Email.Display
  ' Clean object
  Set Email = Nothing
  Set OutObj = Nothing
End Sub

@+

hi Brian
Thank you, i got an compile error "Next without For", also i would like to add more textboxes and comboboxes to the code.
I really appreciate your help screen cap.gif

This is my scenario:

Private Sub Cbn_Send_Click()
Dim OutObj As Object ' Late binding
Dim Email As Object
Dim Msg As String
Dim Ind As Integer
Dim sReceiver As String
' Find mail adress
sReceiver = Worksheets("Param").Range("A:A").Find(What:=Me.ComboBox1.Value).Offset(0, 1).Value
' Create OUTLOOK instance
Set OutObj = CreateObject("Outlook.Application")
' Create container object for mail
Set Email = OutObj.CreateItem(0)
' Create email : Object, corp post, receiver
Email.Subject = "Notification of Issue"
' Initialize message
Msg = ""
' For control 1 to 13 in USF
For Ind = 1 To 13
If Ind < 13 Then
Msg = Msg & Me("Label1" & Ind).Caption & " : " & Me("ComboBox1" & Ind).Value & vbCrLf
Else
Msg = Msg & Me("Label4" & Ind).Caption & " : " & Me("ComboBox2").Value & vbCrLf
Else
Msg = Msg & Me("Label2" & Ind).Caption & " : " & Me("ComboBox3").Value & vbCrLf
Else
Msg = Msg & Me("Label5" & Ind).Caption & " : " & Me("TextBox1").Value & vbCrLf
Else
Msg = Msg & Me("Label3" & Ind).Caption & " : " & Me("ComboBox4").Value & vbCrLf
Else
Msg = Msg & Me("Label6" & Ind).Caption & " : " & Me("ComboBox5").Value & vbCrLf
Else
Msg = Msg & Me("Label7" & Ind).Caption & " : " & Me("TextBox3").Value & vbCrLf
Else
Msg = Msg & Me("Label8" & Ind).Caption & " : " & Me("ComboBox6").Value & vbCrLf
Else
Msg = Msg & Me("Label9" & Ind).Caption & " : " & Me("TextBox4").Value & vbCrLf
Else
Msg = Msg & Me("Label10" & Ind).Caption & " : " & Me("TextBox5").Value & vbCrLf
Else
Msg = Msg & Me("Label11" & Ind).Caption & " : " & Me("TextBox6").Value & vbCrLf
Else
Msg = Msg & Me("Label12" & Ind).Caption & " : " & Me("ComboBox7").Value & vbCrLf
Else
Msg = Msg & Me("Label13" & Ind).Caption & " : " & Me("TextBox7").Value & vbCrLf

End If
Next
' Register message in body
Email.Body = Msg
' Possible to add attachment
'Email.Attachments.Add sPath & sFic
'
Email.To = sReceiver
' Send mail directly
'Email.Send
' Or see mail
Email.Display
' Clean object
Set Email = Nothing
Set OutObj = Nothing

End Sub

Thanks again

Hi GMU

In my opinion, the correct code is

Private Sub Cbn_Send_Click()
  Dim OutObj As Object  ' Late binding
  Dim Email As Object
  Dim Msg As String
  Dim Ind As Integer
  Dim sReceiver As String
  ' Find mail adress
  sReceiver = Worksheets("Param").Range("A:A").Find(What:=Me.ComboBox1.Value).Offset(0, 1).Value
  ' Create OUTLOOK instance
  Set OutObj = CreateObject("Outlook.Application")
  ' Create container object for mail
  Set Email = OutObj.CreateItem(0)
  ' Create email : Object, corp post, receiver
  Email.Subject = "Notification of Issue"
  ' Initialize message
  Msg = ""
  ' For every control, no loop needed
  Msg = Msg & Me.Label1.Caption & " : " & Me.ComboBox1.Value & vbCrLf
  Msg = Msg & Me.Label4.Caption & " : " & Me.ComboBox2.Value & vbCrLf
  Msg = Msg & Me.Label2.Caption & " : " & Me.ComboBox3.Value & vbCrLf
  Msg = Msg & Me.Label5.Caption & " : " & Me.TextBox1.Value & vbCrLf
  Msg = Msg & Me.Label3.Caption & " : " & Me.ComboBox4.Value & vbCrLf
  Msg = Msg & Me.Label6.Caption & " : " & Me.ComboBox5.Value & vbCrLf
  Msg = Msg & Me.Label7.Caption & " : " & Me.TextBox3.Value & vbCrLf
  Msg = Msg & Me.Label8.Caption & " : " & Me.ComboBox6.Value & vbCrLf
  Msg = Msg & Me.Label9.Caption & " : " & Me.TextBox4.Value & vbCrLf
  Msg = Msg & Me.Label10.Caption & " : " & Me.TextBox5.Value & vbCrLf
  Msg = Msg & Me.Label11.Caption & " : " & Me.TextBox6.Value & vbCrLf
  Msg = Msg & Me.Label12.Caption & " : " & Me.ComboBox7.Value & vbCrLf
  Msg = Msg & Me.Label13.Caption & " : " & Me.TextBox7.Value & vbCrLf
  ' Register message in body
  Email.Body = Msg
  ' Possible to add attachment
  'Email.Attachments.Add sPath & sFic
  '
  Email.To = sReceiver
  ' Send mail directly
  'Email.Send
  ' Or see mail
  Email.Display
  ' Clean object
  Set Email = Nothing
  Set OutObj = Nothing
End Sub

use the syntax : Me("ControlName" & ControlIndex) or use : Me.ControlFullName
not both at the same time

@+

Hi Brian
You are totally right, it works perfectly
Thank you for teaching