Using MS Excel 2013 VBA how can I have it send specific as possible files in an E-Mail

I have a MS Excel 2013 macro which will send an e-mail in Outlook 2013.  I will attach the code below.  My question is how can I set it up to E-Mail certain files in a directory or certain file names which will always change from week to week in a directory.  How do people usually do this?  I have been to the site.

I know it has to do with this line below:           '.Attachments.Add ("C:\test.txt")

Here is my Excel VBA:

sub macro1()

'Working in Excel 2000-2013
    'This example send the last saved version of the Activeworkbook
    'For Tips see:
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = "lynn@<some email>.net"    
        .CC = ""
        .BCC = ""
        .Subject = "<some company> Sales Report"
        .body = "Hi," & _
                vbNewLine & _
                vbNewLine & _
                "Frank, did you get this e-mail?" & _
                vbNewLine & _
                "Attached is your sales report." & _
                vbNewLine & _
                "Thank you and have a great day!" & _
                vbNewLine & _
                vbNewLine & _
                "Lynn <LastName>" & _
                vbNewLine & _
                "<Business Name>" & _
                vbNewLine & _
                "Sales Analyst" & _
                vbNewLine & _
                "<Phone> Office" & _
                vbNewLine & _

        .Attachments.Add ActiveWorkbook.FullName
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
end sub
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


certain files in a directory or certain file names which will always change from week to week in a directory

You wil have to give us a way to determine how they are named

ldhonAuthor Commented:
Thank you for the fast response.  Sorry I guess the directory name would always be the same.  It would be like P:\<Company Folder>\Databases - Reports\<CompanyClientNameHere> for the directory name.  The file(s) name would be like <CompanyName> Sales Week Ending 102315.xls.  The company name would always be the same every time.  and the directories would have the same name every time.  Thanks in advance.
I suppose you are sending the Monday after  the date mentioned

then try
        .Attachments.Add ("P:\<Company Folder>\Databases - Reports\<CompanyClientNameHere>\ <CompanyName> Sales Week Ending " & _
            format(Date()+1-Application.Weekday(Date())-1, "mmddyyyy")& ".xls"

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Saurabh Singh TeotiaCommented:
If you can tell me the logic of what will be the file name in every week i can help you writing the code for the same..

To give you an example where you need to change is you need to change your this line to attach file..

     .Attachments.Add ActiveWorkbook.FullName

Open in new window

to like let say i want to attach today's date file saved in format on the same path..

.Attachments.Add Thisworkbook.path & "\"  &  format(now(),"") & ".xlsx"

Open in new window

ldhonAuthor Commented:
Thank you all very much.  I just haven't had the time to see what works.  Although I am sure they all work.  Because you people are the EXPERTS.  Thank you very very much.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.