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

Company	Subject	Msg Body	Contacts	Email	CC	Bcc	Msg Formula
5Linx	Past due Invoice(s)# 5Linx	SEND TO MONIQUE FIRST					Past due Invoice(s)#
Alive Worldwide	Past due Invoice(s)# Alive Worldwide	Dear Susann,%0AThis is a friendly reminder that your account has a past due balance of Thank you for your attention to this matter.	Susann Paulson	susann@revvnrg.com			Past due Invoice(s)#
Amkey	Past due Invoice(s)# Amkey	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.	Mo	yvonnemo@gmail.com	Linking text		Past due Invoice(s)#
ASEA	Past due Invoice(s)# ASEA	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.		accountspayable@asea.net	accountspayable@asea.net	mbarszcz@hyperwallet.com	Past due Invoice(s)#
Avisae	Past due Invoice(s)# Avisae	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.	Brooks Yates	brooks@avisae.com			Past due Invoice(s)#

<tbody>
</tbody>

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