Macro to create an e-mail using an Outlook Template and add an attachment

Hello Guys!

As this is my first thread here I'd like to say that this Forum has been extremely useful for me since I joined. My boss insists on using MS Excel for some databases and I've been fighting hard in trying to automate all procedures we do as much as we can.

Recently I have deloped a code that takes a line of data from our database, transfer it to a nice looking Excel form for the revision of a group, saves this new form created at a backup location and then starts a PDF printer.

Now, I want to create an additional "Sub" that could generate an e-mail based on an Outlook e-mail template (*.oft), attaching the PDF file which has been generated by the previous macro. The PDF name is sequential and automatically generated by the previous macro as well (0001, 0002, 0003 and so on).

Anyone, any idea on how to solve it?

Thanks!

Hi hsarro,

I have been using such macro for some time now. It works really cool with office 2010 don't know how it will run on 2007. One more thing it is attaching only one specific file with the given path,

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItemFromTemplate("here should be the path to your template.oft")
myItem.SentOnBehalfOfName = "I use this because I need to send it on behalf of somebody"
myItem.Display
Set myAttachments = myItem.Attachments
myAttachments.Add "path to your attachment"

This will give you some hints and maybe some other guys can advise on how to add the automatically generated pdfs,

Cheers

Hi,

Is it possible to add this code in below ... i have tried it but template will not appear !! Please help me

HTML Code: 

Sub yoga()
Set myOlApp = CreateObject("Outlook.Application")
Set myitem = myOlApp.CreateItemFromTemplate("c:\users\yogananda\desktop\yoga.oft")
myitem.SentOnBehalfOfName = "I use this because I need to send it on behalf of somebody"
myitem.Display
Set myAttachments = myitem.Attachments
myAttachments.Add "c:\users\yogananda\desktop\yoga.oft"
End Sub