Creating email attachment out of excel - so close yet so far...

Hi,

I have the following sub which creates an email in Lotus Notes using the output of my query in excel.

Sub Lotus()

        vaRecipients = VBA.Array("My Client")
        stSubject = "CREST ETI " & Date

        ActiveSheet.Copy

        ActiveSheet.Shapes("CommandButton1").Select
        Selection.Delete
        ActiveSheet.Shapes("CommandButton2").Select
        Selection.Delete
        Rows("1:4").Select
        Selection.Delete Shift:=xlToUp
        Columns("A:B").Select
        Selection.Delete Shift:=xlToLeft
        Range("A1:AC1", Selection.End(xlDown)).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

        TempFilePath = Environ$("CREST ETI") & "\"
        TempFileName = ActiveWorkbook.Name
        FileExtStr = ".csv": FileFormatNum = 6
        
        ActiveWorkbook.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        
        Set noSession = CreateObject("Notes.NotesSession")
        Set noDatabase = noSession.GETDATABASE("", "")
          
        If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
        
        Set noDocument = noDatabase.CreateDocument
        Set noAttachment = noDocument.CreateRichTextItem(ActiveWorkbook.FullName)
        Set noEmbedObject = noAttachment.EmbedObject(1454, "", ActiveWorkbook.FullName)
        
        With noDocument
              .Form = "Memo"
              .SendTo = vaRecipients
              .CopyTo = vaCopyTo
              .Subject = stSubject
              .PostedDate = Now()
        End With
        
        Set Workspace = CreateObject("Notes.NotesUIWorkspace")
        Call Workspace.EditDocument(True, noDocument)
           
        Dim UIdoc As Object
        Set UIdoc = Workspace.CURRENTDOCUMENT
        Call UIdoc.GotoField("Body")
        Call UIdoc.InsertText("Hello," & vbCrLf & vbCrLf & "Please find attached the CREST ETI file for" & Date & ". Thank you." & vbCrLf & vbCrLf)
    
        ActiveWorkbook.Close SaveChanges:=False
        Kill TempFilePath & TempFileName & FileExtStr
    
        MsgBox "You can now open Lotus and edit the mail"
        
End Sub

It works, the output is a .csv file with the range of rows that I have in excel except that it also includes the remaining 65000 empty lines as a string of commas (,,,,,,,,,,,,,,,,,,) underneath. I dont understand why since in the selection.copy I really only get the rows filled by my query.

Anyone have an idea how to create a temp .csv file with only the rows that I select in the selection.copy part of the code? Any help will be greatly appreciated!

Hi OllieB,

{Ctrl}{Shift}{End} select A1:AE65536.

Unfortunately, the user for this sheet is too lazy to agree with your alternate solution

Hi OllieB, once again many thanks for your help. I cannot seem to incorporate your code into my initial sub. In the meantime I have convinced my client to just copy and paste into a different sheet and save as .csv :D