Announcement

Collapse
No announcement yet.

HYPERLINK=mailto: - getting info from cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Hi Dan and welcome,

    Try this,

    Code:
    =HYPERLINK(CONCATENATE("mailto:",B1,"?subject=Submission Ref Number ",A1,"&body=Hello ",C1))

    Cheers
    Andy

    Comment


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

      Thank you that is wonderful. I appreciate the help.

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

      -dan

      Comment


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

        Also, is there a limit as to how large the concatinated string can be. I keep getting errors (#VALUE!) if one of the fields is too long.

        -dan

        Comment


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

          I would imagine there is a 255 limit on the combined length of the link.

          Cheers
          Andy

          Comment


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

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

            Code:
            =HYPERLINK("mailto:"&E3&"?Subject="&E6&"&CC="&E4&"&Bcc="&E5&"&Body="&E7,"Click on this Hyperlink to send the Email to all List")
            while B1 to B100 are email addresses, E4 is this;
            Code:
            =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
            Last edited by muratis; November 29th, 2012, 01:11. Reason: providing an example

            Comment


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

              with thanks to http://datapigtechnologies.com/blog/...link-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”)





              Originally posted by Dan Partain View Post
              Thank you that is wonderful. I appreciate the help.

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

              -dan

              Comment


              • #8


                Re: HYPERLINK=mailto: - getting info from cells

                Nice information, thank you for sharing it.

                termite control chennai
                termites control in chennai

                Comment

                Working...
                X