+ Reply to Thread
Results 1 to 12 of 12

Take contents of cells and use them to compose an email - Text Body Problem

  1. 10-21-2010, 08:42 PM #1
    nupema
    nupema is offline
    Registered User
    Join Date
    10-21-2010
    Location
    Portugal
    MS-Off Ver
    Office 365 & Google Sheets
    Posts
    23

    Take contents of cells and use them to compose an email - Text Body Problem

    Hello All,

    I need assistance with what appears to be a simple task, but I am unable to accomplish.

    I need to take contents of cells (which are results of formulas and references to other cells) and use the contents to compose an email.

    B3 (Named "SEND MAIL") contains the formula used to compose the email from the cells with formulas, however, I cannot add all the text that is in the cell "body" without an error. At some point, there is too much text (contained in the cell) and B3 returns #VALUE error.

    This is the formula I use:

    =IF(J7="LX";HYPERLINK("mailto:"&C7&"?cc="&Conteudo_Mail!$B$2&"&subject="&K7&"&body="&Conteudo_Mail!B4&"%0D%0A"&"%0D%0A"&Conteudo_Mail!B5&"%0D%0A"&"%0D%0A"&"%0D%0A";"MAIL LX");(IF(J7="PT";HYPERLINK("mailto:"&C7&"?cc="&Conteudo_Mail!$B$3&"&subject="&K7&"&body="&Conteudo_Mail!B4&"%0D%0A"&"%0D%0A"&Conteudo_Mail!B5&"%0D%0A"&"%0D%0A"&"%0D%0A";"MAIL PT"))))

    The above formula works now, because it doesn't contain all of the body (of email) I need. :-(

    Does anyone have any suggestions? Is there a better (but simple) approach?

    Thank you in advance for your help.

    Sincerely.
    Reply With QuoteRegister To Reply
  2. 10-21-2010, 09:05 PM #2
    Paul
    Paul is offline
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Take contents of cells and use them to compose an email - Text Body Problem

    Hi Nupema, welcome to the forum.

    Can you use a macro to send your e-mail instead of using a hyperlink formula? The following site reviews many ways to send e-mail from Excel:

    http://rondebruin.nl/sendmail.htm
    - Paul

    Click here to read the Forum Rules

    Reply With QuoteRegister To Reply
  3. 10-22-2010, 04:18 AM #3
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Take contents of cells and use them to compose an email - Text Body Problem

    HYPERLINK link_location is restricted to 255 chars.
    I am as yet unaware of any workaround to this limitation...

    If as implied you need to exceed that then as outlined by Paul VBA may be viable.
    A VBA approach does however assume (to some extent) that the mail clients of all machines using the code can in turn be controlled via VBA (and are known in advance).
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  4. 10-22-2010, 05:24 AM #4
    nupema
    nupema is offline
    Registered User
    Join Date
    10-21-2010
    Location
    Portugal
    MS-Off Ver
    Office 365 & Google Sheets
    Posts
    23

    Re: Take contents of cells and use them to compose an email - Text Body Problem

    Hello Paul, and DonkeyOte. Thanks for answers.

    Before I posted, I was allready in Ron de Bruin searching for a solution.

    VBA runnning on machines, is not a problem. All the machines can run it..

    Unfortunately, VBA is not part of my knowledge in Excel. But any way, is there a way to make a VBA macro that do the same verification that the formula I use? I kind of.. IF cell J7="LX" send mail to X.. IF cell J7="PT" send mail to Y..?

    Best regards.
    Last edited by Paul; 10-22-2010 at 10:50 AM. Reason: Removed quote of full post.
    Reply With QuoteRegister To Reply
  5. 10-22-2010, 05:40 AM #5
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Take contents of cells and use them to compose an email - Text Body Problem

    nupema, if you opt to post the same question on multiple boards please have the courtesy to disclose all relevant links:

    http://www.mrexcel.com/forum/showthread.php?t=503511

    the same holds true at MrExcel (and anywhere else you've posted this)
    Reply With QuoteRegister To Reply
  6. 10-22-2010, 09:28 AM #6
    nupema
    nupema is offline
    Registered User
    Join Date
    10-21-2010
    Location
    Portugal
    MS-Off Ver
    Office 365 & Google Sheets
    Posts
    23

    Re: Take contents of cells and use them to compose an email - Text Body Problem

    Apologize didn't note your are in both forums
    Last edited by Paul; 10-22-2010 at 10:50 AM. Reason: Removed quote of full post.
    Reply With QuoteRegister To Reply
  7. 10-22-2010, 09:34 AM #7
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Take contents of cells and use them to compose an email - Text Body Problem

    The reason we ask you post links is so that the free time being donated by those willing to help you is not wasted answering something already resolved elsewhere.
    Reply With QuoteRegister To Reply
  8. 10-22-2010, 11:04 AM #8
    Paul
    Paul is offline
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Take contents of cells and use them to compose an email - Text Body Problem

    You may want to read this...

    http://www.excelguru.ca/node/7

    The following code (adjusted from here: http://www.rondebruin.nl/mail/folder3/smallmessage.htm ) may help you out.
    Please Login or Register  to view this content.
    You can add additional Case's in the Select Case statement if you have to check for more than just LX and PT. If the body of the e-mail must change, get rid of the first "srtbody" value and change the ".Body = " value in each case separately.

    Hope that helps. If it does, please let the other forums know, too.
    Reply With QuoteRegister To Reply
  9. 10-22-2010, 05:59 PM #9
    nupema
    nupema is offline
    Registered User
    Join Date
    10-21-2010
    Location
    Portugal
    MS-Off Ver
    Office 365 & Google Sheets
    Posts
    23

    Re: Take contents of cells and use them to compose an email - Text Body Problem

    WOW!! my low knowledge of programming language tell me this seem's very nice!

    I read the code and think I get it..

    But now I have a problem, how do I get this VBA code to work with my Excel sheet?

    I can enter developer mode and past the vba code.. no problem.

    But how can I make it work?

    thank you
    Last edited by nupema; 10-23-2010 at 07:17 PM. Reason: Removed quote of full post.
    Reply With QuoteRegister To Reply
  10. 10-29-2010, 06:09 AM #10
    nupema
    nupema is offline
    Registered User
    Join Date
    10-21-2010
    Location
    Portugal
    MS-Off Ver
    Office 365 & Google Sheets
    Posts
    23

    Re: Take contents of cells and use them to compose an email - Text Body Problem

    Finally is working! 

    Please Login or Register  to view this content.
    A just need help with two more things please...

    First:

    I know how i can put a function to attach a file into the email, but I need the search the file equal to a cell value and than attach the file to the email.

    Something like: search pdf "file name" equal to F7 cell, (see jpg in attach) and than attach.


    Second:

    And how can I apply the code to all lines, so i dont have to put 200 buttons (one for which line)?

    Thank you, very much...
    Attached Images Attached Images
    Last edited by nupema; 10-29-2010 at 06:13 AM.
    Reply With QuoteRegister To Reply
  11. 10-29-2010, 08:20 AM #11
    nupema
    nupema is offline
    Registered User
    Join Date
    10-21-2010
    Location
    Portugal
    MS-Off Ver
    Office 365 & Google Sheets
    Posts
    23

    Re: Take contents of cells and use them to compose an email - Text Body Problem

    Finally is working! 

    Please Login or Register  to view this content.
    A just need help with two more things please...

    First:

    I know how i can put a function to attach a file into the email, but need to search the file equal to a cell value and than if it is equal, attach the file to the email.

    Something like: search pdf file name equal to F7 cell and than attach.


    Second:

    And how can I apply the code to all lines, so i dont have to put 200 buttons (one for which line)?
    Reply With QuoteRegister To Reply
  12. 10-31-2010, 12:24 PM #12
    nupema
    nupema is offline
    Registered User
    Join Date
    10-21-2010
    Location
    Portugal
    MS-Off Ver
    Office 365 & Google Sheets
    Posts
    23

    Re: Take contents of cells and use them to compose an email - Text Body Problem

    Anyone help? please...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1