Hyperlink mailto #value error

Question

I'm trying to send email dynamically via Hyperlink mailto formula everything is going good but when I inserted the subject via vlookup. Its giving me #Value error, furthermore when I remove some alphabets from the cell where the text is inserted for the subject error removes but when I again insert my desired text length to the cell its giving me error again.

=(HYPERLINK("mailto:"&VLOOKUP(L6,'EmailTO&CC'!A:C,2,FALSE)&"?subject="&Email!A19&" "&Email!B19&" "&Email!C19&" "&Email!D19&"&cc="&VLOOKUP(L6,'EmailTO&CC'!A:C,3,FALSE),"Sent_Temp"))

Answer 1

Hello El Jebali

Welcome to Microsoft Community.

According to your current description, it seems that Hyperlink mailto formula is used in Excel to send mail, but if the subject is inserted via vlookup, you get the #VALUE error.

The #VALUE error is occurring because the value being returned by the VLOOKUP function is not a valid input for the mailto hyperlink formula. There are a few things you can try to resolve this issue:

• Ensure that the VLOOKUP function is returning a valid email address: Double-check that the value being returned by the VLOOKUP function is a properly formatted email address. If the value is not a valid email address, this could be causing the error.
• Check for trailing spaces: It's possible that the value being returned by the VLOOKUP function contains trailing spaces, which could be causing the error. You can try using the TRIM function to remove any extra spaces from the value.
• Use CONCATENATE function instead of "&": Instead of using "&" to concatenate the different elements of the mailto hyperlink formula, you can try using the CONCATENATE function. This can sometimes help to avoid errors with formatting.

Here's an example of how you could modify your formula using the CONCATENATE function:

=(HYPERLINK(CONCATENATE("mailto:", VLOOKUP(L6,'EmailTO&CC'!A:C,2,FALSE), "?subject=", Email!A19, " ", Email!B19, " ", Email!C19, " ", Email!D19, "&cc=", VLOOKUP(L6,'EmailTO&CC'!A:C,3,FALSE)), "Sent_Temp"))

I hope this helps! assuming that the above formula does not work, could you please share this file with me. It would be my pleasure to take a look at it. See your private message here:

Chandy |Microsoft Community Support Specialist