+ Reply to Thread
Results 1 to 14 of 14

Send part of Excel-Sheet via customized E-Mail

  1. #1
    Forum Contributor
    Join Date
    08-31-2022
    Location
    Swiss
    MS-Off Ver
    365
    Posts
    138

    Exclamation Send part of Excel-Sheet via customized E-Mail

    Dear members of the forum,

    I would like to send part of an Excel-Sheet via a button per E-Mail. The E-Mail should be customizable (e.g. enter a customized text before sending), the recipient is always the same.
    A sample-sheet is in the attachments - is something like this possible?

    Thank you in advance and best regards,

    David
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    91,060

    Re: Send part of Excel-Sheet via customized E-Mail

    This will require VBA - shall I move the thread for you?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    08-31-2022
    Location
    Swiss
    MS-Off Ver
    365
    Posts
    138

    Re: Send part of Excel-Sheet via customized E-Mail

    Hi Ali,
    yes, that would be great - thank you! And sorry for using the wrong topic...

  4. #4
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2024
    Posts
    982

    Re: Send part of Excel-Sheet via customized E-Mail

    Hi there,

    Please refer to the attached file.

    I have used a Userform. This gives you more flexibility than just an input box.

    The default email address and topic are setup in the Module code, but can be overwritten in the UserForm if required.

    For the message, there are 4 boxes. This allows for some simple structuring in the email. Each box will show in a separate line (if it was only one box, all the test would be in one line, which could be a bit messy if the message is longer.
    The last box is a little offset, allowing a line separator for the sign off.

    Let us know how you go with this.
    Attached Files Attached Files
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

  5. #5
    Forum Contributor
    Join Date
    08-31-2022
    Location
    Swiss
    MS-Off Ver
    365
    Posts
    138

    Re: Send part of Excel-Sheet via customized E-Mail

    Hi ORoos,

    first of all - thank you very much for this amazing macro!
    It works perfectly fine in your worksheet.

    I have tried to implement it into my worksheet (just be drag and drop in VBA) but somehow I always get a runtime error in my worksheet(as in the attachment).

    How do I transfer your macro into my worksheet so that it also works?

    Thank you in advance and best regards
    Attached Images Attached Images

  6. #6
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2024
    Posts
    982

    Re: Send part of Excel-Sheet via customized E-Mail

    Gruezi mrdaave,

    You need to copy the module as well as the userform across to your actual workbook.
    If drag & drop does not work. You can 'right-click' on each of them and export, then import in the new file.
    Having said that, a missing userform would give you a different error..

    The error highlighted in yellow is the part to generate the actual pdf file/attachment.

    Try to set another file path for the temporary file. Change strPath = "C:\Users\xxxxxxxx\Downloads"
    With a fixed path (make sure it is valis and replace the xxxxxx with the correct username, you don't relay on the Envirion$ command.

    Let us know if that helps.

  7. #7
    Forum Contributor
    Join Date
    08-31-2022
    Location
    Swiss
    MS-Off Ver
    365
    Posts
    138

    Re: Send part of Excel-Sheet via customized E-Mail

    Grüezi ORoos,

    happy to hear some Swiss-German

    I've worked with your Step-by-Step-instruction and I think that it works now!
    I just have to change the path to one that everybody can use (also different users) but I think that this will be some try and error.

    I will play with the macro for a little bit longer and will close the thread if I cannot find any more issues.

    Thank you for your tremendous help and best regards,

    David

  8. #8
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2024
    Posts
    982

    Re: Send part of Excel-Sheet via customized E-Mail

    Gruezi David,

    The advantage of using the Environ$("temp") option is that it will be user specific, hence dynamic.

    Try to run the code below and check the Immediate window for the result:

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    08-31-2022
    Location
    Swiss
    MS-Off Ver
    365
    Posts
    138

    Re: Send part of Excel-Sheet via customized E-Mail

    Hoi ORoos,

    please excuse this basic question: but I just made a new Module and entered your code into it and let it run.

    If thats correct -> nothing happens

    If I made something wrong -> I think I would need a little bit more help

  10. #10
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2024
    Posts
    982

    Re: Send part of Excel-Sheet via customized E-Mail

    Sali David,

    Yes, adding the short code into a new module is fine. You could also just add it at the bottom of the existing one.

    I assume you have the Immediate window open. IF not, in the VBA Editor, use Ctrl + G to open it.
    The information in the code starting with Debug.Print should be writing there. Nothing else is happening. We just try to see if we can read the information from the system or not.
    If we can't read the environ info, then we need to go back to using a hard-coded file path for the temp pdf file.

    PS: May not work with MAC.
    Last edited by ORoos; 04-02-2024 at 09:53 AM.

  11. #11
    Forum Contributor
    Join Date
    08-31-2022
    Location
    Swiss
    MS-Off Ver
    365
    Posts
    138

    Re: Send part of Excel-Sheet via customized E-Mail

    Guete Tag ORoos,

    I got it - thank you for the Ctrl + G, so there WAS something happening, I just wasnt able to see it at the beginning

    And I am also able to read the information from my system. But somehow if I use Environ(temp) I get a runtime error.
    If I use any hard copy path I am able to use the macro.

    Is it possible to use a server adress where everybody has access?

    PS: I am using Win10
    Last edited by mrdaave; 04-02-2024 at 10:12 AM.

  12. #12
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2024
    Posts
    982

    Re: Send part of Excel-Sheet via customized E-Mail

    Schoene Abig David,

    If you get a valid file path in the first row of the Immediate window, it should work theoretically.
    Also, given that it all works with the sample file, the folder/path and pdf generation should work...

    Glad to hear it works with a hard-coded path. In regards to a shared server location, as I am not on a server, this is something for you to explore and test.

    Viel Glueck

    PS: Thanks for the Rep.

  13. #13
    Forum Contributor
    Join Date
    08-31-2022
    Location
    Swiss
    MS-Off Ver
    365
    Posts
    138

    Re: Send part of Excel-Sheet via customized E-Mail

    Schöna Vormittag ORoos,

    it seems to work just perfectly fine!
    Thank you again for your tremendous help

    Have a nice week and best regards,

    David

  14. #14
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2024
    Posts
    982

    Re: Send part of Excel-Sheet via customized E-Mail

    Gärn gschee David.

    Also thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel MAC 2011 send mail with sheet as PDF to list of contacts
    By danwoltrs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2015, 02:22 PM
  2. How to Automatically Mail customized content from excel?
    By Asterix_Obelix in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-22-2012, 01:57 AM
  3. How to e-mail selected row and use e-mail address in a cell to send e-mail from excel
    By syedalamgir in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-27-2010, 02:15 AM
  4. Code to send attachments to mail id s listed on excel sheet
    By chaitanya051 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-10-2010, 04:20 AM
  5. Send mail from excel sheet to oulook automatically
    By hai_krk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-21-2009, 03:42 AM
  6. [SOLVED] cannot send excel worksheet (sheet only) to mail recipient
    By Nancy in forum Excel General
    Replies: 1
    Last Post: 11-09-2005, 05:00 PM
  7. How Can I send customized email content by Excel VBA
    By new.microsoft.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-12-2005, 04:06 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1