Userforms in Excel to generate custom Email Templates for outlook

UserForm Code (VBA)

Paste into the code for your UserForm:

Private Sub UserForm_Initialize()
    cmbEmailType.AddItem "RFP"
    cmbEmailType.AddItem "PO Request"
End Sub

Private Sub cmbEmailType_Change()
    cmbServiceMaterial.Clear
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("ServicesMaterials")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim i As Long
    For i = 2 To lastRow
        If ws.Cells(i, 1).Value = cmbEmailType.Value Then
            cmbServiceMaterial.AddItem ws.Cells(i, 2).Value
        End If
    Next i
End Sub

Private Sub cmbServiceMaterial_Change()
    lstVendors.Clear
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Vendors")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim i As Long
    For i = 2 To lastRow
        If ws.Cells(i, 2).Value = cmbServiceMaterial.Value Then
            If Not dict.exists(ws.Cells(i, 1).Value) Then
                dict.Add ws.Cells(i, 1).Value, True
                lstVendors.AddItem ws.Cells(i, 1).Value
            End If
        End If
    Next i
End Sub

Private Sub btnGenerateEmails_Click()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Set OutlookApp = CreateObject("Outlook.Application")
    
    Dim wsVendors As Worksheet, wsServices As Worksheet, wsSettings As Worksheet
    Set wsVendors = ThisWorkbook.Sheets("Vendors")
    Set wsServices = ThisWorkbook.Sheets("ServicesMaterials")
    Set wsSettings = ThisWorkbook.Sheets("Settings")
    
    Dim templateBody As String
    Dim i As Long
    For i = 2 To wsServices.Cells(wsServices.Rows.Count, 1).End(xlUp).Row
        If wsServices.Cells(i, 1).Value = cmbEmailType.Value And wsServices.Cells(i, 2).Value = cmbServiceMaterial.Value Then
            templateBody = wsServices.Cells(i, 3).Value
            Exit For
        End If
    Next i
    
    Dim ccEmail As String
    ccEmail = wsSettings.Range("B1").Value ' Assuming CC is in B1
    
    Dim vendorRow As Long
    For i = 0 To lstVendors.ListCount - 1
        If lstVendors.Selected(i) Then
            Dim vendorName As String
            vendorName = lstVendors.List(i)
            
            Dim emailList As String
            emailList = ""
            
            For vendorRow = 2 To wsVendors.Cells(wsVendors.Rows.Count, 1).End(xlUp).Row
                If wsVendors.Cells(vendorRow, 1).Value = vendorName And _
                   wsVendors.Cells(vendorRow, 2).Value = cmbServiceMaterial.Value Then
                   
                   emailList = emailList & ";" & wsVendors.Cells(vendorRow, 4).Value
                End If
            Next vendorRow
            
            Set OutlookMail = OutlookApp.CreateItem(0)
            With OutlookMail
                .To = Mid(emailList, 2)
                .CC = ccEmail
                .Subject = txtProjectName.Value
                .Body = templateBody
                .Display ' or use .Send to auto-send
            End With
        End If
    Next i
End Sub
'VBA Code is untested, please backup you file first.

My answers are voluntary and without guarantee!

Hope this will help you.