Results 1 to 5 of 5

Opening Outlook Template in Excel VBA

This is a discussion on Opening Outlook Template in Excel VBA within the Excel Questions forums, part of the Question Forums category; Alright, I've been researching this for the last few hours without much luck. Essentially, I've written some VBA for excel ...

  1. #1
    New Member
    Join Date
    Aug 2007
    Posts
    47

    Exclamation Opening Outlook Template in Excel VBA

    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?????????

  2. #2
    Board Regular Claymationator's Avatar
    Join Date
    Sep 2006
    Posts
    696

    Default Re: Opening Outlook Template in Excel VBA

    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
    Microsoft Windows 7
    Microsoft Office 2007

  3. #3
    New Member
    Join Date
    Aug 2007
    Posts
    47

    Default Re: Opening Outlook Template in Excel VBA

    EXCELLENT LINK!!! Thank you so much!

  4. #4
    New Member
    Join Date
    Aug 2007
    Posts
    47

    Default Re: Opening Outlook Template in Excel VBA

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

  5. #5
    New Member
    Join Date
    Aug 2007
    Posts
    47

    Default Re: Opening Outlook Template in Excel VBA

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com