Adding a List of Attachments to Emails as They’re Sent


This morning I ran across the following tweet from hillarief.

As I explained to Hillarie when I replied, there’s no simple way to do this as an item is attached, but it’s pretty easy to do it as the message is sent. Of course this requires a macro. All the macro does is check each outbound message for attachments. If it finds any, then it builds a list of attachment names and appends it to the beginning of the message. The macro ignores hidden attachments (e.g. embedded images). This solution should work for Outlook 2007 and later. Hillarie is using Outlook 2010, so this should satisfy her requirement.

Instructions.

  1. Open Outlook.
  2. Press ALT + F11 to open the Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  4. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  5. Click the diskette icon on the toolbar to save the changes
  6. Close the VB Editor
  7. Click File and select Options
  8. When the Outlook Options dialog appears click Trust Center then click the Trust Center Settings button
  9. Click Macro Settings
  10. Select either of the two bottom settings (i.e. Notifications for all macros or Enable all macros (not recommended; potentially dangerous code can run). The choice of which to choose is up to you. If you select Notifications, then you’ll be prompted at times to enable macros. If you pick Enable all then there’s a chance that a malicious macro could run. It’s a question of how much risk you’re willing to assume.
  11. Click Ok until the dialog-boxes have all closed
  12. Close Outlook
  13. Start Outlook. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run. Say yes.
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Dim olkAtt As Outlook.Attachment, strLst As String
    If Item.Class = olMail Then
        For Each olkAtt In Item.Attachments
            If Not IsHiddenAttachment(olkAtt) Then
                strLst = strLst & olkAtt.DisplayName & vbCrLf
            End If
        Next
        If Len(strLst) > 0 Then
            Select Case Item.BodyFormat
                Case olFormatHTML
                    Item.HTMLBody = "Attached:<br><br>" & Replace(strLst, vbCrLf, "<br>") & "<br><br>" & Item.HTMLBody
                Case Else
                    Item.Body = "Attached:" & vbCrLf & vbCrLf & strLst & vbCrLf & vbCrLf & Item.Body
            End Select
            Item.Save
        End If
    End If
End Sub

Public Function IsHiddenAttachment(olkAtt As Outlook.Attachment) As Boolean
    ' Purpose: Determines if an attachment is a hidden attachment.
    ' Written: 7/12/2012
    ' Author:  David Lee
    ' Outlook: 2007 and later
    Const PR_ATTACH_CONTENT_ID = "http://schemas.microsoft.com/mapi/proptag/0x3712001E"
    Dim olkPA As Outlook.PropertyAccessor, varTemp As Variant
    On Error Resume Next
    Set olkPA = olkAtt.PropertyAccessor
    varTemp = olkPA.GetProperty(PR_ATTACH_CONTENT_ID)
    IsHiddenAttachment = (varTemp <> "")
    On Error GoTo 0
    Set olkPA = Nothing
End Function
Advertisements

39 comments on “Adding a List of Attachments to Emails as They’re Sent

  1. Hi David

    Hopefully you still read this.

    Your Macro is exactly what I have been looking for. Thank you very much for all the work put in so far.

    Without much knowledge of macro I was wondering if you could combine Jason’s request for the attachment list at the bottom of the email with Dave’s request to modify the text properties.

    • Hi, Andrew.

      This should do it.

      Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
          Dim olkAtt As Outlook.Attachment, strLst As String
          If Item.Class = olMail Then
              For Each olkAtt In Item.Attachments
                  If Not IsHiddenAttachment(olkAtt) Then
                      strLst = strLst & olkAtt.DisplayName & vbCrLf
                  End If
              Next
              If Len(strLst) > 0 Then
                  Select Case Item.BodyFormat
                      Case olFormatHTML
                          Item.HTMLBody =  Item.HTMLBody & "<br><br><span style=""font-family:calibri; font-size:11pt;"">Attached:<br><br>" & Replace(strLst, vbCrLf, "<br>") & "</span>"
                      Case Else
                          Item.Body = "Attached:" & vbCrLf & vbCrLf & strLst & vbCrLf & vbCrLf & Item.Body
                  End Select
                  Item.Save
              End If
          End If
      End Sub
      
  2. Hi David, this is a wonderful code, I have been looking for a while.
    It is not working for me, and I’ll tell you what I did:

    On the trust center I enabled all macros.
    I went into Developer, open a project on this Outlook session.
    Copied and past your first script, and later on added the the script to verify that the macros are running.
    In the first case, when testing the script I do not get an error, but I do not get an ok.
    In the second case I get confirmation that the macros are active.
    Before testing the macro I placed a link in order to apply the macro, but the list of attachments do not show in the sent nor the received message.
    Outlook 2010, WIN 7.
    Please let me know at your earliest convenience.
    Thx

    • Hi, Andres.

      Thanks, I’m glad you like the code. Now, let’s see if we can get it operational for you.

      I’m not clear on what you mean when you say “I placed a link in order to apply the macro”. Please clarify.

      Ok, so macros are enabled and the AreMacrosEnabled macro says that macros are enabled, but nothing is happening when you send a message with an attachment. That leaves a couple of possibilities: the code isn’t running, the code is running but isn’t inserting the list of attachments. Let’s start by checking to see if the code is running. Please add this line of code

      Msgbox "The code fired"
      

      immediately after this line of code

      If Item.Class = olMail Then
      

      Once you’ve added that line, please send a test message with an attachment. Let me know what happens.

    • Hello David,
      The solution is awesome.
      It’s very useful and easy to implement at the outlook client level.
      But Please tell me at the server level.

      Since the outlook rules at the server level had very limited actions and also ‘Start application’ action is not available for Rules Wizard , how to make this VBA application be run at the server/transport rules level.

      We are using MS office Professional Plus 2010.

      Please help me.

      Thanks and Regards,
      Sri

    • Hi, Sri.

      It’s not possible to run VBA at the server in the way you mean. Exchange doesn’t have that capability. In order to do this at the server you’d have to write an “event sink”. Writing event sinks are best left to professional developers since a poorly written or buggy event sink could bring the entire mail system down.

  3. Hi David,

    Awesome code, thank you. I have just upgraded a customer to Outlook 2013 and the macro stopped working (no errors or warnings, and I have changed the macro security settings as advised). Any ideas why this might be? It was working great under Outlook 2010.

    Thanks in advance.

    James.

    • Hi James.

      Thanks! I’m glad you like it.

      Since there aren’t any errors, I’m guessing that macros aren’t working even though they’re enabled. Try this. Add this code to that you already have. Now, run this macro. If Outlook prompts you to enable macros, click the affirmative answer.

      Sub AreMacrosEnabled()
          Msgbox "Macros are enabled.", vbInformation + vbOkOnly, "Are Macros Enabled?"
      End Sub
      
    • Hi David,

      The macro test worked – the popup appeared with no warnings. After that, the original code started working!

      Not sure why it happened that way, but it’s all good now, thanks 🙂

      James.

    • Hi, James.

      Outlook sometimes doesn’t recognize that macros are enabled until you actually run a macro. All that macro did was force Outlook into action so it could see that macros are enabled.

  4. Hello David;

    This is a great tool, unfortunately my VBA skills went out the door as I left the door at University all those years ago.

    I am however very keen to roll this out to my company and if you have a spare few moments it would be great if;

    1. Font of the list could be set (Say calibri 11pt) to keep with company standards
    2. Current Date added, ie “Attachment: dd mmm yyyy”, if the email goes around in circles there might be numerous attachments lists at the bottom of each email.

    Best Regards

    Dave

    • Hi, Dave.

      Thanks. I’m glad you like it/find it useful. To set the font face and size change this line

      Item.HTMLBody = "Attached:<br><br>" & Replace(strLst, vbCrLf, "<br>") & "<br><br>" & Item.HTMLBody
      

      to

      Item.HTMLBody = "<span style=""font-family:calibri; font-size:11pt;"">Attached:<br><br>" & Replace(strLst, vbCrLf, "<br>") & "</span><br><br>" & Item.HTMLBody
      

      As to the current date, I’m not clear on whether you want it once at the beginning of the list or next to each file name. Please let me know which you prefer.

    • Thanks I have the font change working;

      For the date what I had in mind was at the beginning of the list for example if the email has been suffixed a couple of time

      Attached: 29 Jul 2014

      Document attached 2.docx
      Spreadsheet xyz.xlsx

      Attached: 31 Jul 2014

      Another document attached to email.txt
      Quick drawing.dwg

    • You’re welcome, Dave.

      I can do the date like that but want to make sure you understand that the list of attachments will be a simple list of all the attachments on the email, not those added on that date. In other words, if “spreadsheet xyz.xlsx” was on the original message and is still attached later, it will be listed again.

    • Dave,

      Ok. Modify the this line from

      Item.HTMLBody = "<span style=""font-family:calibri; font-size:11pt;"">Attached:<br><br>" & Replace(strLst, vbCrLf, "<br>") & "</span><br><br>" & Item.HTMLBody
      

      to

      Item.HTMLBody = "<span style=""font-family:calibri; font-size:11pt;"">Attached: " & Format(Date,"dd mmmm yyyy") & "<br><br>" & Replace(strLst, vbCrLf, "<br>") & "</span><br><br>" & Item.HTMLBody
      
  5. Hi, I get a syntax error when sending the message, and the first line of the code is highlighted yellow. Can you please help?
    Thank you!

    • Hi, Andreea.

      Something happened that messed the code up on the site. I’ve corrected it. Please download the code again.

    • Thank you so much, it works great!
      I also copied the change below, in order to have the list included at the end of the message. If it’s not too much trouble, is there any way that the list be included at the end of the message body but before the signature line?
      Thanks again,
      Andreea.

    • Hi, Andreea.

      You’re welcome. That’s difficult to do. Th problem is that Outlook inserts the signature automatically and unfortunately there isn’t anything to mark where the signature begins. If you’re signature begins with something unique, then I can try to key on that. If not, then I don’t know of any way to accomplish this.

    • Ok, I understand. Does it make a difference that my signature is a table and the first words in row 1 are my full name?

    • I don’t think that will work. The problem is that there’s so much mark-up in an HTML formatted message. I can’t just look for a TABLE tag, because the body of the message might also contain a table. Nor can I safely look for your name. Your name might appear in the body of the message. Even if it doesn’t, your name is going to be in a table cell. There’s no surefire way of backtracking from there to the beginning of the table. The only good solution I can think of for inserting the list before your signature, is to modify your signature by adding something that’s guaranteed to be unique just before the table containing your actual signature. For example,

      %ATTACHLIST%
      Your actual signature goes here

      The code could then replace %ATTACHLIST%, a string that’s guaranteed to appear only here, with the list of attachments. If there weren’t any attachments, then the code would simple delete that string, ensuring that the message recipient never sees it. How do you fell about that solution?

    • One more thing: this tweak is really necessary for the situations where I am replying to another message, since otherwise the list of attachments is included at the very end of the thread.

    • Hi, Andreea.

      My deepest apology for taking so long to reply. I’d completely forgotten that I still owed you an answer.

      Here’s the revised version of Application_ItemSend. You still need the IsHiddenAttachment function from the original post. This version expects the literal value “%ATTACHLIST%” to appear just before your signature. The code will replace that string with the actual list of attachments, if there are any, or remove it from the message if there aren’t any attachments.

      Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
          Dim olkAtt As Outlook.Attachment, strLst As String
          If Item.Class = olMail Then
              For Each olkAtt In Item.Attachments
                  If Not IsHiddenAttachment(olkAtt) Then
                      strLst = strLst & olkAtt.DisplayName & vbCrLf
                  End If
              Next
              If Len(strLst) > 0 Then
                  Select Case Item.BodyFormat
                      Case olFormatHTML
                          strLst = "Attached:<br><br>" & Replace(strLst, vbCrLf, "<br>") & "<br><br>"
                          Item.HTMLBody = Replace(Item.HTMLBody, "%ATTACHLIST%", strLst)
                      Case Else
                          strLst = "Attached:" & vbCrLf & vbCrLf & strLst & vbCrLf & vbCrLf
                          Item.Body = Replace(Item.Body, "%ATTACHLIST%", strLst)
                  End Select
              Else
                  Select Case Item.BodyFormat
                      Case olFormatHTML
                          Item.HTMLBody = Replace(Item.HTMLBody, "%ATTACHLIST%", strLst)
                      Case Else
                          Item.Body = Replace(Item.Body, "%ATTACHLIST%", strLst)
                  End Select            
              End If
              Item.Save
          End If
      End Sub
      
    • No apologies needed, just many thanks to you! The script does exactly what I was looking for.
      Have a nice day!

    • Hi, I’m coming back on this neat script because in Outlook 2013 it somehow erases the logo in my signature (but only when there is an attachment, otherwise it lets it there). Any idea how I can correct that?

      Thank you!

    • Hi, Andreea.

      What format are your sending your messages in and where did you put “%ATTACHLIST%” at in your message?

    • My emails are sent HTML by default. My signature includes a logo in .rgb format, while %ATTACHLIST% is one line before the signature itself. This is how it was also in Outlook 2010 and it worked fine – my signature hasn’t changed at all, just the Outlook version.
      I don’t get an error or any placeholder or anything, the email is simply sent with an empty space instead of the logo.

    • Andreea,

      In Outlook 2013 I created a signature with a graphic on the first line then sent a message with attachments. The code inserted the list of attachments and the graphic is still there. There’s no reason why the code should delete the graphic. There must be something about the position of the replaceable text (i.e. %ATTACHLIST%) in your messages, the name or placement of the graphic you’re using, etc. I’d need to look at your signature and how you have the replaceable text positioned in order to figure out what’s going on.

    • Jason,

      Replace the Application_ItemSend event from the code in my post with the version below. This does what you described.

      Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
          Dim olkAtt As Outlook.Attachment, strLst As String
          If Item.Class = olMail Then
              For Each olkAtt In Item.Attachments
                  If Not IsHiddenAttachment(olkAtt) Then
                      strLst = strLst & olkAtt.DisplayName & vbCrLf
                  End If
              Next
              If Len(strLst) > 0 Then
                  Select Case Item.BodyFormat
                      Case olFormatHTML
                          Item.HTMLBody = Item.SendUsingAccount & " Attached:<br><br>" & Replace(strLst, vbCrLf, "<br>") & "<br><br>" & Item.HTMLBody
                      Case Else
                          Item.Body = Item.SendUsingAccount & " Attached:" & vbCrLf & vbCrLf & strLst & vbCrLf & vbCrLf & Item.Body
                  End Select
                  Item.Save
              End If
          End If
      End Sub
      
    • Hi, Jason.

      Sure, that’s doable. to put the list of attachments at the bottom of the message change this

      Select Case Item.BodyFormat
          Case olFormatHTML
              Item.HTMLBody = "Attached:<br><br>" & Replace(strLst, vbCrLf, "<br>") & "<br><br>" & Item.HTMLBod
          Case Else
              Item.Body = "Attached:" & vbCrLf & vbCrLf & strLst & vbCrLf & vbCrLf & Item.Body
      End Select
      

      to this

      Select Case Item.BodyFormat
          Case olFormatHTML
              Item.HTMLBody = Item.HTMLBody & "<br><br> Attached:<br><br>" & Replace(strLst, vbCrLf, "<br>")
          Case Else
              Item.Body = Item.Body & vbCrLf & vbCrLf & "Attached:" & vbCrLf & vbCrLf & strLst
      End Select
      

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s