Excel Email Range in Body AND Signature

[teatimecrumpet]

Hi,

I'm familiar with the ron debruin code he has all over the net. The code that will copy a range and paste it into a body and then send it works but I can't seem to marry that up with showing my signature as well.

Does anyone have any examples handy?


Ron's code with my attempt to marry the signature and the range:

Sub EMAIL222()
' You need to use this module with the RangetoHTML subroutine.
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String

Set rng = Nothing
On Error Resume Next
' Only send the visible cells in the selection.
Set rng = ActiveSheet.Range("L44:N49")
' You can also use a range with the following statement.
' Set rng = Sheets("YourSheet").Range("D4:D12").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)

SigString = "C:\Users\" & Environ("tea") & _
"\AppData\Roaming\Microsoft\Signatures\Mysig.htm"
If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If

On Error Resume Next
With OutMail
.To = "teatimecrumpet@email.com"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng) & "

" & Signature
.display
' In place of the following statement, you can use ".Display" to
' display the e-mail message.
'.Send
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing

End Sub

I also have the getboiler function within the module as well.

 

[daverunt]

Hi,

I recall a post that said you need to set the body format.
Also check reference: Tools > References > Microsoft Outlook object library

Can't find the post again but it's worth a try.

'Set body format to HTML
.BodyFormat = olFormatHTML
.HTMLBody = RangetoHTML(rng) & " " & Signature

Let us know if it works.

 

[teatimecrumpet]

Hi Dave,

Thanks for replying and with that code. That with a modification to the signature path did the trick. I had to change the sig path to:

SigString = "C:\Users\tea\AppData\Roaming\Microsoft\Signatures\Mysig.htm"

I'm not sure what the "environ" part of Ron's code does and what leaving it effectively did. Would love to hear if anyone knows what it does.

Here is my modified code in full:

Sub TeaMail()
' You need to use this module with the RangetoHTML subroutine.
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim SigString As String
Dim Signature As String

Set rng = Nothing
On Error Resume Next
' Only send the visible cells in the selection.
Set rng = ActiveSheet.Range("L44:N49")
' You can also use a range with the following statement.
' Set rng = Sheets("YourSheet").Range("D4:D12").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)

SigString = "C:\Users\tea\AppData\Roaming\Microsoft\Signatures\Mysig.htm"

If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If

On Error Resume Next
With OutMail
.To = "teatimecrumpet@email.com"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.BodyFormat = olFormatHTML
.HTMLBody = RangetoHTML(rng) & "<br> <br> " & Signature
.Display
' In place of the following statement, you can use ".Display" to
' display the e-mail message.
'.Send
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Thanks!