CODEWITHSUNDEEP
vbaoutlook
MrsAdmin
MrsAdmin

Reputation: 548

Outlook 2010: Macro: Add attachments with dynamic file name

Program: Outlook 2010
OS: Win8
VBA Skill: Novice

Requirement
I have a template.oft for sales reports which I call through this Macro.
The macro attaches a file with a static & secondly, a dynamic name.
I want to attach the dynamic file using another variable of some sort.

'Working File
    Sub zzzAccs()
        Dim newItem As Outlook.mailItem
        Dim dateFormat As String
            dateFormat = Format(Now, "YYYYMMDD")

        Set newItem = CreateItemFromTemplate(":\location\zzz accs.oft")

            newItem.Attachments.Add ":\location\" & "zzz sales_" & Format(Now, "YYYYMMDD") & ".pdf"

        'Attachment 2 - always will have the same name, general notice/reminder
            newItem.Attachments.Add ":\location\zzz Notice.pdf"

            newItem.Display
    End Sub

What I want To call a file with a wildcard.

It will always have:
":\location\zzz Acc (wildcard, namely date within the last month).pdf"

This way it will always pick the Account file, but the date or dynamic wildcard will either be dated in the current month, or something different.

eg: ":\location\zzz Acc 20140201.pdf" (current month, but not a defined 'date from now') ":\location\zzz Acc statement Feb 2014.pdf" (will change based on what the file is for).

Note
I have since tried the following, however it will only attach 1 file, not all files with wildcards: 

'source:    http://stackoverflow.com/a/13729215/2337102
    Dim strPath As String
    Dim strFilter As String
    Dim strFile As String

    strPath = "E:\My Documents\"      'Edit to your path
    strName = "test_"  'added in file core name as I didn't want all the .pdf attached
    strFilter = "*.pdf"
    strFile = Dir(strPath & strName & strFilter)

'New email message from Template
    Set newItem = CreateItemFromTemplate("E:location\test.oft")

'File Locations
    newItem.Attachments.Add "E:\My Documents\" & "test - " & Format(Now, "YYYYMMDD") & ".pdf"
    newItem.Attachments.Add "E:\My Documents\test.pdf"  

    newItem.Attachments.Add (strPath & strFile)
    'the above line only attached 1 file, not 3 that were named according to the str Rules eg:  
    'test_2014; test_20140131; test_agreement

Please advise.

Upvotes: 0

Views: 7421

Answers (1)

MrsAdmin
MrsAdmin

Reputation: 548

I have found the answer to my question.

    Sub AccswithOFT()
        'source:    http://bit.ly/1jzoTy7  (slipstick hyperlink templates)
        'source:    http://bit.ly/1dlG0Qr  (mrexcel dynamic-attachment)
            '.Attachments.Add "G:\Financial Planning\" & Format(PrevDay, "yyyy") & " Daily Sales\Production\" & Format(PrevDay, "mmmm") & "\Daily Sales " & Format(PrevDay, "mmmm yyyy") & " by Channel_" & Format(PrevDay, "mmddyy") & ".pdf"
        'source:    http://bit.ly/1magjbd  (stackoverflow strLocation)
        'source:    http://bit.ly/1g9fxG7  (html body text)
        'source:    http://bit.ly/1kEdRb0  (How to add signature, with my solution)
        'source:    http://bit.ly/1nDrKnd  (excelforum HTML with "")
        'source:    http://bit.ly/1fqqYpd  (mrexcel HTML with ' ')

    Dim newItem As Outlook.MailItem
    Dim dateFormat As String
        dateFormat = Format(Now, "YYYYMMDD")
    Dim sig As String  

    'New email message from Template
    Set newItem = CreateItemFromTemplate("D:\yourlocation\accstest.oft")

    Dim strPath As String
    Dim strFilter As String
    Dim strFile As String 'might need to use as variant
    Dim strName As String

    'source:  http://bit.ly/1jqUmPS   (Based off Unavergae Guy post)
    strPath = "D:\My Documents\"      'Edit to your full path
    strName = "test_"  'added as I don't want all .pdf to attach
    strFilter = "*.pdf"
    strFile = Dir(strPath & strName & strFilter)

        'source:    http://bit.ly/1b923u9  (outlookforums loop for files)
    While (strFile <> "")
        If InStr(strFile, "test") > 0 Then
            'MsgBox "found " & strFile   'I don't use the MsgBox
        newItem.Attachments.Add (strPath & strFile)
        End If
    strFile = Dir
    Wend

        newItem.Display
    End Sub

Upvotes: 1

Related Questions