Auto populate Email when Name is chosen

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

I have a list of names which i present in a combo box on the userform.
I have an email textbox. I want the email to be auto-populated based
on the name chosed. May I ask your help with this?
 
Hi Jerry, Only the first procedure is really relevant in this post the
others are to show how you may want to call it but, that entirely depends on
your needs. Private sub BuildAndSend has 4 required parameters “ToWhomâ€,
“Subjectâ€,â€Bodyâ€, and “Sendâ€. Pass the appropriate values to the procedure
and it will send or display an email to the user to send.
HTH.


Private Sub BuildAndSend( _
ByVal ToWhom As String, _
ByVal Subject As String, _
ByVal Body As String, _
ByVal Send As Boolean)

Dim Outlook As Object
Dim Mail As Object

Set Outlook = CreateObject("Outlook.Application")
Outlook.Session.Logon
Set Mail = Outlook.CreateItem(0)

With Mail
.to = ToWhom
.Subject = Subject
.Body = Body
If Send = True Then
.Send
Else
.Display
End If
End With

Set Outlook = Nothing
Set Mail = Nothing
End Sub

Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
Case "Paul"
Call BuildAndSend("(e-mail address removed)", "annual bonus", "Hi Paul your
annaul bonus is $1000", False)
Case "John"
Call BuildAndSend("(e-mail address removed)", "annual bonus", "Hi John your
annaul bonus is $2000", False)
Case "George"
Call BuildAndSend("(e-mail address removed)", "annual bonus", "Hi George your
annaul bonus is $3000", False)
Case "Ringo"
Call BuildAndSend("(e-mail address removed)", "annual bonus", "Hi Ringo your
annaul bonus is $4000", False)
End Select
End Sub

Private Sub UserForm_Initialize()
Dim NameArray As Variant
Dim I As Long
NameArray = Array("Paul", "John", "George", "Ringo")

For I = LBound(NameArray) To UBound(NameArray)
ComboBox1.AddItem NameArray(I)
Next
End Sub
 
Back
Top