Excel attachment to outlook

pecisc

New Member
Joined
Mar 6, 2019
Messages
6
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,850
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,237
Messages
5,576,899
Members
412,753
Latest member
Coach_Olson
Top