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