HYPERLINK=mailto: - getting info from cells

  • Hello, this is my first post here. Wonderful site you got going here.


    My problem is this:
    I work for a Medical journal and we are keeping all of our records in an Excel file. (I would rather it in a database, but this being a large company we have to work with what we got). So, I have a rather large worksheet with contacts, email addresses, various dates, and other random stuff that I need to keep track of. Now I have to add a new record when a new paper comes in, then update it when I get revisions and such. Everytime I get some new information I need to email the Author to inform them we have the info.


    What I would like to do is to write a nice little (ok it will probably be long) formula that will grab fields in that row and add them to an formula. Then I can click that cell and an email will pop up and I can just check it for accuracy before I push send. I would probably have one of these for every row.


    So lets say I have a Reference Number in Col A, The email address in Col B, and The persons name in Col C. Is it possible to use the HYPERLINK=("mailto:Col B?subject=Submission Ref Number+ "Col A":?body=Hello+" Col C",friendly_name )


    Now, I am pretty sure that my syntax is off but I hope that my question comes across clear.


    As a note, the reason I am doing it using the mailto and not exploring a VB route is that I don't have access to VB here at work.


    Thanks for the help,
    Dan

  • Re: HYPERLINK=mailto: - getting info from cells


    I was able to concatenate 6 email addresses and no more... is this an excel bug?


    Code
    1. =HYPERLINK("[EMAIL=""&E3&"?Subject="&E6&"&CC="&E4&"&Bcc="&E5&"&Body="&E7,"Click"]mailto:"&E3&"?Subject="&E6&"&CC="&E4&"&Bcc="&E5&"&Body="&E7,"Click[/EMAIL] on this Hyperlink to send the Email to all List")


    while B1 to B100 are email addresses, E4 is this;

    Code
    1. =SUBSTITUTE(SUBSTITUTE(CONCATENATE(B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16,B17,B18,B19,B20,B21,B22,B23,B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B36,B37,B38,B39,B40,B41,B42,B43,B44,B45,B46,B47,B48,B49,B50,B51,B52,B53,B54,B55,B56,B57,B58,B59,B60,B61,B62,B63,B64,B65,B66,B67,B68,B69,B70,B71,B72,B73,B74,B75,B76,B77,B78,B79,B80,B81,B82,B83,B84,B85,B86,B87,B88,B89,B90,B91,B92,B93,B94,B95,B96,B97,B98,B99,B100),E3,""),E5,"")


    This hyperlink only works when I have only 6 email addresses in the range of B1:B100

  • Re: HYPERLINK=mailto: - getting info from cells


    with thanks to http://datapigtechnologies.com…g-the-hyperlink-function/ :


    If you need to add a multi-lined body (meaning the text of your body is on multiple lines), you can use the carriage return encoding (%0A).
    =HYPERLINK(“mailto:[email protected], [email protected][email protected]&[email protected]&subject=Email To DataPig&body=I ate too much salmon.%0ANow I have salmonella “, “Send Email”)





    Quote from Dan Partain;132389

    Thank you that is wonderful. I appreciate the help.


    Is there by chance a way to place a carrige return in that formula?


    -dan