Macro to send Tab of Excel As a CSV via email

calof1

Try below code. This module is programmed to use MS Outlook for the email and is set to activate and run only for sheet named Upload CSV. I don't have Outlook so was not able to fully test the code, however, the .csv file is created / saved successfully. I also changed the send function to 'display' for testing purposes but you can switch this back once you're happy it works as anticipated. Hope this is works for you:

Public Sub Email_Sheets_As_CSV()

Dim csvFile As String
Dim wsName As Variant
Dim OutApp As Object, OutMail As Object

Sheets("Upload CSV").Activate

wsName = ActiveSheet.Name
csvFile = ThisWorkbook.Path & "\" & wsName & ".csv"
ThisWorkbook.ActiveSheet.Copy
ActiveWorkbook.SaveAs csvFile, FileFormat:=xlCSV
ActiveWorkbook.Close False

'Email the .csv files

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = ThisWorkbook.Worksheets("Upload CSV").Range("B5").Value 'cell containing email address - CHANGE THE SHEET & CELL
.CC = ""
.BCC = ""
.Subject = "Email subject here"
.Body = "This email contains 1 .csv file attachments."
.Attachments.Add csvFile
.Display 'change to .Send to send email without displaying first
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

'Delete the .csv files

Kill csvFile

End Sub