I need assistance with what appears to be a simple task, but I am unable to accomplish.
need to take contents of cells (which are results of formulas and references to other cells) and use the contents to compose
Here are some cells to illustrate. NOTE: the information in brackets is the formula that's contained in
the cell, while the text is the result of the formula.
B3 = This is the link used to compose an email.
C19 = email@example.com (an email address)
C22 = Login expired on node citrix08. [=concatenate("Login expired on node ",C7,".")]
C25 = Dear Company A, [=concatenate("Dear ",C6,",")]
C27 = The login for citrix08 has expired. [=concatenate("The login for ",C7," has expired.")
C29 = Contact us within 15 days to continue. [=concatenate("Contact us within ",C9," days ...]
C31 = Ticket 444 has been created to track the issue. [=concatenate("Ticket ",C10," has...]
C33 = Lots of other text
D33 = More text
E33 = More text
C48 = Sincerely,
B3 (Named "SEND MAIL") contains the formula used to compose the email
from the cells above, however, I cannot add all the cells to the formula without an error. At some point, there is too much
data (contained in the referenced cells) and B3 returns #VALUE error.
=HYPERLINK("mailto:" & C19 &"?subject=" &
C22 & "&body=" & C25 & "%0D%0A" & "%0D%0A" & C27 & "%0D%0A" & "%0D%0A" & "%0D%0A", "SEND MAIL")
The above formula
works now, because it doesn't contain all of the body (of email) I need.
When I click on B3, it properly launches
the email client, puts in an address, subject, and two lines of text in the body.
My workaround to this problem is
to manually highlight cells C29 through E48, select copy, then go to the partially formed email and paste the rest of the
Is there a way to include all the body into an email? I think this can be accomplished with a VBA code, but
have no idea how to do it.
I dont want to send the mail, just compose it using cells that have calculated what is
needed in the body. I can then read the email, confirm it's content, and click send in the email client manually.
Does anyone have any suggestions? Is there a better (but simple) approach?
I know this could be done via
I do know excell quite well and have many cells to calculate what is needed in the body of the email, but, alas,
still can't compose the ENTIRE email from the formula in B3.
Thank you in advance for your help.