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

**harryco79** · 05-27-2015, 01:08 PM

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. "gaslog@gmail.com"....if the user select no, then it only just saves the txt file.

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

**nigelbloomy** · 05-27-2015, 04:36 PM

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

Sub sendEmail()
Dim stFileName As String
Dim stFileLocation As String
Dim iQuestion As Integer

stFileName = Range("A1").Value
Range("A4:A67").Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
stFileLocation = Application.GetSaveAsFilename(InitialFileName:=stFileName)
ActiveWorkbook.SaveAs Filename:=stFileLocation & "txt", FileFormat:=xlText
ActiveWorkbook.Close SaveChanges:=False

iQuestion = MsgBox("Would you like to email the file?", vbYesNo)

If iQuestion = vbYes Then
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .Attachments.Add stFileLocation & "txt"
        .Display
    End With
Else: End If

End Sub

**harryco79** · 05-27-2015, 04:44 PM

Oh my thanks so much! That was fast.

Will give it a whirl and post results

**harryco79** · 06-03-2015, 11:43 AM

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

**harryco79** · 06-05-2015, 12:40 PM

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!

**nigelbloomy** · 06-10-2015, 05:06 PM

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?

**harryco79** · 06-10-2015, 05:17 PM

Originally Posted by nigelbloomy

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

 On Error Resume Next
    With OutMail
        .To = "name@name.com"
        .CC = ""
        .BCC = ""
        .Subject = "BUNKER EVALUATION" & Range("H152").Value
        .Body = "See attached"
        .Attachments.Add stFileLocation & "txt"
        .Display
    End With
Else: End If

**harryco79** · 06-10-2015, 05:26 PM

here is an image of the COPY OF THE WORKBOOK with a possible range that the macro will read from
Attachment 400327

**nigelbloomy** · 06-10-2015, 05:33 PM

Try changing this line:

Range("A1").PasteSpecial xlPasteAll

to this:

Range("A1").PasteSpecial xlPasteValues

I didn't think about having formulas in that range so this should work by pasting the values instead.

**harryco79** · 06-10-2015, 05:45 PM

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?

**nigelbloomy** · 06-10-2015, 05:45 PM

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:

Dim stSubject as String
stSubject = Range("H152").value

**harryco79** · 06-10-2015, 05:54 PM

Originally Posted by nigelbloomy

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:

Dim stSubject as String
stSubject = Range("H152").value

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

**nigelbloomy** · 06-10-2015, 05:53 PM

The text file works for me. You can try the csv option.
Change this line to save it as a csv

ActiveWorkbook.SaveAs Filename:=stFileLocation & "csv", FileFormat:=xlCSV

And then this line when attaching the file to make sure it finds the .csv version

.Attachments.Add stFileLocation & "csv"

**nigelbloomy** · 06-10-2015, 06:03 PM

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/

**harryco79** · 06-12-2015, 12:09 PM

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!!!

**nigelbloomy** · 06-12-2015, 12:21 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Macro Help to select the data and save as a txt file.

[SOLVED] VBA / Macro to Select File and Save As

Excel Macro lotus notes email Select range and paste in subject

[SOLVED] Save selected range in a pdf format & email the file to a someone automatically?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions