I created a macro to open an email template, I want to add additional code so that it automatically attaches a specific file, can this be done?

Question

I am fairly new to the macro scene but I was able to create a macro to open an email template, I want to add additional code so that it automatically attaches a specific file, can this be done?

I know that you can nest macros and I believe that's what I will need to do, I am just not having any luck in finding any examples and I am not good enough yet to know how to layout it all out.

Any help will be greatly appreciated!

Answer 1

I copied the following from the Office VBA reference topic, which I know will attach the file.

Sub AddAttachment()

Dim myItem As Outlook.MailItem

Dim myAttachments As Outlook.Attachments

Set myItem = Application.CreateItem(olMailItem)

Set myAttachments = myItem.Attachments

myAttachments.Add "D:\Documents\Q496.xlsx", _

olByValue, 1, "4th Quarter 1996 Results Chart"

myItem.Display

End Sub

But do I have to input this as a nested command or can I it just run off the Open template macro that I created? If someone could show me an example of a nested macro that would be very helpful as well.

Answer 2

If you already have a macro that creates an email from a template then you can simply add the attachment logic as part of that same macro. The only reasons you might leave it as a separate macro are: 1) you need to create an email without the attachment, and 2) the attachment logic is needed by other macros. If you don't need 2 macros though then don't do it as it adds work and makes maintaining them harder.

Note: I haven't verified your macro code actually works, I'm just giving you a sample of how to do it.

Sub CreateEmailWithAttachment ()

   ' Your existing macro code that creates the email from template goes here...

   ' You should already have the email created so this logic is not needed
   'Dim myItem As Outlook.MailItem
   'Set myItem = Application.CreateItem(olMailItem)

   ' New code to attach files...
   Dim myAttachments As Outlook.Attachments
   Set myAttachments = myItem.Attachments

   myAttachments.Add "D:\Documents\Q496.xlsx", _
      olByValue, 1, "4th Quarter 1996 Results Chart"

   myItem.Display  

End Sub

Answer 3

Michael Taylor, thank-you for your response! The code that I have is listed below and prior to adding the attachment bit worked great in opening my email template.

'Open AP Report 03182024 email template.

Set temp = Application.CreateItemFromTemplate( _

"C:\Users\jcornia\AppData\Roaming\" _

& "Microsoft\Templates\AP Report 03182024.oft")

temp.Display

Set temp = Nothing

Dim myAttachments As Outlook.Attachments

Set myAttachments = myItem.Attachments

olByValue , 1, "AP Report"

myAttachments.Add "G:\Jeannie\AP Report.xlsm"



    

myItem.Display

After adding the my Attachments bit I was getting a runtime error 424 object required error, when I added the olByValue I am getting an Invalid us of property compile error. I'm sure it's how I have it written, your insight is greatly appreciated.

Answer 4

ive got the same issue, my code looks like that before trying to add attachements, and it works.

I tried to add the above, but it also comes up with error 424 object required error.

Sub Welcome()

Dim obApp As Application

Dim NewMail As Outlook.MailItem

Set obApp = Outlook.Application

Set NewMail = obApp.CreateItemFromTemplate("C:\Users\annajackson\AppData\Roaming\Microsoft\Templates\Welcome.oft")

NewMail.Display

Set obApp = Nothing

Set NewMail = Nothing

End Sub