User form Email with outlook

[josros60]

I have this table and I found the code below this table on the net how can i modify it so when select a company picks the mailto, cc, bcc, subject and body message address to each company.

Here it's the table

[TABLE="width: 2557"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Subject[/TD]
[TD]Msg Body[/TD]
[TD]Contacts[/TD]
[TD]Email[/TD]
[TD]CC[/TD]
[TD]Bcc[/TD]
[TD]Msg Formula[/TD]
[/TR]
[TR]
[TD]5Linx[/TD]
[TD]Past due Invoice(s)# 5Linx[/TD]
[TD]SEND TO MONIQUE FIRST[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Past due Invoice(s)#[/TD]
[/TR]
[TR]
[TD]Alive Worldwide[/TD]
[TD]Past due Invoice(s)# Alive Worldwide[/TD]
[TD]Dear Susann,%0AThis is a friendly reminder that your account has a past due balance of Thank you for your attention to this matter.[/TD]
[TD]Susann Paulson[/TD]
[TD]susann@revvnrg.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]Past due Invoice(s)#[/TD]
[/TR]
[TR]
[TD]Amkey[/TD]
[TD]Past due Invoice(s)# Amkey[/TD]
[TD]DearMo,%0AThis is a friendly reminder that your account has a past due balance of $, Invoices #. Thank you for your prompt attention to this matter.[/TD]
[TD]Mo[/TD]
[TD]yvonnemo@gmail.com[/TD]
[TD]Linking text[/TD]
[TD][/TD]
[TD]Past due Invoice(s)#[/TD]
[/TR]
[TR]
[TD]ASEA[/TD]
[TD]Past due Invoice(s)# ASEA[/TD]
[TD]Dear,%0AThis is a friendly reminder that your account has a past due balance of $, Invoices #. Thank you for your prompt attention to this matter.[/TD]
[TD][/TD]
[TD]accountspayable@asea.net [/TD]
[TD]accountspayable@asea.net[/TD]
[TD]mbarszcz@hyperwallet.com[/TD]
[TD]Past due Invoice(s)#[/TD]
[/TR]
[TR]
[TD]Avisae[/TD]
[TD]Past due Invoice(s)# Avisae[/TD]
[TD]Dear Brooks,%0AThis is a friendly reminder that your account has a past due balance of $, Invoices #. Thank you for your prompt attention to this matter.[/TD]
[TD]Brooks Yates[/TD]
[TD]brooks@avisae.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]Past due Invoice(s)#[/TD]
[/TR]
</tbody>[/TABLE]

here it's the code:

Option Explicit

Private Sub Cbn_Send_Click()
  Dim OutObj As Object  ' Late binding
  Dim Email As Object
  Dim Msg As String
  Dim Ctrl As Control
  Dim sReceiver As String
  ' Find mail adress
  sReceiver = Worksheets("Param").Range("E:E").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 = "Invoice(s) #"
  ' Initialize message
  Msg = ""
  ' For each control in USF
  For Each Ctrl In Me.Controls
    ' IF it's TextBox or Combobox
    If TypeOf Ctrl Is MSForms.TextBox Or TypeOf Ctrl Is MSForms.ComboBox Then
      ' Add value in message
      Msg = Msg & Ctrl.Text & vbCrLf
    End If
  Next Ctrl
  ' 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


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

thanks

 

[josros60]

May anybody help on this?

thanks