Hyperlink disappearing when certain conditions are met

Hello, I am using a hyperlink formula to create a hyperlink to send an email to an email address that is specified in another cell. The email also pulls information from other cells to put into the body of the email, but the "Generate Email" hyperlink I created seems to be disappearing when the person it is sending the email to reaches 6 pulled values that must be put into the body (It works fine for 5, but when I enter data into the 6th cell the hyperlink disappears completely and will not let me click it, though the cell still shows the formula when its selected. I have attached a copy of the formula I am using.

=IFERROR(HYPERLINK("mailto:" & $M2 & "?subject=360 Reviews" & "&body=Hello "&VLOOKUP($L2,$H$1:$J$102,3,FALSE)&","&"%0A%0AYou have been chosen to be a 360 Reviewer for the following employee(s):%0A%0A"&UniqueListTranspose!$B2
&"%0A"&UniqueListTranspose!$C2 &"%0A"&UniqueListTranspose!$D2 &"%0A"&UniqueListTranspose!$E2 &"%0A"&UniqueListTranspose!$F2 &"%0A"&UniqueListTranspose!$G2 &"%0A"&UniqueListTranspose!$H2 &"%0A"&UniqueListTranspose!$I2 &"%0A"&UniqueListTranspose!$J2 &"%0A"&UniqueListTranspose!$K2, "Generate Email"),"")

EDIT* I've just discovered another interesting problem with this. When I add more cells to reference in my formula (For example, above is B2-K2 in sheet UniqueListTranspose), such as expanding from B2-Z2, I am only allowed 1 value to be pulled, a second breaks the hyperlink. Similarly, B2-Q2 allows for 3 values before breaking when a 4th value is added. This is extremely counter intuitive as I need to be able to reference roughly 30 cells in the formula to return all of that data in the email, but the more cells I attempt to call to in my formula, the fewer I can actually have it return.

There is a limit to the length of a hyperlink formula, I can't remember what it is offhand.

Last time I suggested vba to replace the hyperlink formula with a text string and a proper hyperlink.

I'll see if I can find the last thread where this happened.

edit:- this was the thread, but the OP didn't return to confirm if it worked or not, this was for opening the link in a browser so will probably need some changes to do what you need.

If this is an option that you can use then we can look into modifying it to fit your requirement.

Hey Jason,

Does this work for a formula that is referencing a multitude of cells? For example, I have people changing the contents of a table, and then the link should update with what those cells contain now, thus altering the mailto hyperlink body contents.

Here's what I want it to do:
1. Pull the email address from M2
2. Create the subject "360 Reviews"
3. Create the body as follows
Line 1: Hello (add name pulled from vlookup that checks the email address we are sending it to, and the associated first name. This comes from range H2:J102 and the return column is 3),
Line 2: Line break
Line 3: You have been chosen to review the following employees:
Line 4: Line break
Line 5: Values in Sheet3 cells A2:CC2 - with a line break following each cell value (For example, line 5 would be cell A2, but B2 would be on the next line and so on)

The tricky part is that I am essentially looking for this to create a button next to each employees name, so that I can click it and it will generate an email to that employee. In other words, I was trying to create a formula and drag it down 115 cells so that each person would have a unique button, I'm not sure if this is possible with VBA - maybe one "button" could send an email to all of them?

If possible, I would also like to add my default signature to the email (I'm using outlook)

You need to desensitise the data - change names to Person 1 and E-Mails to [email protected], etc.

We won’t need a full dataset, anyway.

Even if it were possible to send a file by PM, you would still need to desensitise it: this is a public forum and you should never trust any of us with other people’s personal details.

Jason,

I was able to deduce why it would not even attempt to send the emails. The macro was referencing a cell which contained a vlookup to determine the email address to send it to, so I had to change the macro from .Cells.SpecialCells(xlCellTypeConstants) to .Cells.SpecialCells(xlCellTypeFormulas).

The only remaining issue is that it is not recognizing the line breaks and prints out the "%0A" in the email itself.

*EDIT - After we have the line breaks figured out, I was wondering if it was possible to add a commented out line for where I can place an attachment if I decide to add it in later.

Formulas to make up the email addresses was something that I hadn't given consideration to. Like Ali said, you should never provide files with personal data to anyone on the site (or any other site), but without the original file to test, I would never have found it. Best suggestion is to create a copy of the original using Save As, then remove anything from the copy that is not needed, but leave the formulas in place in places where they are related to the question, overwriting your real data with fiction.

The %0A part was something that I didn't recognose, so I just left it in from your existing formula, I assumed that it was something that you had found elsewhere.

Not sure if it will be recognised when it is passed to outlook, but try using CHAR(10) as the delimiter instead of "%0A".

Note that there are no double quotes around CHAR(10), excel needs to see it as a function, not as text.

If that comes out on a single line as well, take a look at 'Method 1' here, https://support.microsoft.com/en-gb/...mat-in-outlook before trying any other alternatives.

I ended up changing the VBA to use .HTMLBody and changed %0A (which Outlook generally accepts as a line break) to <br> in order to work properly. I also used this to add my default signature to the file.

The last thing I need (so close!) is the addition of a commented out line that I can use later on to add unique files for each email being sent. I know how to add a single file that is the same for each email, but that is not what i am looking for. I would assume I must create a new column in excel with all of the locations and use that in my Attachments.Add line?

Sorry, I ended up changing my mind and editing out my question - I didn't realize you saw what I had originally said. The files will all be located in the same folder, with the naming convention FilenameFirstnameLastname and I can very easily add everyone's first and last names to the spreadsheet if necessary. I am only confused about the actual edit to the VBA that I will need, I'm not worried about tweaking the specific locations that it searches and such. Here's what I currently have that works exactly how I would like it to (excluding the attachments, of course)

Please Login or Register  to view this content.

But wouldn't the code you suggested use the same file for everyone? Email addresses are in the form of [email protected]. Just be sure we're on the same page - I have a folder on my desktop called "360 Reviews" with files such as ReviewJakePetersen.docx and so on. If Jake Petersen happens to come up in the email list that I generate, I want his email to have the attachment just mentioned on it. But I also want any other person on the email list to have their own ReviewFirstLast attached to their email.

Would it work to do something similar to what we did with the body line, but for the attachment? I can create a new column that will run the vlookup to associate the file name with the person selected and print the name in the cell (say, N2 for example). But I will then need the VBA to look for the attachment in N2 for the first email, N3 for the second email, and so on.

I was just about to suggest this, then re-read your reply and noticed that email is only initial, not full name.

Please Login or Register  to view this content.

edit:- I've changed the line of code above so that it takes first name from the first part of the formula in column N by extracting whatever is between "Hello " and the first comma, "Hello FirstName,", then takes the last name from the email address in column M, taking the second character, up to the one before the @ symbol.

Once you edit the part in red so that it matches the correct location for the folder on your desktop, it should (I hope) work as needed.