Email a CSV file

[Helmut]

Hi - I'm creating a CSV file with 'Fname'
then I want to email it. The macro below creates the csv file BUT I can't
retain the 'Fname' for the file for emailing. it's something to do with the
DIM
Helmut PLEASE
------------------
Public Sub DoTheExport()
Dim Fname As Variant
Dim wb As Workbook

Fname = Application.GetSaveAsFilename("c:\MESSER\MESSERmmyy",
fileFilter:="CSV Files (*.csv), *.csv")

If Fname = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

'Running the Public Sub below
ExportToTextFile CStr(Fname), ",", False 'this creates the CSV file with
"Fname"

Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SendMail "(e-mail address removed)", _
"This is the Subject line"
.Close False
End With
Kill Fname
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Here is example to send the activesheet as csv
http://www.rondebruin.nl/mail/folder1/mail2.htm

See Example 3

 

[Helmut]

Ron,
Unfortunately, if I run just that macro, my Column "D" which is formatted
dd/mm/yyyy gets transformed to mm/dd/yyyy in the CSV file. I don't know why.
If I run the "ExportToTextFile CStr(Fname), ",", False " first to create the
CSV file, then the format is retained.
So what I tried below in my macro is to first create the CSV file and then
mail it.
If you could possibly take a quick look at my macro and fix it, it would be
appreciated.
Helmut

 

[Ron de Bruin]

Hi

ExportToTextFile CStr(Fname), ",", False 'this creates the CSV file with

Is the file is saved on your computer or is it open after this code line

 

[Helmut]

Hi Ron,
thanks, I just figured out the problem. to answer...yes, the
'ExportToText...' is part of the macros on the workbook.
the problem is solved this way...here is my fixed macro:
--------------------------------
' Dim wb As Workbook
' Dim Fname As String
' Fname = Application.GetSaveAsFilename("c:\MESSER\MESSERmmyy",
fileFilter:="CSV Files (*.csv), *.csv")
' Application.ScreenUpdating = False
' ActiveSheet.Copy
' Set wb = ActiveWorkbook
' With wb
' .SaveAs Fname, FileFormat:=xlCSV
' .SendMail "(e-mail address removed)", _
' "Eliahu, this is the ToCAV file for ________"
' .Close False
' End With
' Application.ScreenUpdating = True
-------------------------------------------------
But the problem was the formatted date column.
I had it formatted as:

FORMAT: DATE: *dd/mm/yyyy |Locale: Hebrew

changing it to:
FORMAT: DATE: dd/mm/yyyy |Locale: Hebrew

notice the * on the date format. It had the effect that it changed the
format in the CSV file from dd/mm/yyyy to mm/dd/yyyy

If I ran the 'ExportToText...' ...somehow it created the csv file correctly.
but now I don't have to run it anymore.
thanks for your time and effort.
Helmut