VBA to convert three tabs to csv and add to email attachment

remejiah

New Member
Joined
Jul 13, 2016
Messages
18
This is a big ask, but could anyone please help me find a macro to accomplish the following task?

I have a workbook that has 3 tabs with information to be uploaded in a 3rd party software. They will need to be uploaded into csv format and need to be save for records. I would like to use email so there is a record and copy of the attachment saved on our server. I need to be able to through pressing a button, export each of those three tabs into separate csv documents and then attach them all to an email that is sent to an email address contained in a specific cell.

Is this possible and if so, how.

Regards,

Jeremiah Dawson
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Try this, changing the lines where indicated.
Code:
Public Sub Email_Sheets_As_CSV()

    Dim csvFiles(1 To 3) As String, i As Integer
    Dim wsName As Variant
    Dim OutApp As Object, OutMail As Object
    
    i = 0
    For Each wsName In Array("Sheet1", "Sheet2", "Sheet3")     'sheet names to be emailed - CHANGE THE NAMES
        i = i + 1
        csvFiles(i) = ThisWorkbook.Path & "\" & wsName & ".csv"
        ThisWorkbook.Worksheets(wsName).Copy
        ActiveWorkbook.SaveAs csvFiles(i), FileFormat:=xlCSV
        ActiveWorkbook.Close False
    Next

    'Email the .csv files
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = ThisWorkbook.Worksheets("Settings").Range("A1").Value     'cell containing email address - CHANGE THE SHEET & CELL
        .CC = ""
        .BCC = ""
        .Subject = "Email subject here"
        .Body = "This email contains 3 .csv file attachments."
        .Attachments.Add csvFiles(1)
        .Attachments.Add csvFiles(2)
        .Attachments.Add csvFiles(3)
        .Send
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    'Delete the .csv files
    
    Kill csvFiles(1)
    Kill csvFiles(2)
    Kill csvFiles(3)
    
End Sub
 
Upvote 0
@john_w Thanks so much! If this works it will be a total lifesaver! I will work on adding it to the sheet later today! Thanks again for your help!
 
Upvote 0
It worked!!!! Thanks so much! One more thing if you are able to help. I would like to be able to Insert the Client Name into the file name and in the subject line of the email (which would be referenced on a cell). How would I adjust your awesome code to accomplish that?
 
Upvote 0
Try this. As before, change the lines where indicated.
Code:
Public Sub Email_Sheets_As_CSV()

    Dim csvFiles(1 To 3) As String, i As Integer
    Dim wsName As Variant
    Dim OutApp As Object, OutMail As Object
    Dim clientName As String
    
    clientName = ThisWorkbook.Worksheets("Settings").Range("A2").Value      'cell containing client name - CHANGE THE SHEET NAME AND CELL
    
    i = 0
    For Each wsName In Array("Sheet1", "Sheet2", "Sheet3")     'sheet names to be emailed - CHANGE THE SHEET NAMES
        i = i + 1
        csvFiles(i) = ThisWorkbook.Path & "\" & clientName & " " & wsName & ".csv"
        ThisWorkbook.Worksheets(wsName).Copy
        ActiveWorkbook.SaveAs csvFiles(i), FileFormat:=xlCSV
        ActiveWorkbook.Close False
    Next

    'Email the .csv files
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = ThisWorkbook.Worksheets("Settings").Range("A1").Value     'cell containing email address - CHANGE THE SHEET NAME AND CELL
        .CC = ""
        .BCC = ""
        .Subject = "Email subject here " & clientName
        .Body = "This email contains 3 .csv file attachments."
        .Attachments.Add csvFiles(1)
        .Attachments.Add csvFiles(2)
        .Attachments.Add csvFiles(3)
        .Send
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    'Delete the .csv files
    
    Kill csvFiles(1)
    Kill csvFiles(2)
    Kill csvFiles(3)
    
End Sub
 
Last edited:
Upvote 0
John W,

I presented the completed workbook today in the office and it was a big success. Thanks so much for your help. Everything worked as expected.
 
Upvote 0
Help! A windows or Office update seems to have gone through and the code no longer works. I get an error message that says "Run-time error '-2147467259 (80004005)': Outlook does not recognize one or more names." Do you know of any adjustment to your code that will not return this error when running the macro?
 
Upvote 0
Hi Guys, I have just found this code which i think i can use but would like to make a few changes. Is it possible to send each CSV to individual email addresses? So sheet1 to dave@yahoo.co.uk, sheet2 to bob@yahoo.com etc etc

Many thanks
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top