+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    09-12-2013
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2010
    Posts
    27

    Question 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

  2. #2
    Forum Contributor
    Join Date
    05-10-2012
    Location
    Paris, France
    MS-Off Ver
    2016/365
    Posts
    123

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

    Hello GMU

    Try this file with USF

    @+
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-12-2013
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2010
    Posts
    27

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

    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

  4. #4
    Registered User
    Join Date
    09-12-2013
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2010
    Posts
    27

    Thumbs up Re: Save and Send Userform data to a specific email address based on Combobox value

    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 :-)

  5. #5
    Forum Contributor
    Join Date
    05-10-2012
    Location
    Paris, France
    MS-Off Ver
    2016/365
    Posts
    123

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

    Re,

    Try this
    Please Login or Register  to view this content.
    @+

  6. #6
    Registered User
    Join Date
    09-12-2013
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2010
    Posts
    27

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

    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

  7. #7
    Registered User
    Join Date
    09-12-2013
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2010
    Posts
    27

    Thumbs up Re: Save and Send Userform data to a specific email address based on Combobox value

    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

  8. #8
    Forum Contributor
    Join Date
    05-10-2012
    Location
    Paris, France
    MS-Off Ver
    2016/365
    Posts
    123

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

    Hi GMU

    In my opinion, the correct code is
    Please Login or Register  to view this content.
    use the syntax : Me("ControlName" & ControlIndex) or use : Me.ControlFullName
    not both at the same time

    @+

  9. #9
    Registered User
    Join Date
    09-12-2013
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2010
    Posts
    27

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Send email based on address in a cell
    By behnam in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-19-2013, 02:53 PM
  2. Send workbook to specific email address
    By tulliofn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2013, 09:29 AM
  3. Replies: 11
    Last Post: 05-26-2013, 07:45 AM
  4. send an email based on data in specific cell
    By savage in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2010, 11:54 AM
  5. macro to send excel sheet to specific email address
    By JonnieP in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2005, 06:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1