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

remejiah

New Member
Joined
Jul 13, 2016
Messages
17
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,999
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
 

remejiah

New Member
Joined
Jul 13, 2016
Messages
17
@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

New Member
Joined
Jul 13, 2016
Messages
17
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,999
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:

remejiah

New Member
Joined
Jul 13, 2016
Messages
17
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

New Member
Joined
Jul 13, 2016
Messages
17
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

Active Member
Joined
Aug 16, 2011
Messages
300
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
 

Forum statistics

Threads
1,082,305
Messages
5,364,401
Members
400,800
Latest member
germanpbv

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top