Sending Excel file, that contains the code, as an attachment

Question

I've written Excel VBA code that sends an email via Outlook.

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.

The button that runs this macro is inside the workbook that I'm trying to attach.

Sub mySub
    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

Solution

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