Add Attachment File Names To Body Of Message

  • I have a level of experience in Excel VBA, but have never written an Outlook macro -- and hoped to never need to. But now I do.


    We frequently send emails to customers with proposals attached, which are a collection of various docs -- a Word document, some spreadsheets, etc. Those docs are all maintained in a CM system (we use Perforce.) What I would like to do is this: When documents are attached, retrieve the path and filename for each. Then query Perforce to get the unique changelist number associated the version of each document (Perforce has a COM interface, I can do that), and list the attachments and their changelist numbers at the bottom of the email; e.g,


    Attachments:
    Proposal - Widget.doc 22517
    Price - Widget.xls 22538
    Compliance Matrix - Widget SOW.xls 22501
    Ts&Cs.pdf 20349


    Then I can delete the documents from the sent mail (attachments redundant), and know what versions of what docs were sent in each email.


    In the Send event, I can get filenames but not paths (unless they're linked docs, but they're not), and I need the complete path to query Perforce. There's some kind of an Attachment event, but no file properties available.


    Any help appreciated. Will trade for exotic Excel formulas or VBA ...

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Add Attachment File Names To Body Of Message


    Have a read of this site outlook code


    I think you need to capture the AttachmentAdd event of a mail item. Haven't tested this so not sure whether Path is stored. The attachment object has the property but thats not to say it contains anything useful.

  • Re: Add Attachment File Names To Body Of Message


    Thanks, Andy, I tried the AttachmentAdd Event. It occurs after the attachment takes place, so Attachment.Pathname="" unless the attachment is a link to the file rather than the file itself.


    The BeforeAttachmentAdd event (new to Outlook 2007) looked promising, but it's not an option in the events in the drop-down in the ThisOutlookSession module, so I put it in a class module:

    Code
    1. Public WithEvents newItem As Outlook.MailItem
    2. Private Sub newItem_BeforeAttachmentAdd(Attachment As Attachment, _
    3. Cancel As Boolean)
    4. MsgBox Attachment.PathName & "\" & Attachment.FileName
    5. End Sub


    It doesn't catch the event.


    Any further suggestions appreciated.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]