Send a sheet from a workbook as a CSV file as an email attachment

[Godot60]

Hi,

I have a workbook containing multiple sheets. I have figured out a way to send the entire workbook as an email attachment in Outlook. it works great.

What I need to do now is send just one sheet named ICIMS from the workbook as a CSV file attachment. Any ideas?

Here's what I have so far:

Sub Mail_small_Text_Outlook2()


If Range("c157").Value = "" Or Range("c159").Value = "" Then
MsgBox "Please complete all required fields"
Exit Sub
End If




Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Title: " & Sheet1.Range("A4") & vbNewLine & _
"Department: " & Sheet1.Range("C4") & vbNewLine & _
"SBU: " & Sheet1.Range("B5") & vbNewLine & _
"Level: " & Sheet1.Range("B6") & vbNewLine & _
"Manager: " & Sheet1.Range("B145")


On Error Resume Next
With OutMail
.To = "name.lastname@institution.edu"
.cc = ""
.BCC = ""
.Subject = "Successful Submission: " & Sheet1.Range("a4")
.Body = strbody
.Attachments.Add ActiveWorkbook.FullName
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[John_w]

Incorporate this to save as CSV:

    Dim CSVfileName As String
    
    CSVfileName = ActiveWorkbook.Path & "\ICIMS.csv"
    
    ActiveWorkbook.Worksheets("ICIMS").Copy
    ActiveWorkbook.SaveAs CSVfileName, FileFormat:=xlCSV
    ActiveWorkbook.Close False

and

.Attachments.Add CSVfileName

 

[Godot60]

Thanks John! Where do I plug this code in?

 

[John_w]

You want to attach the .csv file to the email, so logically the 'save as CSV' code should be put before the email is created - before the Set OutApp line. And my Attachments.Add line replaces your Attachments.Add line, so it attaches the .csv file instead of the workbook.

 

[Godot60]

Thanks so much John!!!

 

[Ivy_1011]

You want to attach the .csv file to the email, so logically the 'save as CSV' code should be put before the email is created - before the Set OutApp line. And my Attachments.Add line replaces your Attachments.Add line, so it attaches the .csv file instead of the workbook.

Hi John,

How would you only convert a range to be copy to .csv file. I have:

ActiveWorkbook.Worksheets("Order Report").Range("A1:D25").Copy

 

[Ivy_1011]

Hi John,

How would you only convert a range to be copy to .csv file. I have:

ActiveWorkbook.Worksheets("Order Report").Range("A1:D25").Copy

This code is not working.

 

[John_w]

How would you only convert a range to be copy to .csv file. I have:

ActiveWorkbook.Worksheets("Order Report").Range("A1:D25").Copy

Try changing this code to suit your sheet name and range:

Simple code to create .csv file

I've been looking through the board here and have found a couple of lengthy possible solutions to this problem, but none seems to be exactly what I'm looking for. All I want to do is have a macro that will, for example, create a csv file on my desktop from Range("A1:B10") in the worksheet named...

www.mrexcel.com

If it doesn't work please start your own thread because your request to save a range as a .csv file is different to the OP's request to save a sheet as a .csv file.