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.
Kr,
Pecis
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