Opening Outlook Template in Excel VBA

[tonycm1]

Alright, I've been researching this for the last few hours without much luck. Essentially, I've written some VBA for excel that does all the work I need but I would then like it to open an outlook template (OTF) file so that I can e-mail these attachments.

I have this code already in an outlook macro but it doesn't work when I copy/paste it in excel... the code is the following:

'Sub Open_Login_Logout()
'
'Set myItem = Application.CreateItemFromTemplate _
' ("M:\Reporting Templates\Metrics Shortcuts\Login Logout.oft")
' myItem.Display
'
' Set myAttachments = myItem.Attachments
' myAttachments.Add "M:\Reporting Templates\Custom Reports\Tony\HCLL02FEB08.xls", _
' olByValue, 1
' myAttachments.Add "M:\Reporting Templates\Custom Reports\Tony\NSDLL02FEB08.xls", _
' olByValue, 2
' myAttachments.Add "M:\Reporting Templates\Custom Reports\Tony\OSSDLL02FEB08.xls", _
' olByValue, 3
'
'End Sub

I've also found this site http://www.codeforexcelandoutlook.com/olAutomation.html that I thought would have solved my dileama with the following code:

Sub CreateMailInExcel()
Dim olApp As Outlook.Application
Dim Msg As Outlook.MailItem

Set olApp = New Outlook.Application

Set Msg = olApp.CreateItem(olMailItem)

Msg.Display

Set Msg = Nothing
Set olApp = Nothing
End Sub

BUT.... the code stops working when it fails to find Outlook.Application

I'm running Excel 2000 and Outlook 2003 (i know that's a dumb setup, but it's the only thing allowed at work).

Ideas?????????

 

[Claymationator]

Try this link for ideas with using Excel + Outlook. I have used a few examples from here and they work great.

http://www.rondebruin.nl/sendmail.htm

 

[tonycm1]

EXCELLENT LINK!!! Thank you so much!

 

[tonycm1]

Can Outlook Templates (*.oft) files be opened by Excel VBA however?

 

[tonycm1]

Solved my own question. Here's the code for future reference... just 6 lines of code

Sub OutlookTemplate()

Dim myolapp As Object
Dim myitem As Object

Set myolapp = CreateObject("Outlook.Application")
myolapp.Session.Logon

Set myitem = myolapp.CreateItemFromTemplate("C:\test.oft")
myitem.Display 'or send

End Sub

 

[amitbhatt]

Solved my own question. Here's the code for future reference... just 6 lines of code

Sub OutlookTemplate()

Dim myolapp As Object
Dim myitem As Object

Set myolapp = CreateObject("Outlook.Application")
myolapp.Session.Logon

Set myitem = myolapp.CreateItemFromTemplate("C:\test.oft")
myitem.Display 'or send

End Sub

i have 4 different template and i want to send each template with bind with Email id list ..

can you help me create macro which work in below excel file . ..

To	CC	template Location	Attachment Location
abc@abc.com	xyz@xyz.com	C:/1.oft	C:/1.xls
test@test.com	help@help.com	C:/2.oft	C:/2.xls

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

thanks in advance.

amit bhatt

 

[bulletbikeguy]

Try this link for ideas with using Excel + Outlook. I have used a few examples from here and they work great.

Mail from Excel example pages

I know I'm resurrecting this thread, but this link is amazing! I just integrated some notable functionality into my reporting tools. THANK YOU!