I have a list of employees A3:A23 and G3:G23 who must stay current in observations (B and H). If they go above 0 in E and K (observations Due) I want to email myself their names A3:A23 and G3:G23. If they don't go above 0 then nothing.

I can get an email sent using =IF(E3<"0",HYPERLINK("mailto:myemail""?subject="&N1&"&body="&A3&B3,"Preview Email")) but that only gives me one name not a range and the if function doesn't work. I will give me and name regardless of what number E3 is.

I have a list of employees A3:A23 and G3:G23 who must stay current in observations (B and H). If they go above 0 in E and K (observations Due) I want to email myself their names A3:A23 and G3:G23. If they don't go above 0 then nothing.

I can get an email sent using =IF(E3<"0",HYPERLINK("mailto:myemail""?subject="&N1&"&body="&A3&B3,"Preview Email")) but that only gives me one name not a range and the if function doesn't work. I will give me and name regardless of what number E3 is.

• excel
• email
• range
• cell

1 Answer 1

The answer to the first part of this (the IF statement not working) is that the "0" should not be in quotations. So, like =IF(E3<0,HYPERLINK("mailto:myemail""?subject="&N1&"&body="&A3&B3,"Preview Email")).

However, I don't think you need to use the IF statement at all. To do what you are looking for, you need to use the HYPERLINK function, and inside that nest FILTER and ARRAYTOTEXT.

As an example: =HYPERLINK("mailto:myemail""?subject="&N1&"&body="&ARRAYTOTEXT(FILTER(A3:A23,B3:B23<0)),"Preview Email")

This produces an email with all the names that meet the criteria in the email.

The FILTER function finds the names of people who meet the criteria, and it returns an array. To get them all in one email, the ARRAYTOTEXT function puts them all together.