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