Macro to automate attaching two files to a new email

Kevin Buckland
Kevin Buckland used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Top Expert 2016
Commented:
Hi,

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"
    Else
        Set NewMail = oInspector.CurrentItem
        If NewMail.Sent Then
            MsgBox "This is not an editable email"
        Else
            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

Regards
Kevin BucklandInvestment Officer

Author

Commented:
Works wonderfully! Thank you!!
Kevin BucklandInvestment Officer

Author

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

https://www.experts-exchange.com/questions/29012843/Macro-to-automate-attaching-specific-file-to-a-new-email.html

Thanks
Kevin BucklandInvestment Officer

Author

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

https://www.experts-exchange.com/questions/29012846/Macro-to-automate-attaching-the-newest-scan-to-a-new-email.html

Thanks!

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