Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Email Worksheet Selection as Body

Status
Not open for further replies.

AccordingToDale

Programmer
Jul 11, 2005
128
CA
Hi all,

Not much of a VBA guy, but...

In Excel you can select a range and email that selection. The selection is 'pasted' (formatting intact) in the body of the email, not sent as an attachment.

What I want to do is accomplish that through VBA. I can send a workbook as an attachement, but I would rather send just a selection in the body.

Anyone got a clue on this?

d
 
Look at EnvelopeVisible, MailEnvelope, SendMail properties/methods.

combo
 
Found something that works perfectly.

Thought I'd post the code in case anyone one else wants it.

Code:
Sub sendRange(ByVal rngeSend As Range)
    'Sends a specified range in an Outlook message and retains Excel formatting
    '**************************
    '[URL unfurl="true"]http://www.danielklann.com[/URL]
    '**************************
    
    'Dimension variables
    Dim oOutlookApp As Object, oOutlookMessage As Object
    Dim oFSObj As Object, oFSTextStream As Object
    'Dim rngeSend As Range, strHTMLBody As String, strTempFilePath As String
    
    'Select the range to be sent
    On Error Resume Next
    'Set rngeSend = cellRange 
    
    '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

Daniel, I don't know if you ever come here, but THANX!

d
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top