Pulling email addresses

B

bgmb

New Member
Joined
Jan 10, 2025
Messages
3
Office Version
  1. 365
  2. 2024
Platform
  1. Windows
Okay, here's what I need to do: begin with a UserForm where the user selects an option from a ComboBox array and then hits the "OK" command button.
This triggers a sub that pulls the appropriate email addresses from a worksheet where I have them all populated.
This sub ends by creating a new email and entering those addresses in the "To" field.
Here's what I have, which does everything except for populating those addresses into the email:


Private Sub UserForm_Initialize()
RemoveCloseButton Me
ComboBox1.List = Array("905", "915", "916", "920", "921", "925", "932", "936", "937", "943", "948", "949", "951", "952", _
"953", "954", "960", "962", "966", "968", "969", "970", "971", "972", "973", "975", "976", "977", "978", "979", "980")
Me.ComboBox1.ListIndex = 0
End Sub


Private Sub CommandButton1_Click()
Unload Me
End Sub


Private Sub CommandButton2_Click()
Dim Pull As String
If UserForm3.ComboBox1.Value = "905" Then
Pull = "EmailAddresses!A17"
ElseIf UserForm3.ComboBox1.Value = "915" Then
Pull = "EmailAddresses!A11"
ElseIf UserForm3.ComboBox1.Value = "916" Then
Pull = "EmailAddresses!A14"
ElseIf UserForm3.ComboBox1.Value = "920" Then
Pull = "EmailAddresses!A23"
ElseIf UserForm3.ComboBox1.Value = "921" Then
Pull = "EmailAddresses!A56"
ElseIf UserForm3.ComboBox1.Value = "925" Then
Pull = "EmailAddresses!A62"
ElseIf UserForm3.ComboBox1.Value = "932" Then
Pull = "EmailAddresses!A68"
ElseIf UserForm3.ComboBox1.Value = "936" Then
Pull = "EmailAddresses!A8"
ElseIf UserForm3.ComboBox1.Value = "937" Then
Pull = "EmailAddresses!A20"
ElseIf UserForm3.ComboBox1.Value = "943" Then
Pull = "EmailAddresses!A65"
ElseIf UserForm3.ComboBox1.Value = "948" Then
Pull = "EmailAddresses!A41"
ElseIf UserForm3.ComboBox1.Value = "949" Then
Pull = "EmailAddresses!A59"
ElseIf UserForm3.ComboBox1.Value = "951" Then
Pull = "EmailAddresses!A32"
ElseIf UserForm3.ComboBox1.Value = "952" Then
Pull = "EmailAddresses!A38"
ElseIf UserForm3.ComboBox1.Value = "953" Then
Pull = "EmailAddresses!A71"
ElseIf UserForm3.ComboBox1.Value = "954" Then
Pull = "EmailAddresses!A44"
ElseIf UserForm3.ComboBox1.Value = "960" Then
Pull = "EmailAddresses!A47"
ElseIf UserForm3.ComboBox1.Value = "962" Then
Pull = "EmailAddresses!A5"
ElseIf UserForm3.ComboBox1.Value = "966" Then
Pull = "EmailAddresses!A26"
ElseIf UserForm3.ComboBox1.Value = "968" Then
Pull = "EmailAddresses!A2"
ElseIf UserForm3.ComboBox1.Value = "969" Then
Pull = "EmailAddresses!A50"
ElseIf UserForm3.ComboBox1.Value = "970" Then
Pull = "EmailAddresses!A86"
ElseIf UserForm3.ComboBox1.Value = "971" Then
Pull = "EmailAddresses!A80"
ElseIf UserForm3.ComboBox1.Value = "972" Then
Pull = "EmailAddresses!A29"
ElseIf UserForm3.ComboBox1.Value = "973" Then
Pull = "EmailAddresses!A77"
ElseIf UserForm3.ComboBox1.Value = "975" Then
Pull = "EmailAddresses!A53"
ElseIf UserForm3.ComboBox1.Value = "976" Then
Pull = "EmailAddresses!A89"
ElseIf UserForm3.ComboBox1.Value = "977" Then
Pull = "EmailAddresses!A92"
ElseIf UserForm3.ComboBox1.Value = "978" Then
Pull = "EmailAddresses!A35"
ElseIf UserForm3.ComboBox1.Value = "979" Then
Pull = "EmailAddresses!A83"
ElseIf UserForm3.ComboBox1.Value = "980" Then
Pull = "EmailAddresses!A95"
End If
Call SendMail2
End Sub


Sub SendMail2()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim Pull As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.Subject = ""
.To = Pull
.CC = ""
.Display
End With
End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
I solved it with the following:

Private Sub CommandButton2_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim Pull As String
If UserForm3.ComboBox1.Value = "905" Then
Pull = ThisWorkbook.Sheets(4).Range("A17")
ElseIf UserForm3.ComboBox1.Value = "915" Then
Pull = ThisWorkbook.Sheets(4).Range("A11")
ElseIf UserForm3.ComboBox1.Value = "916" Then
Pull = ThisWorkbook.Sheets(4).Range("A14")
ElseIf UserForm3.ComboBox1.Value = "920" Then
Pull = ThisWorkbook.Sheets(4).Range("A23")
ElseIf UserForm3.ComboBox1.Value = "921" Then
Pull = ThisWorkbook.Sheets(4).Range("A56")
ElseIf UserForm3.ComboBox1.Value = "925" Then
Pull = ThisWorkbook.Sheets(4).Range("A62")
ElseIf UserForm3.ComboBox1.Value = "932" Then
Pull = ThisWorkbook.Sheets(4).Range("A68")
ElseIf UserForm3.ComboBox1.Value = "936" Then
Pull = ThisWorkbook.Sheets(4).Range("A8")
ElseIf UserForm3.ComboBox1.Value = "937" Then
Pull = ThisWorkbook.Sheets(4).Range("A20")
ElseIf UserForm3.ComboBox1.Value = "943" Then
Pull = ThisWorkbook.Sheets(4).Range("A65")
ElseIf UserForm3.ComboBox1.Value = "948" Then
Pull = ThisWorkbook.Sheets(4).Range("A41")
ElseIf UserForm3.ComboBox1.Value = "949" Then
Pull = ThisWorkbook.Sheets(4).Range("A59")
ElseIf UserForm3.ComboBox1.Value = "951" Then
Pull = ThisWorkbook.Sheets(4).Range("A32")
ElseIf UserForm3.ComboBox1.Value = "952" Then
Pull = ThisWorkbook.Sheets(4).Range("A38")
ElseIf UserForm3.ComboBox1.Value = "953" Then
Pull = ThisWorkbook.Sheets(4).Range("A71")
ElseIf UserForm3.ComboBox1.Value = "954" Then
Pull = ThisWorkbook.Sheets(4).Range("A44")
ElseIf UserForm3.ComboBox1.Value = "960" Then
Pull = ThisWorkbook.Sheets(4).Range("A47")
ElseIf UserForm3.ComboBox1.Value = "962" Then
Pull = ThisWorkbook.Sheets(4).Range("A5")
ElseIf UserForm3.ComboBox1.Value = "966" Then
Pull = ThisWorkbook.Sheets(4).Range("A26")
ElseIf UserForm3.ComboBox1.Value = "968" Then
Pull = ThisWorkbook.Sheets(4).Range("A2")
ElseIf UserForm3.ComboBox1.Value = "969" Then
Pull = ThisWorkbook.Sheets(4).Range("A50")
ElseIf UserForm3.ComboBox1.Value = "970" Then
Pull = ThisWorkbook.Sheets(4).Range("A86")
ElseIf UserForm3.ComboBox1.Value = "971" Then
Pull = ThisWorkbook.Sheets(4).Range("A80")
ElseIf UserForm3.ComboBox1.Value = "972" Then
Pull = ThisWorkbook.Sheets(4).Range("A29")
ElseIf UserForm3.ComboBox1.Value = "973" Then
Pull = ThisWorkbook.Sheets(4).Range("A77")
ElseIf UserForm3.ComboBox1.Value = "975" Then
Pull = ThisWorkbook.Sheets(4).Range("A53")
ElseIf UserForm3.ComboBox1.Value = "976" Then
Pull = ThisWorkbook.Sheets(4).Range("A89")
ElseIf UserForm3.ComboBox1.Value = "977" Then
Pull = ThisWorkbook.Sheets(4).Range("A92")
ElseIf UserForm3.ComboBox1.Value = "978" Then
Pull = ThisWorkbook.Sheets(4).Range("A35")
ElseIf UserForm3.ComboBox1.Value = "979" Then
Pull = ThisWorkbook.Sheets(4).Range("A83")
ElseIf UserForm3.ComboBox1.Value = "980" Then
Pull = ThisWorkbook.Sheets(4).Range("A95")
End If
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.Subject = ""
.To = Pull
.CC = "rymoore@arhaus.com"
.Display
End With
End Sub
 
Upvote 0
Welcome to the Board!

Glad you figured it out, but that code looks a little messy, and doesn't lend itself well to adjutsments in data (you would need to update your code every time you have a data change).
It would probably be better to create a lookup table to lookup up your "Pull" value from that table, instead of having two lines of code for every possible ComboBox value!
You would reduce the size of that procedure significantly, and enable it to easily handle data edits without having to amend your code every time.
 
Upvote 0
In a Regular Module :


VBA Code: 
Option Explicit

Function GetEmailAddress(ByVal key As String) As String
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("EmailAddresses")

    Dim foundCell As Range
    Set foundCell = ws.Range("A:A").Find(What:=key, LookIn:=xlValues, LookAt:=xlWhole)
    
    If Not foundCell Is Nothing Then
        GetEmailAddress = foundCell.Offset(0, 1).Value
    Else
        GetEmailAddress = ""
    End If
End Function

Sub CreateEmail(ByVal toAddress As String)
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = toAddress
        .Subject = "Your Subject Here"
        .Body = "Your email body here."
        .Display
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Sub shwFrm()
UserForm1.Show
End Sub


In the UserForm code module :

Code: 
Option Explicit

Private Sub UserForm_Initialize()
    ComboBox1.List = Array("905", "915", "916", "920", "921", "925", "932", "936", "937", "943", "948", _
                           "949", "951", "952", "953", "954", "960", "962", "966", "968", "969", "970", _
                           "971", "972", "973", "975", "976", "977", "978", "979", "980")
    ComboBox1.ListIndex = 0
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Dim selectedValue As String
    selectedValue = Me.ComboBox1.Value

    Dim emailAddress As String
    emailAddress = GetEmailAddress(selectedValue)

    If emailAddress <> "" Then
        Call CreateEmail(emailAddress)
    Else
        MsgBox "No email address found for the selected option.", vbExclamation
    End If
End Sub

Download sample workbook : Internxt Drive – Private & Secure Cloud Storage
 
Upvote 0
Logit said:
In a Regular Module :


VBA Code: 
Option Explicit

Function GetEmailAddress(ByVal key As String) As String
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("EmailAddresses")

    Dim foundCell As Range
    Set foundCell = ws.Range("A:A").Find(What:=key, LookIn:=xlValues, LookAt:=xlWhole)
   
    If Not foundCell Is Nothing Then
        GetEmailAddress = foundCell.Offset(0, 1).Value
    Else
        GetEmailAddress = ""
    End If
End Function

Sub CreateEmail(ByVal toAddress As String)
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = toAddress
        .Subject = "Your Subject Here"
        .Body = "Your email body here."
        .Display
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Sub shwFrm()
UserForm1.Show
End Sub


In the UserForm code module :

Code: 
Option Explicit

Private Sub UserForm_Initialize()
    ComboBox1.List = Array("905", "915", "916", "920", "921", "925", "932", "936", "937", "943", "948", _
                           "949", "951", "952", "953", "954", "960", "962", "966", "968", "969", "970", _
                           "971", "972", "973", "975", "976", "977", "978", "979", "980")
    ComboBox1.ListIndex = 0
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Dim selectedValue As String
    selectedValue = Me.ComboBox1.Value

    Dim emailAddress As String
    emailAddress = GetEmailAddress(selectedValue)

    If emailAddress <> "" Then
        Call CreateEmail(emailAddress)
    Else
        MsgBox "No email address found for the selected option.", vbExclamation
    End If
End Sub

Download sample workbook : Internxt Drive – Private & Secure Cloud Storage
Click to expand...
Perfect, you rock! Thanks a lot!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
average based on time
Replies
1
Views
541
baqer
B
earthworm
  • Question Question
Split Data into separate worksheet based on Cell values
Replies
8
Views
600
earthworm
earthworm
S
  • Solved
EXTRACT ALL DATA ROWS OF A SPECIFIC EMPLOYEE FROM A TABLE
Replies
3
Views
228
KRice
K
G
  • Question Question
How can I sort Individual Rows A to Z from Column I to Column DX with 3 Columns Groups Together (Name, Price and Stock)
Replies
1
Views
476
Goodluckboss
G
S
  • Question Question
Increment series without secqunce function
Replies
7
Views
366
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,225,738
Messages
6,186,728
Members
453,368
Latest member
positivemind

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top