Help!!! Emailing more than one range from excel

michaelcole

New Member
Joined
Mar 15, 2004
Messages
32
I Have been using the code below to send a range from excel via outlook.

What i want to to do now is send two parts of the spreadsheet.

The other range being B10:N15.


The Email would look like be

Range B3:J7
/
/
Range B10:N15

is this possable??
--------------------------

Sub Send_Range()

Sends a specified range in an Outlook message and retains Excel formatting
'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 = ActiveSheet.Range("B3:J7")
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.To = "UK DAILY E-MAIL"
oOutlookMessage.Subject = Range("B22")
oOutlookMessage.HTMLBody = strHTMLBody

oOutlookMessage.Display
ActiveWorkbook.Save

End Sub
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi,

I re-wrote the code to allow for multiple ranges to be sent. Try this and let me know how you get on :)

Code:
Sub Send_Range()

'Dimension variables
    Dim oOutlookApp As Object, oOutlookMessage As Object
    Dim strBody As String

    strBody = ConvertRangeToHTML(ActiveSheet.Range("B3:J7"))
    strBody = strBody & "/
/"
    strBody = strBody & ConvertRangeToHTML(ActiveSheet.Range("B10:N15"))


    '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)


    oOutlookMessage.To = "UK DAILY E-MAIL"
    oOutlookMessage.Subject = Range("B22")
    oOutlookMessage.HTMLBody = strBody

    oOutlookMessage.Display
    ActiveWorkbook.Save

End Sub

Private Function ConvertRangeToHTML(rngeSend As Range) As String
    Dim strHTMLBody As String, strTempFilePath As String
    Dim oFSObj As Object, oFSTextStream As Object


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

    'Publish the range as HTML
    ActiveWorkbook.PublishObjects.Add(4, strTempFilePath, _
            rngeSend.Parent.Name, rngeSend.Address, 0, "", "").Publish True


    '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
    oFSTextStream.Close

    '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)

    ConvertRangeToHTML = strHTMLBody


    'Clean up object variables
    Set oFSTextStream = Nothing
    Set oFSObj = Nothing


End Function
 

Big_Mike

New Member
Joined
Dec 10, 2003
Messages
11
I tried the code and it generates an error at the noted code below with both versions of the above.

Run time error 1004. Method " Publish " of object " PublishObject " failed

Code:
ActiveWorkbook.PublishObjects.Add(4, strTempFilePath, _
rngeSend.Parent.Name, rngeSend.Address, 0, "", "").Publish True

Is there a reference or something I missed to get it going ?

Mike
 

Watch MrExcel Video

Forum statistics

Threads
1,122,715
Messages
5,597,726
Members
414,169
Latest member
Preston_Cleric

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top