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

[remejiah]

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

 

[John_w]

Try this, changing the lines where indicated.

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

 

[remejiah]

@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!

 

[remejiah]

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?

 

[John_w]

Try this. As before, change the lines where indicated.

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

 

[remejiah]

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.

 

[remejiah]

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?

 

[thedeadzeds]

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