You could try to use the below code to generate the CSV


Public Sub tryUsingEmptyWorksheet()

'#
'# declare private variables
'#
   Dim pvt_xls_Source As Excel.Worksheet
   Dim pvt_xls_Target As Excel.Worksheet
   
   Dim vaRecipients As Variant
   Dim stSubject As String
   Dim TempFilePath As String
   Dim TempFileName As String
   Dim FileExtStr As String
   
'#
'# initialise
'#
   Set pvt_xls_Source = ActiveSheet
   Set pvt_xls_Target = ThisWorkbook.Worksheets.Add
   
   vaRecipients = VBA.Array("My Client")
   stSubject = "CREST ETI " & Date
   TempFilePath = Environ$("CREST ETI") & "\"
   TempFileName = ActiveWorkbook.Name
   FileExtStr = ".csv"
   
'#
'# copy the data to the newly created / pristine worksheet
'#
   pvt_xls_Source.Range("C5:AE5", Selection.End(xlDown)).Select
   Selection.Copy
   pvt_xls_Target.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   
'#
'# move the focus to the newly created worksheet and save the contents as a CSV file
'#
   pvt_xls_Target.Activate
   ActiveWorkbook.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=xlCSV
        
End Sub