email as attachment

[JOEE1979]

I would like to send the active workbook as an attachment.
I'm not sure if I missed any posts.
Everywhere I've seen this I'm required to save the excel file to some drive.
I'm not sure if there is a way to not have to save the file (I will end up with so many files I do not need).
Is there a code someone has that will send out the email in outlook and not have to save the file OR possibly to a temp drive and then delete the file after?

Thanks

 

[FGaxha]

Try This: make sure you change email address:


Option Explicit

Public FormulaCell As Range

Sub Mail_with_outlook1()

Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strto = "fgaxha@xxxxx.com"
strcc = ""
strbcc = ""
strsub = ""
strbody = "Hi " & vbNewLine & vbNewLine & _
"Please see Attached File. " & Sheet1.Range("E1") & _
vbNewLine & vbNewLine & "Please let me know if you need anything"

With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = "Please see Attached File"
.Body = strbody
'You can add a file to the mail like this
.Attachments.Add (ThisWorkbook.FullName)
.send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[DanteAmor]

Try this:

Sub sendthiswokbook()
  With CreateObject("Outlook.Application").CreateItem(0)
    .to = "email@domain.com"
    .Subject = "My Subject"
    .Body = "My body"
    .Attachments.Add ThisWorkbook.FullName
    .Send
  End With
End Sub

 

[Pinaceous]

Hi DanteAmor,

How would you attach the exact title of ThisWorkbook because when I use ThisWorkbook.FullName it interrupts the original title? (FYI, I'm using SharePoint.)

For example, when I run the code upon the workbook that I'm using it's titled: "FY23 TOTALS STUFF .xlsm" but when I run the code it attaches it as : "FY23%20TOTALS%20STUFF%20.xlsm".

Can you please help?

Thank you!
pinaceous