Macro to automate attaching two files to a new email

Kevin Buckland
Kevin Buckland used Ask the Experts™
I always have to attach two files to multiple emails I send each day.  I'd like a macro that I can run after I draft a new email which attaches two files:

  • Attach the file: J:\Portfolio\_Reporting & Operations\REPO\RepoTkt.xlsm
  • Attach the newest PDF saved to U:\

The U:\ drive is our scan folder on our network.  I scan the image from the copier and immediately draft my email -- so the scan I need to attach is always the most recent. The filename is something like DOC_20170329101015.PDF  (format is "DOC_" followed by numeric string consisting of the date and time in the format of YYYYMMDDHHMMSS.pdf

Thank you!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

pls try
Sub InsertAttachments()
    Const strPath As String = "U:\" '// Change as required
    Const strFileType As String = "DOC*.pdf"
    Dim strFile As String
    Dim myAttachments As Outlook.Attachments

    Dim NewMail As MailItem, oInspector As Inspector
    Set oInspector = Application.ActiveInspector
    If oInspector Is Nothing Then
        MsgBox "No active inspector"
        Set NewMail = oInspector.CurrentItem
        If NewMail.Sent Then
            MsgBox "This is not an editable email"
            Set myAttachments = NewMail.Attachments
            myAttachments.Add "J:\Portfolio\_Reporting & Operations\REPO\RepoTkt.xlsm"
            strFile = ""
            On Error Resume Next
            strFile = Split(CreateObject("WScript.Shell").Exec("CMD /C DIR """ & strPath & strFileType & """ /O:-D /B /A:-D").StdOut.ReadAll, vbCrLf)(0)
            On Error GoTo 0
            If strFile <> "" Then myAttachments.Add strPath & strFile
        End If
    End If
End Sub

Open in new window

Kevin BucklandInvestment Officer


Works wonderfully! Thank you!!
Kevin BucklandInvestment Officer


I posted a follow up that is for a specific file (and doesn't attach the most recent scan):

Kevin BucklandInvestment Officer


I also posted another follow up email to get a clean macro that only attaches the latest scan and not the other specific file:


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial