Need to open Outlook Template out of Excel

[grohm]

Hi all!

I need to open up a already saved outlook template (C:\template.oft) automatically out of excel.

I found lots of articales about opening word out of excel or reverse or opening a new email, which is not a problem, but i just don't get it how to open a specific file....

any help?

[mvidas]

Hi grohm,

Are you going to need to modify the template, or just simply open/display it for the user?

If simply displaying it is what you need:Public Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Sub OpenFile()
ShellExecute 0&, vbNullString, "C:\template.oft", vbNullString, _
vbNullString, vbMinimizedNoFocus
End SubIf you do need to interact with it via VBA let me know, but that isn't usually the case with template files.

Matt

[grohm]

Hi Matt! Thanks for the reply

Wow, could you maybe explain that code a little? Has a lot of never seen-stuff for me inthere :dunno

I will explain my situation a lil better:

Actually, i have a data range in Excel, which i want to get copied to the clipboard (everything fine so far) then i want excel to open up a template of outlook (which i already have formatted, inserted text, etc.) and copy the data from the clipboard into the template.

Thanks for having a look!

Ben

[Bob Phillips]

Bypass the clipboard. See http://www.rondebruin.nl/mail/folder1/mail4.htm

[mvidas]

The 'Public Declare Function' part is what is called an API call (application programming interface), it allows you to use an external file/library's functions directly. I'm using the shell32.dll library, and calling the "ShellExecute" function from within it. Don't worry if you dont recognize either of those, but shellexecute is used to open any file/link with it's default viewer. Helpful when opening websites in the user's default browser/etc. My OpenFile sub just calls that function. Googling for ShellExecute or shell32.dll API should give you more information, should you want it, but shellexecute is really the only API call i ever make from that .dll, and all I ever do is change the "C:\template.oft" portion of it when I need to.
As an FYI, any Declare statement like that has to go ABOVE any procedures (subs/functions) in a module.

But to interact with the template, use the following as a basis, using the .htmlbody OR the .body of the template to change things within it. Note that in later versions of outlook you'll get a security warning doing this.Sub OpenAndModifyTemplate()
Dim vOL As Object, vItem As Object, vStr As String

vStr = "" 'whatever you want added to the template file

Set vOL = CreateObject("Outlook.Application")
Set vItem = vOL.CreateItemFromTemplate("C:\template.oft")

'if your template is HTML format, use this:
vItem.HTMLBody = vItem.HTMLBody & "<br><br>" & vStr
'otherwise, use this:
vItem.Body = vItem.Body & vbCrLf & vbCrLf & vStr
'"<br>" is HTML code for line break
'vbCrLf is a new line in flat text

''then, either display it or send it
vItem.Display
''or
' vItem.Send

Set vItem = Nothing
Set vOL = Nothing
End Sub
Let me know if you need anything else
Matt

[mvidas]

And I completely agree with Bob.. I use this function frequently when emailing data:Public Function RangetoHTML(ByVal vRange As Range)
' modified from original function from http://www.rondebruin.nl/mail/folder3/mail4.htm
' You can't use this function in Excel 97
Dim vFF As Long, tempStr As String, TempFile As String
TempFile = "C:\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With vRange.Parent.Parent.PublishObjects.Add(SourceType:=xlSourceRange, FileName:= _
TempFile, Sheet:=vRange.Parent.Name, Source:=vRange.Address, HtmlType:=xlHtmlStatic)
.Publish (True)
End With
vFF = FreeFile
Open TempFile For Binary As #vFF
tempStr = Space$(LOF(vFF))
Get #vFF, , tempStr
Close #vFF
RangetoHTML = tempStr
Kill TempFile
End FunctionMatt

[grohm]

Hi Guys

All your examples work well, but is there a way that i could merge your solutions with my old code below? (At least i understand that one :-)
At the moment the only thing that needs to be changed is that it needs to save the current selection (instead of the whole sheet) to a excel sheet and put that sheet into the attachment of the email. It would also be nice if it pastes the selection in outlook right away, but that's not necessary....

Sub Button2_Click()

Dim message As Object, OutApp As Object
Set OutApp = CreateObject("Outlook.Application")
Dim AWS As String

active sheet gets sent by email
AWS = ThisWorkbook.FullName
Set message = OutApp.CreateItem(0)

With message
To = "xxx@xxy.com"
.Subject = "Test SUbject" & Date & Time
.attachments.Add AWS
.Body = "Hi there!"
'mail gets displayed
.Display
'mail goes into sent items
Mail.Send
End With
'Outlook schliessen
Set OutApp = Nothing
Set Nachricht = Nothing
End Sub

[grohm]

btw....

Is there anywhere a good structured knowledge source for the "Object" type? This one seems to be one of the more versatile/difficult commands in VBA an i have actually no overview of what it's capable to do and how to handle. Basic Help is not really useful if you don't understand it....

[mvidas]

Hi Ben,

Re: "Object" type
The Object variable type is just a generic variable for a variable of a class' type, the Excel Application is an object type variable, just as an Excel Range is, the Outlook Application, Outlook Message, Word Document, as well as about a million+ other types. We're using Object here instead of using the actual type to avoid having to set a reference to that type's library. I'm no expert, and probably not the best instructor on such a thing, so you may want to either look around google or post a separate question for this.

VBA Code tag explanation
As for your code, I simply used the .htmlbody property with the rangetohtml function calling the Selection object (selected cells):Sub Button2_Click()

Dim message As Object, OutApp As Object
Set OutApp = CreateObject("Outlook.Application")

'active sheet gets sent by email
Set message = OutApp.CreateItem(0)

With message
.To = "xxx@xxy.com"
.Subject = "Test SUbject" & Date & Time
'add selected cells as HTML to message
.HTMLBody = RangetoHTML(Selection)
'mail gets displayed
.Display
'mail goes into sent items - I commented this out so you only see the message
'Mail.Send
End With
'Outlook schliessen
Set OutApp = Nothing
Set Nachricht = Nothing
End Sub
Public Function RangetoHTML(ByVal vRange As Range)
' modified from original function from http://www.rondebruin.nl/mail/folder3/mail4.htm
' You can't use this function in Excel 97
Dim vFF As Long, tempStr As String, TempFile As String
TempFile = "C:\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With vRange.Parent.Parent.PublishObjects.Add(SourceType:=xlSourceRange, Filename:= _
TempFile, Sheet:=vRange.Parent.Name, Source:=vRange.Address, HtmlType:=xlHtmlStatic)
.Publish (True)
End With
vFF = FreeFile
Open TempFile For Binary As #vFF
tempStr = Space$(LOF(vFF))
Get #vFF, , tempStr
Close #vFF
RangetoHTML = tempStr
Kill TempFile
End FunctionMatt

[grohm]

Hi Matt

Thanks for that one!!!! Works perfectly. Exactly what i wanted :-)

thanks for the explanation, too.

Greetz

Ben

[grohm]

Hi Ben,

Re: "Object" type
The Object variable type is just a generic variable for a variable of a class' type, the Excel Application is an object type variable, just as an Excel Range is, the Outlook Application, Outlook Message, Word Document, as well as about a million+ other types. We're using Object here instead of using the actual type to avoid having to set a reference to that type's library. I'm no expert, and probably not the best instructor on such a thing, so you may want to either look around google or post a separate question for this.

VBA Code tag explanation
As for your code, I simply used the .htmlbody property with the rangetohtml function calling the Selection object (selected cells):Sub Button2_Click()

Dim message As Object, OutApp As Object
Set OutApp = CreateObject("Outlook.Application")

'active sheet gets sent by email
Set message = OutApp.CreateItem(0)

With message
.To = "xxx@xxy.com"
.Subject = "Test SUbject" & Date & Time
'add selected cells as HTML to message
.HTMLBody = RangetoHTML(Selection)
'mail gets displayed
.Display
'mail goes into sent items - I commented this out so you only see the message
'Mail.Send
End With
'Outlook schliessen
Set OutApp = Nothing
Set Nachricht = Nothing
End Sub
Public Function RangetoHTML(ByVal vRange As Range)
' modified from original function from http://www.rondebruin.nl/mail/folder3/mail4.htm
' You can't use this function in Excel 97
Dim vFF As Long, tempStr As String, TempFile As String
TempFile = "C:\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With vRange.Parent.Parent.PublishObjects.Add(SourceType:=xlSourceRange, Filename:= _
TempFile, Sheet:=vRange.Parent.Name, Source:=vRange.Address, HtmlType:=xlHtmlStatic)
.Publish (True)
End With
vFF = FreeFile
Open TempFile For Binary As #vFF
tempStr = Space$(LOF(vFF))
Get #vFF, , tempStr
Close #vFF
RangetoHTML = tempStr
Kill TempFile
End FunctionMatt


Matt, i need to come back on this one.

This works fine but what if i have a template which needs to get openend and not a blank email? if i try this code with a before saved template, it overwrites everything and displays only the html copied range. Now is thre any parameter which i can chnage in order that it does not overwrite everything in the email template stored?

[grohm]

.

[mvidas]

Sure thing.. the .HTMLBody property is what is being written to, and can also be read. Make the following change (assuming your template is HTML Format):'.HTMLBody = RangetoHTML(Selection)
.HTMLBody = .HTMLBody & "<BR><BR>" & RangetoHTML(Selection)
If your template is just plain text format, the following should work:.HTMLBody = .Body & "<BR><BR>" & RangetoHTML(Selection)

EDIT: Added "<BR><BR>" html line break codes

[mvidas]

And to try and make sure it will work regardless of blank or html/text format:.HTMLBody = IIf(Len(.HTMLBody) > 0, .HTMLBody & "<BR><BR>", _
IIf(Len(.Body) > 0, .Body & "<BR><BR>", "")) & RangetoHTML(Selection)

[grohm]

thanks, matt. i'll have a look later today.

Cheers

ben