Excel attachment to outlook

[pecisc]

Hi guys,

most likely a simple one, but I could not come up with effective solution for the following.

I have a macro that among other things attaches workbook to the e-mail and names the file with today's date and value from cell E3. Afterwards the workbook is closed.
I need the macro to do exactly as described but without closing the workbook or saving it on the PC and user can generate the next e-mail without reopening the template. I tried to "comment out" certain lines (in bold), but then either i get a saved copy of the file in my documents or I do not get the attached workbook named correctly (with the most current value in cell E3).

Summary of the desired result:
1) User fills in the excel template and runs the macro to create an e-mail.
2) The attached workbook name includes the latest value of cell E3
3) Workbook remains open.
4) Nothing is saved on the computer unless the user manually decides to save a copy.

Sub EmailWorkbook1()

    Application.ScreenUpdating = False

    Dim OutApp As Object, OutMail As Object, LWorkbook As Workbook, LFileName As String, fName As String, fAccount As String
    Set LWorkbook = ActiveWorkbook
    fName = Range("E3").Value
    fAccount = Range("D2").Value
    LFileName = Format(Now(), "yyyymmdd") & "_" & fName
    
    On Error Resume Next
    Kill LFileName
    On Error GoTo 0
    
    LWorkbook.SaveAs Filename:=LFileName, FileFormat:=52
    
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    ' Open a new email, put in some basic information and attach the Excel file
    With OutMail
        .To = "abc@123.com"
        '.CC = ""
        '.BCC = ""
        .Subject = Format(Now(), "yyyymmdd") & "_" & fName & "_" & fAccount
        '.Body = ""
        .Attachments.Add LWorkbook.FullName
        .Display
    End With

    'Delete and close the workbook
    LWorkbook.ChangeFileAccess Mode:=xlReadOnly
    Kill LWorkbook.FullName
    LWorkbook.Close SaveChanges:=False
        
    Application.ScreenUpdating = True
    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

Kr,

Pecis

 

[RoryA]

IMO, it would be much easier to use LWorkbook.SaveCopyAs to save a copy of the workbook (without affecting the open one), send it, then delete the copy.