• Outlook User
  • Exchange Admin
  • Office 365
  • Outlook Developer
  • Outlook.com
  • Outlook Mac
  • Outlook & iCloud
    • Common Problems
    • Outlook BCM
    • Utilities & Addins

Use a Macro to Attach Files to New Messages

Slipstick Systems

› Developer › Use a Macro to Attach Files to New Messages

Last reviewed on August 7, 2016     16 Comments

This is a version of the macro at Insert the Newest HTML File. Instead of inserting the file as stationery, it adds the newest file as an attachment to a new message.

By changing the conditions in the IF statement, you can add files based on other conditions.
If fsoFile.DateLastModified > dtNew And Right(fsoFile.Name, 5) = ".docx" Then

The macro uses the Scripting Runtime to read the HTML file. You'll need to set a reference to it in the VB Editor's Tools, References dialog.
Set a reference to scripting runtime

 

Sub AttachNewestFile()
' You need to set a reference to the scripting object
Dim objMail As Outlook.MailItem
Dim fso As Scripting.FileSystemObject
 
 Dim strFile As String
 Dim fsoFile As Scripting.File
 Dim fsoFldr As Scripting.Folder
 Dim dtNew As Date, sNew As String
 
 Set fso = New Scripting.FileSystemObject
 
 strFile = "C:\Users\Administrator\Documents\"
   
 Set fsoFldr = fso.GetFolder(strFile)
     
     For Each fsoFile In fsoFldr.Files
' check the extension and age
         If fsoFile.DateLastModified > dtNew And Right(fsoFile.Name, 5) = ".docx" Then
             sNew = fsoFile.Path
             dtNew = fsoFile.DateLastModified
             Debug.Print sNew & dtNew
         End If
     Next fsoFile
 
 'Create e-mail item
 Set objMail = Application.CreateItem(olMailItem)

 With objMail
 .BodyFormat = olFormatHTML
 .HTMLBody = "Here is the file you asked for"
 .Attachments.Add sNew
 .Display
 End With
End Sub

Attach Two Files

This version of the macro gets the newest document then finds the Excel with the same 4 first letters and inserts it into a new message.

Sub AttachTwoFiles()
' You need to set a reference to the scripting object
Dim objMail As Outlook.MailItem
Dim fso As Scripting.FileSystemObject
Dim fsoFile As Scripting.File
Dim fsoFldr As Scripting.Folder

 Dim strFilePath As String, strDoc As String
 Dim strExcel As String, strName As String
 Dim dtNew As Date, sNew As String
 
 Set fso = New Scripting.FileSystemObject
 
 strFilePath = "C:\Users\Administrator\Documents\"
   
 Set fsoFldr = fso.GetFolder(strFilePath)
     
     For Each fsoFile In fsoFldr.Files
         If fsoFile.DateLastModified > dtNew And Right(fsoFile.Name, 5) = ".docx" Then
             sNew = fsoFile.Path
             dtNew = fsoFile.DateLastModified
             Debug.Print sNew & dtNew
         End If
     Next fsoFile
     
 strDoc = sNew

strName = Right(sNew, Len(sNew) - Len(strFilePath))
strName = Left(strName, 4)

    For Each fsoFile In fsoFldr.Files
        If Left(fsoFile.Name, 4) = strName And Right(fsoFile.Name, 5) = ".xlsx" Then
            sNew = fsoFile.Path
        End If
    Next fsoFile

strExcel = sNew

 'Create e-mail item
 Set objMail = Application.CreateItem(olMailItem)

 With objMail
 .BodyFormat = olFormatHTML
 .HTMLBody = "Here is the file you asked for"
 .Attachments.Add strDoc
 .Attachments.Add strExcel
 .Display
 End With
End Sub

Attach Newest File to Message in Reading Pane

This macro works with Outlook 2013/2016's Reading Pane Compose feature and attaches the newest message to the reply in the Reading Pane.

Sub AttachNewestFileReadingPane()
' You need to set a reference to the scripting object
Dim objMail As Outlook.MailItem
Dim fso As Scripting.FileSystemObject
 
 Dim strFile As String
 Dim fsoFile As Scripting.File
 Dim fsoFldr As Scripting.Folder
 Dim dtNew As Date, sNew As String
 
 Dim exp As Explorer
 
 Set exp = Application.ActiveExplorer
 Set objMail = exp.ActiveInlineResponse
 If Not objMail Is Nothing Then
 
 Set fso = New Scripting.FileSystemObject
 
 strFile = "C:\Users\Diane\Documents\"
   
 Set fsoFldr = fso.GetFolder(strFile)
    
    For Each fsoFile In fsoFldr.Files
' check the age
         If fsoFile.DateCreated > dtNew Then
             sNew = fsoFile.Path
             dtNew = fsoFile.DateCreated
             Debug.Print sNew, dtNew
        End If

 Next fsoFile
 
    With objMail
        .Attachments.Add sNew
    End With
 End If
 
End Sub

Send a File to a Contact

This version of the macro sends a file named for the selected contact.

Send file to contact

If you use a different filename format, for example "MarySmith" or "smith-mary", a simple macro edit will find the correct file:

strContact = objContact.FullName
Replace(strContact , " " , "")

Or use

strContact = lcase(objContact.lastName & "-" & objContact.firstName)

To use this macro, select the contact and run the macro.

Public Sub SendFileToContact()
Dim objApp As Outlook.Application
Dim objContact As ContactItem
 
Dim objMail As Outlook.MailItem
Dim fso As Scripting.FileSystemObject
Dim fsoFile As Scripting.File
Dim fsoFldr As Scripting.Folder

 Dim strFilePath As String, strDoc As String
 Dim strExcel As String, strName As String
 Dim strContact As String, sNew As String
 
Set objApp = Application
Set objContact = objApp.ActiveExplorer.Selection.Item(1)
strContact = objContact.FullName

Set fso = New Scripting.FileSystemObject
 
 strFilePath = "C:\Users\Administrator\Documents\"
   
 Set fsoFldr = fso.GetFolder(strFilePath)
     
     For Each fsoFile In fsoFldr.Files
         If Left(fsoFile.Name, Len(strContact)) = strContact And Right(fsoFile.Name, 5) = ".docx" Then
             sNew = fsoFile.Path
             Debug.Print sNew & dtNew
         End If
     Next fsoFile
     
 strDoc = sNew
 'Create e-mail item
 Set objMail = objApp.CreateItem(olMailItem)

 With objMail
 .To = objContact.Email1Address
 .BodyFormat = olFormatHTML
 .HTMLBody = "Here is the file you asked for"
 .Attachments.Add strDoc
 .Display
 End With
 
 Set objApp = Nothing
End Sub

More Information

Send a new message to From address of selected messages
Send email to all addresses for one Contact
Use an Outlook Macro to Send Files by Email

How to use macros

First: You will need macro security set to low during testing.

To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.

After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.

Open the VBA Editor by pressing Alt+F11 on your keyboard.

To put the code in a module:

  1. Right click on Project1 and choose Insert > Module
  2. Copy and paste the macro into the new module.

More information as well as screenshots are at How to use the VBA Editor

Use a Macro to Attach Files to New Messages was last modified: August 7th, 2016 by Diane Poremsky
  • Twitter
  • Facebook
  • LinkedIn
  • Reddit
  • Print

Related Posts:

  • Use an Outlook Macro to Send Files by Email
  • Create a New Message using an HTML File or Stationery
  • Apply Outlook Stationery to Replies and Forwards
  • Use VBA to open Outlook messages stored in the file system

About Diane Poremsky

A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Subscribe
Notify of
16 Comments
newest
oldest most voted
Inline Feedbacks
View all comments

Serge (@guest_218614)
August 10, 2021 7:58 pm
#218614

Hi Diane,

thanks for your lights ! Is there a way, in reading pane, to open the "insert attachment dialog", opened in the dowloads folder by default, or any other folder I could put in the macro ?

Best regards

0
0
Reply
WARD DE GEEST (@guest_215223)
May 16, 2020 7:35 am
#215223

Hi Diane,
How do I add an existing file to an Outlook message that is already started.
All VBA codes here generates a new outlook email message.
I want to automate (add file, choose file, add), in 1 click. The file is the same for each message.
(Not all outgoing messages should contain this file).
As Outlook has no recorder, I need some help to fix this.
Tnahks, Eduard.

0
0
Reply
Jay (@guest_211955)
September 16, 2018 8:54 pm
#211955

Diane, hello. I'm trying to find some code that would automate sending multiple emails to selected email recipients, plus an attachment specific to each email. The emails contain reports that are required to be sent once a month. The subject line for each email changes according to the attachment (e.g, F4333/K54 TO001 Cost Report (Aug 2018), F4333/K54 TO005 Cost Report (Aug 2018), etc.) When the TO number changes, the recipients change. I'm currently using templates specific to each TO where I drag the specific attachment into each email template, verify it, then send. I'm currently sending around 120 emails a month. Then the next month there may be additions or subtractions to the TO list. Was thinking of having a macro button in Outlook specific to each "K" number, like K54, K877, K987, etc. and the corresponding TO templates under each "K" level macro. Once clicked on, an input box would ask for the directory to pull the attachment from (as the location changes each month) and I can use one of your filters to pull the correct attachment based on the first 12 characters or so. I've looked through many of your examples, the closest is "Open a Template… Read more »

0
0
Reply
Jimbo (@guest_211719)
August 15, 2018 8:09 pm
#211719

Hi Dianne
Is it possible to have a macro to just add a standard attachment which i will point to with a button on the ribbon in a new email?

Even better, asI have probably 5 or 6 of these standard ones...can i add a custom dropdown to the ribbon and add files to it?

0
0
Reply
Chris (@guest_201597)
September 15, 2016 4:47 pm
#201597

Hi Diane,
. Can this macro be modified to automatically open a .oft template and then attach the newest .pdf from a folder on a network drive?

0
0
Reply
Diane Poremsky(@diane-poremsky)
Author
Reply to  Chris
September 15, 2016 8:39 pm
#201604

Should just need to replace
Set objMail = Application.CreateItem(olMailItem)
with
Set objMail = Application.CreateItem("C:\path\template.oft")

getting the attachment works the same as in the macro - you just need to use the network path in the filepath.
strFilePath = "\\computer\network\path\"

0
0
Reply
Rebecca (@guest_217728)
Reply to  Diane Poremsky
March 9, 2021 9:20 pm
#217728

I can't seem to get it to recognise the PDF file type when retreiving the item, it only works for docx, pptx etc.

0
0
Reply
Diane Poremsky(@diane-poremsky)
Author
Reply to  Rebecca
March 9, 2021 9:49 pm
#217729

You need to change the count and the extension:
Right(fsoFile.Name, 5) = ".docx" Then
to
Right(fsoFile.Name, ) = ".pdf" Then

1
0
Reply
Rebecca (@guest_217732)
Reply to  Diane Poremsky
March 10, 2021 12:45 am
#217732

Changed it to a 4 and it seemed to work, thank you!

0
0
Reply
Tracy H (@guest_195723)
December 31, 2015 12:26 pm
#195723

Hi Diane,

I would like to attach 8 files to one email monthly, the file names are static with the only change being the date at the end of the file. All files begin with "NP_" and end in "yyyy-mm-dd.txt" All of the files are stored in the same directory. filename = NP_Dissolution2015-12-01. The "Dissolution" part is what changes. All of the files are also created on the same day of the month. (So the next group will be created on 01-04-2015.

Is there a way to script the email message to automatically grab the 8 files and attach them?

0
0
Reply
Diane Poremsky(@diane-poremsky)
Author
Reply to  Tracy H
December 31, 2015 11:08 pm
#195730

you'd change this -
For Each fsoFile In fsoFldr.Files
If Left(fsoFile.Name, 4) = strName And Right(fsoFile.Name, 5) = ".xlsx" Then
sNew = fsoFile.Path
End If
I think this might work -

For Each fsoFile In fsoFldr.Files
If Right(fsoFile.Name, 14) = format(date, "yyyy-mm-dd") & ".txt" Then
sNew = fsoFile.Path
End If

0
0
Reply
Mitch (@guest_194073)
October 16, 2015 3:39 pm
#194073

I should have included that in the first message. All the items are located in one folder in the C drive.

0
0
Reply
Mitch (@guest_193727)
October 2, 2015 4:21 pm
#193727

Hi Diane,

Weekly I send 3 unique PDFs to around 15 different email recipients (45 total PDFs, 15 emails). Each of the 3 PDFs (going to 1 recipient) begins with a custom name. i.e one recipient gets BOB1, BOB2, BOB3, and another gets SARA1, SARA2, SARA3. I would like to use a macro to search in the C drive for the PDF based on the beginning phrase, grab the files that match it, look up the email address associated with the beginning phrase, and then email them out.

0
0
Reply
Diane Poremsky(@diane-poremsky)
Author
Reply to  Mitch
October 15, 2015 7:55 am
#194051

It would be a lot easier if all of the files are in one folder - VBA would need to check every folder and it would be slow. If it knows to look in a specific folder, it would be much faster. Picking up the files is fairly easy as long as they use a uniform name that it can detect.

0
0
Reply

Visit Slipstick Forums.
What's New at Slipstick.com

Latest EMO: Vol. 28 Issue 11

Support Services

Do you need help setting up Outlook, moving your email to a new computer, migrating or configuring Office 365, or just need some one-on-one assistance?

Subscribe to Exchange Messaging Outlook






Our Sponsors

CompanionLink
ReliefJet
  • Popular
  • Latest
  • WeekMonthAll
  • Adjusting Outlook's Zoom Setting in Email
  • How to Remove the Primary Account from Outlook
  • Cannot add Recipients in To, CC, BCC fields on MacOS
  • Save Sent Items in Shared Mailbox Sent Items folder
  • Move an Outlook Personal Folders .pst File
  • Create rules that apply to an entire domain
  • Outlook's Left Navigation Bar
  • Use PowerShell to get a list of Distribution Group members
  • View Shared Calendar Category Colors
  • Remove a password from an Outlook *.pst File
  • Cannot add Recipients in To, CC, BCC fields on MacOS
  • Change Appointment Reminder Sounds
  • Messages appear duplicated in message list
  • Reset the New Outlook Profile
  • Delete Old Calendar Events using VBA
  • Use PowerShell or VBA to get Outlook folder creation date
  • Outlook's Left Navigation Bar
  • Contact's Display Bug
  • Use PowerShell to get a list of Distribution Group members
  • Edit Outlook’s Attach File list
Ajax spinner

Newest Code Samples

Delete Old Calendar Events using VBA

Use PowerShell or VBA to get Outlook folder creation date

Rename Outlook Attachments

Format Images in Outlook Email

Set Outlook Online or Offline using VBScript or PowerShell

List snoozed reminders and snooze-times

Search your Contacts using PowerShell

Filter mail when you are not the only recipient

Add Contact Information to a Task

Process Mail that was Auto Forwarded by a Rule

Recent Bugs List

Microsoft keeps a running list of issues affecting recently released updates at Fixes or workarounds for recent issues in Outlook for Windows.

Outlook for Mac Recent issues: Fixes or workarounds for recent issues in Outlook for Mac

Office Update History

Update history for supported Office versions is at Update history for Office

Outlook Suggestions and Feedback

Outlook Feedback covers Outlook as an email client, including Outlook Android, iOS, Mac, and Windows clients, as well as the browser extension (PWA) and Outlook on the web.

Use Outlook.com Feedback for suggestions or feedback about Outlook.com accounts.

Other Microsoft 365 applications and services




Windows 10 Issues

  • iCloud, Outlook 2016, and Windows 10
  • Outlook Links Won’t Open In Windows 10
  • Outlook can’t send mail in Windows 10: error Ox800CCC13
  • Missing Outlook data files after upgrading Windows?

Outlook Top Issues

  • The Windows Store Outlook App
  • The Signature or Stationery and Fonts button doesn’t work
  • Outlook’s New Account Setup Wizard
  • Outlook 2016: No BCM
  • Exchange Account Set-up Missing in Outlook 2016

VBA Basics

How to use the VBA Editor

Work with open item or selected item

Working with All Items in a Folder or Selected Items

VBA and non-default Outlook Folders

Backup and save your Outlook VBA macros

Get text using Left, Right, Mid, Len, InStr

Using Arrays in Outlook macros

Use RegEx to extract message text

Paste clipboard contents

Windows Folder Picker

Custom Forms

Designing Microsoft Outlook Forms

Set a custom form as default

Developer Resources

Developer Resources

Developer Tools

Outlook-tips.net Samples

VBOffice.net samples

SlovakTech.com

Outlook MVP David Lee

MSDN Outlook Dev Forum

Repair PST

Convert an OST to PST

Repair damaged PST file

Repair large PST File

Remove password from PST

Merge Two Data Files

Sync & Share Outlook Data

  • Share Calendar & Contacts
  • Synchronize two computers
  • Sync Calendar and Contacts Using Outlook.com
  • Sync Outlook & Android Devices
  • Sync Google Calendar with Outlook
  • Access Folders in Other Users Mailboxes

Contact Tools

Data Entry and Updating

Duplicate Checkers

Phone Number Updates

Contact Management Tools

Diane Poremsky [Outlook MVP]

Make a donation

Calendar Tools

Schedule Management

Calendar Printing Tools

Calendar Reminder Tools

Calendar Dates & Data

Time and Billing Tools

Meeting Productivity Tools

Duplicate Remover Tools

Mail Tools

Sending and Retrieval Tools

Mass Mail Tools

Compose Tools

Duplicate Remover Tools

Mail Tools for Outlook

Online Services

Productivity

Productivity Tools

Automatic Message Processing Tools

Special Function Automatic Processing Tools

Housekeeping and Message Management

Task Tools

Project and Business Management Tools

Choosing the Folder to Save a Sent Message In

Run Rules on messages after reading

Help & Suggestions

Outlook Suggestion Box (UserVoice)

Slipstick Support Services

Home | Outlook User | Exchange Administrator | Office 365 | Outlook.com | Outlook Developer
Outlook for Mac | Common Problems | Utilities & Addins | Tutorials
Outlook & iCloud Issues | Outlook Apps
EMO Archives | About Slipstick | Advertise | Slipstick Forums
Submit New or Updated Outlook and Exchange Server Utilities

Send comments using our Feedback page
Copyright © 2023 Slipstick Systems. All rights reserved.
Slipstick Systems is not affiliated with Microsoft Corporation.

wpDiscuz

Sign up for Exchange Messaging Outlook

Our weekly Outlook & Exchange newsletter (bi-weekly during the summer)






Please note: If you subscribed to Exchange Messaging Outlook before August 2019, please re-subscribe.

Never see this message again.

You are going to send email to

Move Comment