Outlook VBA --> Dynamic Attachment

[laxcat73]

Hey,

I am writing code to add an attachment that has yesterday's date on the file name, but can't find how I can do this. This file goes out every morning to a distribution list, with yesterday's financial performance.

Here is my current code - everything works wonderfully except the attachment line:

Sub SaveAsDraft()
    Dim objOutlook As Object
    Dim objMailMessage As Outlook.MailItem
    Dim emlBody, sendTo As String
    Dim wkbook As String
    Dim currday As String
    Dim currmonth As String
    Dim ThisDay As Date, PrevDay As Date, ThisDay2 As Date
    
    ThisDay = Format(Now, "mm/dd/yy")
    PrevDay = ThisDay - 1
    ThisDay2 = Format(Now, "mmddyy")
    Set objOutlook = CreateObject("Outlook.Application")
    Set objMailMessage = objOutlook.CreateItem(0)
    
    'Date stuff
    vardate = Format(PrevDay, "mm/dd")
    currday = Format(PrevDay, "dddd")
    
    'Current month
    currmonth = "July"

    sendTo = list1
    sendCC = list2
    emlBody = " "
    With objMailMessage
        .To = sendTo
        .CC = sendCC
        .Attachments.Add ("x:\xxxxxxxxxx\xxxxxxxxxx\xxxxxxxxxx\" & currmonth & "\Daily Sales " & currmonth & " 2012 by Channel_" & ThisDay2 & ".pdf")
        .Body = emlBody
        .Subject = currmonth & " Daily Sales and Merchandise Margin updated through " & currday & ", " & vardate
        .Display
    End With
End Sub

Any guidance on how to address the attachment?

 

[wigi]

Maybe:

[LEFT][COLOR=#333333].Attachments.Add "x:\xxxxxxxxxx\xxxxxxxxxx\xxxxxxxxxx\" & [/COLOR][COLOR=#333333]Format(PrevDay, "mmmm")[/COLOR][COLOR=#333333] & "\Daily Sales " & [/COLOR][COLOR=#333333]Format(PrevDay, "mmmm")[/COLOR][COLOR=#333333] & " 2012 by Channel_" & ThisDay2 & ".pdf"[/COLOR][/LEFT]

If not, please give more explanation on the path and the filename: a concrete example is needed here.

 

[laxcat73]

Maybe:

[LEFT][COLOR=#333333].Attachments.Add "x:\xxxxxxxxxx\xxxxxxxxxx\xxxxxxxxxx\" & [/COLOR][COLOR=#333333]Format(PrevDay, "mmmm")[/COLOR][COLOR=#333333] & "\Daily Sales " & [/COLOR][COLOR=#333333]Format(PrevDay, "mmmm")[/COLOR][COLOR=#333333] & " 2012 by Channel_" & ThisDay2 & ".pdf"[/COLOR][/LEFT]

If not, please give more explanation on the path and the filename: a concrete example is needed here.

My apologies. Example for Today would be: G:\Financial Planning\2012 Daily Sales\Production\July\Daily Sales July 2012 by Channel_071912.pdf

 

[wigi]

Try:

.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"

 

[laxcat73]

Brilliant! Thank you!!!