Insert object into e-mail body?

[geralf]

I'm using a function in Access to automatically send an e-mail report in Excel format as an attachement. I would like to insert the Excel file as an object into the body of the e-mail. You can do this manually, but how can this be done using VBA? I'm using the Outlook object ftom Access.

Thanks in advance

 

[Geoff Codd]

Sub SendRange()

'Sends a specified range in an Outlook message and retains Excel formatting

'**************************
'http://www.danielklann.com
'**************************

'Dimension variables
Dim oOutlookApp As Object
Dim oOutlookMessage As Object
Dim oFSObj As Object
Dim oFSTextStream As Object
Dim rngeSend As Range
Dim strHTMLBody As String
Dim strTempFilePath As String



'Select the range to be sent
On Error Resume Next
Set rngeSend = ActiveSheet.Range("B50:K58")
If rngeSend Is Nothing Then Exit Sub 'User pressed Cancel
On Error GoTo 0


'Get the temp folder path
Set oFSObj = CreateObject("Scripting.FilesystemObject")
strTempFilePath = oFSObj.GetSpecialFolder(2)
strTempFilePath = strTempFilePath & "\XLRange.htm"


'Now create the HTML file - NOTE! xlSourceRange and xlHtmlStatic have been replaced by their
'numeric values due to a potential error (unexplained) noted by Ivan F Moala 15/5/03
ActiveWorkbook.PublishObjects.Add(4, strTempFilePath, _
rngeSend.Parent.Name, rngeSend.Address, 0, "", "").Publish True

'Create an instance of Outlook (or use existing instance if it already exists
Set oOutlookApp = CreateObject("Outlook.Application")

'Create a mail item
Set oOutlookMessage = oOutlookApp.CreateItem(0)

'Open the HTML file using the FilesystemObject into a TextStream object
Set oFSTextStream = oFSObj.OpenTextFile(strTempFilePath, 1)

'Now set the HTMLBody property of the message to the text contained in the TextStream object
strHTMLBody = oFSTextStream.ReadAll

'By default the range will be centred. This line left aligns it and you can
'comment it out if you want the range centred.
strHTMLBody = Replace(strHTMLBody, "align=center", "align=left", , , vbTextCompare)

oOutlookMessage.HTMLBody = strHTMLBody

oOutlookMessage.Display

End Sub

 

[geralf]

Hi!

I'm sorry for the exceptional late reply here Geoff. After I started this thread I got my computer destroyed by lightning causing electrical shock to my system. It did take some time til I was up and running again since I had to build a new one.

Now I'm online again with a new machine.

I haven't yet tried your code, The format hasn't been decided how it should look like, or if they want it inserted as an object in the body or just a attached file. I will test it though for future use.

Thanks for yor reply. Much appreciated.