Outlook to excel problem

[BORUCH]

I have this code below which pasted my selection to outlook i have 2 problems
#1 it deletes my outlook signature i would like my signature to go underneath the table
#2 for some reason the bottom border is not visible how can i have it that it should pasted it just like its on the original sheet

any help is greatly appreciated

Sub Mail_Selection_Range_Outlook_Body()


Dim rng As Range

Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String
Set rng = Nothing
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
Set rng = Selection.SpecialCells(xlCellTypeVisible)


On Error GoTo 0


If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If


With Application
.EnableEvents = False
.ScreenUpdating = False
End With


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
StrBody = "Hi" & "<br><br>" & _
"Just a friendly reminder that you have a balance of " & " " & Range("AB1") & "<br><br>" & _
"Any questions or concerns please feel free to contact me " & "<br><br>" & _
"We appreciate your continuing business, and we look forward to hearing from you shortly."
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "STATEMENT"
.HTMLBody = StrBody & RangetoHTML(rng)
.Display
End With
On Error GoTo 0


With Application
.EnableEvents = True
.ScreenUpdating = True
End With


Set OutMail = Nothing
Set OutApp = Nothing
Range("A1").Select
End Sub




Function RangetoHTML(rng As Range)


Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook


TempFile = Environ$("temp") & "" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"


'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)

.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With


'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With



Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.readall
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center xublishsource=", _
"align=left xublishsource=")


'Close TempWB
TempWB.Close savechanges:=False


'Delete the htm file we used in this function
Kill TempFile


Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

 

[BORUCH]

Any updates ?

 

[gruntingmonkey]

So I have a verion of doing this (Point 1). We can muddle through it to work out what you need..

I think the first line you should specify where your signature is stored. The second is once you have declared where its stored, it goes to get it?

SigString = "C:\Documents and Settings\" & Environ("username") & "\Application Data\Microsoft\Signatures\" & Dir("C:\Documents and Settings\" & Environ("username") & "\Application Data\Microsoft\Signatures\" & "*.htm")
 If Dir(SigString) <> "" Then
      Signature = GetBoiler(SigString)
  Else
      Signature = ""
  End If



Function GetBoiler(ByVal sFile As String) As String
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

And remember to add it in here also

.HTMLBody = StrBody & RangetoHTML(rng) & Signature

And if all that is not helpful, try the following link

Insert Outlook Signature in mail