Sending Email attachments from excel via VBA

Question

I've written a macro that at the click of a button it sends an automated email via outlook. Everything runs smoothly except I just can't figure out how to attach a file to the email. Everywhere I've looked, example code for attaching files to an email is for static named files, as in, you're sending the same file name, with the same path every time.

If it makes it more convenient, the button that runs this macro is inside the workbook that I'm trying to attach. I'm not sure if opening a windows explorer window is easiest and attaching the file that way would be best.

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.recipient
Dim objOutlookAttach As Outlook.Attachment
Dim WeekendingDate As Date

With Worksheets("Macro Buttons")
    WeekendingDate = Range("N2").Value
End With

Set objOutlook = CreateObject("Outlook.Application")

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
    Set objOutlookRecip = .Recipients.Add("blah@blah")
    objOutlookRecip.Type = olTo
   .Subject = "Blah " & WeekendingDate
   .Body = "blah blah blah"

'Add attachments to the message [some code]

   For Each objOutlookRecip In .Recipients
       objOutlookRecip.Resolve
   Next
   If DisplayMsg Then
       .Display
   Else
       .Save
   End If
End With
Set objOutlook = Nothing
End Sub

Answer 1

You need the Attachments.Add code inserted into the MailItem setup:

With objOutlookMsg
    Set objOutlookRecip = .Recipients.Add("blah@blah")
    objOutlookRecip.Type = olTo
   .Subject = "Blah " & WeekendingDate
   .Body = "blah blah blah"
'Add attachments to the message [some code]
   .Attachments.Add "pathToFile"
   For Each objOutlookRecip In .Recipients
       objOutlookRecip.Resolve
   Next
   If DisplayMsg Then
       .Display
   Else
       .Save
   End If
End With
Set objOutlook = Nothing

In one of my own scripts I pass a collection of attachments to the MailItem to be attached using a Dictionary object and the following code:

With oMailItem
        Set .SendUsingAccount = oOutlook.Session.Accounts.Item(iAccount)
        .To = EmailData("To")
        .CC = EmailData("CC")
        .BCC = EmailData("BCC")
        .Subject = EmailData("Subject")
        .Body = EmailData("Body")
        sAttachArray = Split(EmailData("AttachmentPaths"), ";")
        For Each sAttachment In sAttachArray
            .Attachments.Add(sAttachment)
        Next
        .Recipients.ResolveAll
        .Display    ' debug mode - uncomment this to see email before it's sent out
    End With