Add workbook and automatic dispatch via mail

[Nelson78]

Good morning.

I'm working on this stuff with the help of the macro recorder. But clearly it's not exaustive for specific needs.

I've a workbook named "source".
Starting from a sheet named "last" of this workbook, I've to generate a new workbook with some datas and sending it in csv format to e-mail adresses via Microsoft Outlook.

This is my work with the macro recorder.

Sub sent_mail()

    Workbooks.Add 'open a new workbook'
    Sheets("Sheet2").Select 'delete useless sheets, maybe not necessary'
    ActiveWindow.SelectedSheets.Delete
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
    ChDir "C:\Users\john.smith\Desktop"
    ActiveWorkbook.SaveAs Filename:="C:\Users\john.smith\Desktop\example.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False 'save with name example'
    
    Windows("source.xlsm").Activate
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy 'copy needed datas'
    Windows("example.csv").Activate
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False 'past needed datas on csv new workbook'
    Application.CutCopyMode = False 
    
    Application.Dialogs(xlDialogSendMail).Show 
    
End Sub

Furthermore, I've to name the new workbook using one cell of the "source" workbook. The cell is B2 in a sheet called "test".

The e-mail adresses that have to receive the mail are: "frank@example.com" and "mark@example.com"

Thank you.

 

[Nelson78]

Sure I will.

Just one thing: in my first message I referred to a "geographic problem".

I mean: the sender and the addressee are both located in Italy. Could some local setting cause the problem?

 

[FormR]

I mean: the sender and the addressee are both located in Italy. Could some local setting cause the problem?

It's possible - comparing the content of two files in notepad might help to shed some light. Especially as you've suggested that manually saved files work for them and the code created ones don't.

 

[Nelson78]

I've opened both files with notepad.

Code: the datas are devided by commas ,
Manual: the datas are devided by semicolon ;

 

[FormR]

Code: the datas are devided by commas ,
Manual: the datas are devided by semicolon ;

That'll be the problem then. You might want to have a search around for VBA code that creates a CSV file using a semicolon as the delimiter and use that instead of the Save As method.

 

[Nelson78]

First test positive.

ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlCSV, CreateBackup:=False, [COLOR="#FF0000"][B]Local:=True[/B][/COLOR]

 

[FormR]

Very good - thanks for sharing

 

[Nelson78]

Unfortunately, we've encountered a problem.

One of the columns contains phone number beginning by zero: after the saving in csv format, the zero is not present.

Instead, opening with notepad, the zero is correctly reported in front of the number.

How can I manage this situation?

Thank's.

 

[FormR]

opening with notepad, the zero is correctly reported in front of the number.

Hi, then the zero is there and should be available to the software you are importing into - it's Excel that is dropping it.

 

[Nelson78]

So, the reason about the today's unsuccessfull importation could be something else.

Tomorrow I will try again and report you back.

 

[FormR]

So, the reason about the today's unsuccessfull importation could be something else.

That would be my guess, and it's clearly just a guess. Does your other software not give you any hints as to why a particular import fails?