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]

Perhaps I've to explane the concepts in a more concise way.

1) Add a new workbook;
2) copy and paste datas from a original workbook to the new one;
3) name the new workbook using e value situate in a cell of the original workbook;
4) send via e-mail to specific e-mail adresses.

 

[FormR]

Hi, here is some code you can test:

Sub Sent_mail()

Dim OMail As Object
Dim FName As String

FName = "C:\Users\john.smith\Desktop\" & Range("B2").Value & ".csv"

ActiveSheet.Copy

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close False
Application.DisplayAlerts = True

Set OMail = CreateObject("Outlook.Application").CreateItem(0)

With OMail
    .to = "frank@example.com;john@example.com"
    .Subject = "Subject"
    .Body = "Body"
    .Attachments.Add FName
    .Display 'Change to .Send to send immediately
End With

End Sub

 

[Nelson78]

I've examined your code and adapted it to my real work.

It's all ok except for an aspect, maybe a merely local problem (in a geographic meaning).

When the addressee opens the sent file, the datas are not divided by columns as the original, but divided by commas (,,,).

How can I dispatch them with datas in columns?


Thank you.

 

[FormR]

How can I dispatch them with datas in columns?

Hi, why don't you just send the file to them in XLSX format rather than CSV format?

 

[Nelson78]

Because the addressee should not open the file, but just upload it in a software.

As the upload has not been successful, we've opened it in order to identify the problem.

 

[FormR]

As the upload has not been successful, we've opened it in order to identify the problem.

CSV files are comma separated values, i.e. they do not have columns. Excel (normally) automatically parses them when they are opened.

If the file is not uploading correctly to your external software then it's unlikely to be related to how your addressee's Excel treats/parses the file.

 

[Nelson78]

Doing manually the operation of saving, the file is saved as wished (datas divided by columns).
That is what I read with the macro recorder.

ChDir "C:\Users\john.smith\Desktop"
    ActiveWorkbook.SaveAs Filename:="C:\Users\john.smith\Desktop\example.csv", _
        FileFormat:=xlCSV, CreateBackup:=False

Obviously, if the best solution doesn't exist, we'll proceed with the second best (sending the file in xlsx format).

 

[FormR]

Doing manually the operation of saving, the file is saved as wished (datas divided by columns).

Try opening that manually saved file with notepad.

 

[FormR]

Try opening that manually saved file with notepad.

And also try opening the file created by the code in notepad - comparing these might help you spot if there are any differences.