Attaching files based on datestamp in filename using VBA RRS feed

  • Question

  • Greetings:

    I'm not a programmer, but I have come across a need that I have been trying to fulfill through VBA in outlook.  I have googled the heck out of this and found a lot of useful information, but nothing that seems to help with this one issue.

    Everyday there is a new log file created.  For example, ex120917.log.   I want my code to basically look at today's date ie 120918 and then look for a file dated yesterday and append ex to the beginning so that the attached file is ex120917.log.  I was able to piece together with the help of google and the programmers kind enough to post their code.  This will attach the file, but as you can see, it is a static file.  I don't know programming that well, but I have tried to use a format(date(), yymmdd) to name the file but it doesn't work.  Someone else told me that I can't use format in VBA.  So my question is how can I get it to attach yesterdays log file, without manually opening the code and putting a static file name in the .Attachments.Add portion?  Any help would be greatly appreciated. 

    Here is the code I am currently using:

    Option Explicit
    Sub mail()
    Dim olapp As Object
    Dim olns As Object
    Dim olfolder As Object
    Dim olitem As Object
    Dim olattach As Object
    Dim objOutlookMsg As Object
    Dim objOutlook As Object

    Set olapp = CreateObject("Outlook.Application")
    Set olns = olapp.GetNamespace("MAPI")
    Set olfolder = olns.GetDefaultFolder(6)
    Set olitem = olapp.CreateItem(0)
    Set olattach = olitem.Attachments

    With olitem
       .To = ""
       '.Cc = ""
       .Subject = "Event Log " & Date
       .Body = "ACCSRV (Stop Errors)" & Chr$(13) & Chr$(13) & "DPISRV (Stop Errors)" & Chr$(13) & Chr$(13) _

       .Attachments.Add "\\Insite\DRIVE-C\WINDOWS\system32\LogFiles\SMTPSVC1\ex120917.log", _
       olByValue, 1, "Insite Log"

       '.Send 'Let´s go!
    End With
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing

    End Sub

    Thank you,


    Tuesday, September 18, 2012 4:11 PM


All replies