hi all,
i am trying to do this without vba or macros.
Mailto hyperlink: [table one] has the drop down list of names, information columns and email link on the same row. [Table two] has the email hyperlinks referenced by the email link in ]table one] that's based on the name selected from the drop down list on [table one]. In the email body, I need to reference the cells in the row where the email link was clicked (column P) to be added to the email. i need the reference to reflect the row activated email link is located.
right now i have a bit of a mess that works until i try to reference the cells according to row.
can this even be accomplished without vba and macros?
Thanks for any help you can provide!
EDIT: Excel version is microsoft 365 version 2305
first sheet:
second sheet:
i am trying to do this without vba or macros.
Mailto hyperlink: [table one] has the drop down list of names, information columns and email link on the same row. [Table two] has the email hyperlinks referenced by the email link in ]table one] that's based on the name selected from the drop down list on [table one]. In the email body, I need to reference the cells in the row where the email link was clicked (column P) to be added to the email. i need the reference to reflect the row activated email link is located.
right now i have a bit of a mess that works until i try to reference the cells according to row.
can this even be accomplished without vba and macros?
Thanks for any help you can provide!
EDIT: Excel version is microsoft 365 version 2305
first sheet:
| mailto_alt_send.xlsx | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
| 10 | From | info1 | info2 | info3 | Issue | info4 | info5 | info6 | info7 | info8 | Comments | info9 | info10 | info11 | info12 | info13 | |||
| 91 | Ethan | test2 | fix 2 | Ethan | |||||||||||||||
| 92 | Jeff | test | fix test | Jeff | |||||||||||||||
L_Requests | |||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| P91:P92 | P91 | =HYPERLINK("mailto:" & VLOOKUP([@From], 'Contact emails'!A:B, 2, FALSE),[@From]) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| A19:A93 | List | ='Contact emails'!$A$2:$A$13 |
second sheet:
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B13 | B2 | =HYPERLINK("mailto:" &[@[Scorp_email]]&"?subject=" &"Updated Request"&"&cc=" &'Contact emails'!E2&'Contact emails'!D2 & "&body=" &"your request & 'L_Requests'!E92 & sheet1 table reference here and sheet1 table cell2 reference here & 'L_Requests'!K92") |
