+ Reply to Thread
Results 1 to 16 of 16

Select range in excel and save as text file & email - Macro

  1. #1
    Registered User
    Join Date
    04-27-2015
    Location
    Houston, Texas
    MS-Off Ver
    2013
    Posts
    49

    Cool Select range in excel and save as text file & email - Macro

    Hello VB gods,
    I have searched past posts but have found nothing quite suited to help with my macro solution.

    In essence, I want a macro that I can attach to a button that will;

    a) Select a range of values in Active Sheet (eg. A4:A67)

    b) Save to a Txt (.txt) file with a specific name from cell "A1" in active sheet

    c) Prompt the user as to where he/she wants to save this file (desktop, documents folder,...etc)

    d) and Once the txt file is saved, the macro will prompt the user if they want to email to a specific address. if the user select yes then it activates the default email program and attaches the file with the subject header "Gas Log file" and emails to a specific address e.g. "[email protected]"....if the user select no, then it only just saves the txt file.

    Pls help excel gurus/gods. Much appreciate it in advance!!!

  2. #2
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Select range in excel and save as text file & email - Macro

    This works with Outlook. I don't know how to call other email programs from VBA.

    Please Login or Register  to view this content.
    Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.

    <---Click * Add Reputation for all helpful comments. It's like giving a smile.
    Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.

  3. #3
    Registered User
    Join Date
    04-27-2015
    Location
    Houston, Texas
    MS-Off Ver
    2013
    Posts
    49

    Re: Select range in excel and save as text file & email - Macro

    Oh my thanks so much! That was fast.
    Will give it a whirl and post results

  4. #4
    Registered User
    Join Date
    04-27-2015
    Location
    Houston, Texas
    MS-Off Ver
    2013
    Posts
    49

    Re: Select range in excel and save as text file & email - Macro

    The code works in that it saves the txt file and attaches it to outlook.

    However there are 2 issues encountered which I think are easily solved;
    1) When I open the saved txt file it is BLANK with no data in it
    2) Also, for some reason, the range in excel that is copied into the .txt file somehow is changed (which is most odd, it shifts the range....)

    Pls help with this? Not sure why this is

    Thanks

  5. #5
    Registered User
    Join Date
    04-27-2015
    Location
    Houston, Texas
    MS-Off Ver
    2013
    Posts
    49

    Re: Select range in excel and save as text file & email - Macro

    Ok I have figured out (2) above! It was my error
    But the TXT file has none of the data...in other words its an empty file.

    PLEASE HELP Excel god!

  6. #6
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Select range in excel and save as text file & email - Macro

    Are you still having a problem with this? I tested it out again and I get values in the text file. Can you post a copy of your workbook without any sensitive data?

  7. #7
    Registered User
    Join Date
    04-27-2015
    Location
    Houston, Texas
    MS-Off Ver
    2013
    Posts
    49

    Re: Select range in excel and save as text file & email - Macro

    Quote Originally Posted by nigelbloomy View Post
    Are you still having a problem with this? I tested it out again and I get values in the text file. Can you post a copy of your workbook without any sensitive data?
    Thanks so much for your response.
    I am not quite sure how to post a workbook on here

    But basically just as in your code all the values to be copied are in A4 through to A67.
    Now the values in each cell is reading from other values above (e.g. A4= G4). Could this be affecting it?

    The email works great but the txt file is still empty.

    Also can I tried modifiying the Email code to include a particular SEND TO address and a particular SUBJECT LINE but that didn't seem to take for some reason. See below
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-27-2015
    Location
    Houston, Texas
    MS-Off Ver
    2013
    Posts
    49

    Re: Select range in excel and save as text file & email - Macro

    here is an image of the COPY OF THE WORKBOOK with a possible range that the macro will read from
    Attachment 400327
    Last edited by harryco79; 06-10-2015 at 05:33 PM.

  9. #9
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Select range in excel and save as text file & email - Macro

    Try changing this line:

    Please Login or Register  to view this content.
    to this:
    Please Login or Register  to view this content.
    I didn't think about having formulas in that range so this should work by pasting the values instead.

  10. #10
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Select range in excel and save as text file & email - Macro

    The .to should work for hard coding in the address.
    The subject should work as well. The only thing may be that you need to get that value in Range("H152") when the original sheet is still open. You can do something like this near when the stFilename is set to A1:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-27-2015
    Location
    Houston, Texas
    MS-Off Ver
    2013
    Posts
    49

    Re: Select range in excel and save as text file & email - Macro

    Thanks again for the quick response.
    I just modified the macro as you suggested and tried it but my TEXT FILE is still coming up empty...see the attached image excelFORUMimagepost_2.jpg
    you can see that the file has the correct filename as specified by the macro but the txt file is blank

    Could there be another format file that may work best instead? like a .csv maybe?
    Attached Images Attached Images

  12. #12
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Select range in excel and save as text file & email - Macro

    The text file works for me. You can try the csv option.
    Change this line to save it as a csv
    Please Login or Register  to view this content.
    And then this line when attaching the file to make sure it finds the .csv version
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    04-27-2015
    Location
    Houston, Texas
    MS-Off Ver
    2013
    Posts
    49

    Re: Select range in excel and save as text file & email - Macro

    Quote Originally Posted by nigelbloomy View Post
    The .to should work for hard coding in the address.
    The subject should work as well. The only thing may be that you need to get that value in Range("H152") when the original sheet is still open. You can do something like this near when the stFilename is set to A1:

    Please Login or Register  to view this content.
    My Apologies for the repetitive images....my bad
    I modified the email code as suggested but it is till coming up empty in the SEND TO, and SUBJECT LINE part of the email...? By all accounts it should work but not sure why it isnt

    THANKS SO MUCH FOR YOUR PATIENCE WITH ME

  14. #14
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Select range in excel and save as text file & email - Macro

    The sendkeys method mentioned here may work for you if your default mail isn't outlook.

    http://www.databison.com/3-nifty-way...l-using-excel/

  15. #15
    Registered User
    Join Date
    04-27-2015
    Location
    Houston, Texas
    MS-Off Ver
    2013
    Posts
    49

    Re: Select range in excel and save as text file & email - Macro

    I Got the macro to work!!! YAY!
    Your code was spot on. It was user-error (namely myself)
    I really appreciate the help and support nigelbloomy!

    Thanks! Thanks! Thanks!!!

  16. #16
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Select range in excel and save as text file & email - Macro

    Oh Good! I really couldn't figure out another way to do it. I'm glad it's working for you now.

+ 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. Macro Help to select the data and save as a txt file.
    By annyfouanny in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-15-2015, 12:04 AM
  2. [SOLVED] VBA / Macro to Select File and Save As
    By bloomingcarrot in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-17-2015, 01:13 AM
  3. Excel Macro lotus notes email Select range and paste in subject
    By johnny_w in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-31-2013, 07:27 AM
  4. [SOLVED] Save selected range in a pdf format & email the file to a someone automatically?
    By jjin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-13-2013, 12:16 PM

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