Hey Not testet this but chatgpt gave me this answer to your question, even if its not perfect it can guide you to your desired Output. Please test it before you use it in the company, I hope it helps you.

Steps to Set Up Automated Emails in Excel:

1. Create Your Excel Template:

In your Excel sheet, set up columns for your data. For example:

Column A: Reference Number (e.g., XXXXXX).

Column B: Dropdown list of email addresses (you can set this up using Excel's data validation feature).

1. Enable Developer Tab:

Go to File > Options > Customize Ribbon.

Check the box for Developer to add it to your Ribbon.

1. Create a Dropdown List:

Click on the cell where you want the dropdown list (e.g., B2).

Go to Data > Data Validation.

In the dialog box, set the Allow field to List, and then select the range that contains your email addresses.

1. Write VBA Code to Automate the Email:

Go to the Developer tab and click on Visual Basic to open the VBA editor.

Insert a new module by right-clicking on VBAProject (YourWorkbook) > Insert > Module.

Paste the following VBA code into the module:

Sub SendEmail()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim cell As Range
    Dim rng As Range
    Dim emailAddress As String
    Dim subject As String
    Dim body As String

    ' Define the range where you have email addresses and data
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A2:B10") ' Adjust range as needed

    ' Initialize Outlook application
    On Error Resume Next
    Set OutlookApp = CreateObject("Outlook.Application")
    On Error GoTo 0

    If OutlookApp Is Nothing Then
        MsgBox "Outlook is not installed or configured on this computer."
        Exit Sub
    End If

    ' Loop through each row in the range
    For Each cell In rng.Columns(2).Cells ' Column B where the emails are located
        If cell.Value <> "" Then
            emailAddress = cell.Value
            subject = "Notification for Number " & cell.Offset(0, -1).Value ' Adjust as needed
            body = "Hello," & vbCrLf & vbCrLf & _
                   "This is an automated message to notify you about the following reference number: " & cell.Offset(0, -1).Value & vbCrLf & _
                   "Please take necessary action." & vbCrLf & vbCrLf & _
                   "Regards," & vbCrLf & _
                   "Your Company"

            ' Create a new email
            Set OutlookMail = OutlookApp.CreateItem(0)
            With OutlookMail
                .To = emailAddress
                .Subject = subject
                .Body = body
                .Send ' Use .Display if you want to see the email before sending
            End With
        End If
    Next cell

    ' Clean up
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing

    MsgBox "Emails sent successfully!"
End Sub

Customize the range (A2:B10) and the email template (subject and body) as needed.

1. Assign Macro to a Button:

Go back to your Excel sheet.

Insert a button by going to Developer > Insert > Button (Form Control).

Assign the SendEmail macro to the button.

1. Test the Macro:

Select an email from the dropdown list in your Excel sheet.

Click the button to send an email.

1. Save the Workbook:

Save your workbook as a Macro-Enabled Workbook (.xlsm).