link drop down to specific email address in excel

[humbert]

I have 3 different options in the Column 'Actions' as a drop down

option 1 go to persons email
options 2 go to persons email
option 3 go to that persons email

depending on the option selected I would like the link to go to outlook and send that person an email

any help appreciated

[Logit]

.

Please Login or Register  to view this content.

[humbert]

Great amazing , thank you !

I am creating a spreadsheet that requires the user to select the appropriate email from the drop down under the specific name
The sheet may have up to 30 rows with names and details on each row

EG...When the user selects the email address drop down under bob smith , the email window pop up needs to include his name , employer and still to complete(rows B, C & E)

So the sheet could have several names every week being emailed to either email address 1, 2, or 3 and needs to include row B, C & E of that person.

If its too tricky to do this I can just have one email address in column D next to every name but the email needs to be send to that one person with the selected name and the contents of the individual (rows B, C & E)

hope that makes sense

[Logit]

.
Where are you wanting the information in B, C & E to populate ? In the email itself ? If so, where in the email ?

If somewhere else please specify.

[humbert]

in the main body of the email

[Logit]

.

Please Login or Register  to view this content.

[humbert]

Impressed!
Only a small thing though when I choose for example bob and specific email I only want B,C & E of Bobs line not the other entries.
Then i may go the next line Paige and choose a different email to just email her line of text only and so on.

[Logit]

.
HUMBERT:

Here is the updated macro :

Please Login or Register  to view this content.

Also, to make it easier for the user to mark which names will be sent in the email, I added another macro in the Sheet1 module that allows you to double-click the cell in Col A adjacent to the employee name to be emailed.
Double-clicking places a check mark in the cell. Double-clicking again, removes the check mark.

Also, note that this new macro utilizes the cells in AA1:AC1 as a temporary holding area for the data to be pasted into the email body. Those cells must not be used for any other purpose. If this poses
a problem for your project we can move these temporary holding cells to another location.

[humbert]

great many thanks for all your help, this will help me a great deal!
is it easy enough to update/run a macro to extend row numbers more or add or change the subject of the email I may do this in the future?

[Logit]

.
The macro is presently designed to check rows A4:A100 ... or 96 entries. If you desire to have additional names,
edit this line :

Please Login or Register  to view this content.

Change the A100 to the new max # rows. Ex: ("A4:A350") . This would allow you to have entries from row 4 to row 350.


To change the subject, you will edit this line of code :

Please Login or Register  to view this content.

Change "Test Subject" to your new subject line: "This Is My New Subject Line"


Glad to help.

[humbert]

HI

I'm very new to macros and I have tried to add this macro to my excel file
, this gives me two errors as attached , I can see the text in the comments box holds in the temp area AA but is unable to populate the email itself
, every week this will be ran with up to 30 names on a drop down list, and each have the option to possible send a separate email to one of the three email addresses with the contents of ROWS B, C & E
The worksheet itself will be used for the whole year so could have over 300 rows.


Is there any way to allow me to type in the boxes from column e, f, g and so on as it only takes this from entering the text on the formula bar(this maybe something simple sorry)

I also have my example excel file too if you need this

debug.jpg.pngruntime error.jpg.png

[humbert]

ignore last one, I had called my worksheet Sheet 1, with a space, this made all the difference

[Logit]

.
If your questions have been answered, please mark the thread as solved.

Merry Christmas !

[Logit]

.
Final version :

Please Login or Register  to view this content.