Range for Body of E-Mail

B

BillCPA

I am trying to use a range of cells in Excel as the body for an e-mail in
Outlook. The name for the range is 'DuesReceipt'. It doesn't seem to like
setting olBody to the range. Can someone help?

Public olToName As String
Public olccName As String
Public olbccName As String
Public olSubject As String
Public olBody As String
Public olRange As Object
Public olAttach1 As String

Sub SendReceipt()

Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim SigString As String
Dim Signature As String

Dim xx As Integer
Dim yy As Integer
Dim zz As Integer

Set olApp = New Outlook.Application

SigString = "C:\Documents and Settings\...\Signatures\BillLong.txt"
If Dir(SigString) = "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If

olToName = Range("K5").Value
olSubject = "Dues Receipt"
Set olRange = Range("DuesReceipt")

Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = olToName
.Subject = olSubject
.Body = olRange
.Send
End With

Set olMail = Nothing
Set olApp = Nothing

End Sub
 
D

Dave Peterson

Maybe that DuesReceipt named range has more than one cell?

..Body = olRange.Cells(1).value

I'd declare olRange as a Range, too.
 
R

Ron de Bruin

Hi Bill

Or maybe you want this signature example
http://www.rondebruin.nl/mail/folder3/signature.htm


And use

Dim cell As Range
Dim strbody As String
For Each cell In ThisWorkbook.Sheets("Sheet1").Range("C1:C60")
strbody = strbody & cell.Value & vbNewLine
Next

Instead of

strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"


See this page for HTML body examples
http://www.rondebruin.nl/mail/folder3/mail4.htm
 
B

BillCPA

DuesReceipt is cells A1:I9.

If I send it manually, I just highlight A1:I9, copy, and paste into the
e-mail - I guess it is like a table.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top