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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,339
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.
 

Forum statistics

Threads
1,176,152
Messages
5,901,644
Members
434,909
Latest member
subratgupta

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top