Attach Current Workbook to Current Email

Attach Current Workbook to Current Email

I email workbooks all the time. Sometimes I send them unprompted in brand-new emails, in which case Excel’s “Send as Attachment” command works great. More often though, I attach them to a reply, in which case it doesn’t.

In addition, there are other traits of “Send as Attachment” which can be irksome.

  • It locks the workbook until you close the email. Invariably I see something I want to change and then stab pointlessly at the workbook until I notice Outlook blinking.
  • It doesn’t prompt you to save the workbook if you’ve made changes.
  • it doesn’t let you know if Outlook’s not open.

To remedy these issues I had to (yay!) write some code. Here it is:

Sub Attach_Current_Wb_To_Current_Email()

'This requires a reference to Microsoft Outlook #.# Object Library

Dim outApp As Outlook.Application
Dim OutMail As Outlook.MailItem

If ActiveWorkbook Is Nothing Then
  MsgBox ("No active workbook.")
  GoTo Exit_Point
End If
If ActiveWorkbook.Path = vbNullString Then
  MsgBox ("This workbook has never been saved.")
  GoTo Exit_Point
End If
If ActiveWorkbook.Saved = False Then
  If MsgBox(prompt:="Changes have been made since last save." & vbCrLf & _
      "Continue?", Buttons:=vbOKCancel + vbQuestion) = vbCancel Then
    GoTo Exit_Point
  End If
End If
On Error Resume Next
Set outApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If outApp Is Nothing Then
  If MsgBox(prompt:="Outlook isn't open." & vbCrLf & "Open and create a new email?", _
      Buttons:=vbOKCancel + vbQuestion) = vbOK Then
    Set outApp = CreateObject("Outlook.Application")
    Set OutMail = outApp.CreateItem(olMailItem)
    GoTo Exit_Point
  End If
End If
With outApp
  If .ActiveInspector Is Nothing Then
    MsgBox "There is no open item"
    GoTo Exit_Point
  End If
  If Not TypeOf .ActiveInspector.CurrentItem Is MailItem Then
    MsgBox "Type of current item isn't email"
    GoTo Exit_Point
  End If
  Set OutMail = .ActiveInspector.CurrentItem
  If OutMail.Sent Then
    MsgBox "Current email was already sent."
    GoTo Exit_Point
  End If
  OutMail.Attachments.Add ActiveWorkbook.FullName
End With

Set outApp = Nothing
End Sub

One thing it doesn’t do that Excel’s built-in command does is send a never-saved workbook, e.g., “Book1.” In addition:

  • If you haven’t saved all your changes it prompts you to continue or cancel.
  • If Outlook isn’t open it prompts you to open it and create a new email, or cancel.
  • If there is no open item then it exits.  Ditto if the open item isn’t an email or if the email isn’t a draft.

When Outlook is opened from the code I get the little icon and message below, same as when I use Activesync.  Outlook seems to work the same as ever though.
Outlook warning

UPDATE: JP at JP Software Technologies posted a follow-up to this.

7 thoughts on “Attach Current Workbook to Current Email

  1. Your way is more elegant than mine but I can’t write Outlook code so I was happy with mine. With the e-mail and office file open, I save the office file (Excel, Word, Power Point, etc). I then press Save As. The folder where the file is saved is open. I sort by date to get the last saved file to the top. I drag and drop the file into the e-mail. I send the e-mail.

    • Dave, I’ve used Save As (or F12) to see where I am, and deleted files along the way to saving, but never made the connection that I could also drag files from there just like any other instance of Windows Explorer. Very nice.

  2. Doug, This is a good piece of code to have in one’s code library, however a better functionality to have was a Range selection on the Worksheet to be added at a particular cursor point in the email body.
    I tried modifiying the OutMail.body to capture the Selection, but the rest of the email content disappears. Any comments?

    • I would try having a word or phrase in the body like “replace this text” and then replace it with the contents of the range. It’s difficult if the body is html because of all the formatting.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>