sending email based on a combo box to link to another table of email addresses (1 Viewer)

I have the following vba for sending email from a table that contains customers email addresses.

I now have different company record, company a, company b and company c. each of the company will have respective email addresses on another table call tbl_company_a_email, tbl_company_b_email and so on.

there is a combo box showing company a, b and c on the form.

what I want now is once the send email button is click, it should look up the email addresses base on the company selected on the combo box.

example, if the combo box selected company a, then it should look up for the email addresses on tbl_company_a_email.

Private Sub btnEmail_Click()
On Error GoTo errhandlers:
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem
Dim rs As Recordset
Dim companyEmail As String

If oOutlook Is Nothing Then
Set oOutlook = New Outlook.Application
End If
Set oEmailItem = oOutlook.CreateItem(olMailItem)
With oEmailItem
Set rs = CurrentDb.OpenRecordset("Select * from tbl_company_email")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
If IsNull(rs!Email) Then
rs.MoveNext
Else
companyEmail = companyEmail & rs!Email & ";"
.To = companyEmail
rs.MoveNext
End If
Loop
Else
MsgBox "No customer on the list"
End If
End With
Exit_errhandlers:
Exit Sub
errhandlers:
MsgBox Err.Description, vbCritical
Resume Exit_errhandlers:
End Sub

doesn't sound like you have a proper design for a database - you should not have multiple tables for email, you should have just one and include a field to identify the company (typically the companyPK) rather than doing this by table name.

Also, when posting code, please use the code tags (the # button) around your code (highlight code and click the button) to preserve formatting. At the moment very difficult to read.