VBA Script to choose the value from a Drop Down list automatically

Question

I have an Excel Workbook with the 2 sheets.

Sheet 1 contains an Email template and sheet 2 contains the raw data.

Sheet 2 contains the headers as mentioned below. Name ERP Employee Date Email Start Date Is Anniversary(Yes/No) Years Completed

In Sheet 1, I have created a drop-down list where the list will contain the names of the Employees who have the Is Anniversary field (yes)

I have written a VBA script to send an Email to the person who is selected in the drop-down list.

I would like to make it fully automated where the script should be able to select the next value automatically and then send the Email to the person and likewise for the whole list. Please suggest if there is a possibility.

Script for sending the Email

Sub Send_Anniversary_Email()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Mailer")

Dim lr As Integer
lr = sh.Range("J" & Application.Rows.Count).End(xlUp).Row

sh.Range("E5:L" & lr).Select
With Selection.Parent.MailEnvelope.Item

    .to = sh.Range("B12").Value
    .cc = sh.Range("B13").Value
    .Subject = sh.Range("B14").Value
    .send

    ActiveWorkbook.Save
End With

MsgBox "Done"



End Sub

Can you please help me with the script? Below is the script I have written for sending an Email. ---------------------------------------------------------------------------------------------------------------- — C B S VAMSI KRISHNA
Please edit your question instead of posting code in comments. — BigBen
Posted the script above. The cell "B12" will contain the Email address of the person the Email will be sent. So, based on the drop-down selection, the email address will be populated. — C B S VAMSI KRISHNA
So the "Mailer" sheet Range B12 is the dropdown recipient mail id list which source data is on some other sheet. (Raw Data as mentioned). What if there are more than one anniversaries on the same date? I mean instead of selecting from a dropdown list, won't you consider a loop on the recipient mail id list column in Raw Data. Say, for each recipient if Isanniversary is yes then send mail? Also, a confirmation message box before sending each such mail will be a good idea? — Naresh

PeterS PeterS · Accepted Answer · 2020-05-29T08:39:57

I have tried to create the same coding but once i try to send an email to a second person, i get the "Mailenvelope of object_worksheet failed" from this "With Selection.Parent.MailEnvelope.Item" code. What am i supposed to do here?

VBA Script to choose the value from a Drop Down list automatically

1 Answers