Send eMail with Refeerence to a Certain Cell Value

[Bit 7 SL]

I downloaded the following script:

http://www.rondebruin.nl/mail/change.htm

which sends an email in MS Outlook when a cell reaches a certain value. The text that is sent is controlled by the lines

Please Login or Register  to view this content.

What I want to do is put in the string body a reference to a cell value in the worksheet e.g.

If the referenced cell is D3 and the value of D3 represents monthly earnings, the string in the outlook email would say

"The earnings is" {D3} "for this month."

I don't know how to reference a cell value within a string. I am not a VB programmer but a mechanical engineer who needs to get this done a quickly as possible.

Thanks for any help.

[snb]

Please Login or Register  to view this content.

[Bit 7 SL]

Thanks for the help!

I'd like to embed the cell value in the middle of the string on the same text line in the message e.g.

"You have " [cell value "workhours remaining"

The posted solution works but I am splitting the output string into two lines (see red text):

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Good Morning" & vbNewLine & vbNewLine & _
"This is a test of the Project Budget Alert Macro" & vbNewLine & _
"The budget for your project has reached 95% of the PO" & vbNewLine & _
"You have " & Sheets(1).Cells(2, 5).Value & vbNewLine & _
"workhours remaining" & vbNewLine & vbNewLine & _
"Please forward an email to the customer alerting them of the project status. You have only 10 manhours left in your budget" & vbNewLine & _
"SL"

On Error Resume Next
With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "Project Budget Status Macro Test"
.Body = strbody
.Attachments.Add ("N:\project\_Active Project Report\Active Projects_6_3_10.pdf")
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


How can I join the two lines?

[snb]

Please Login or Register  to view this content.