Sending Excel Cell Data via E-Mail

[brennaboy]

Hi,

I have the following macro (see below) which I am putting behind a command button on an Excel worksheet to send an automatic E-Mail.

Can anyone tell me how I can add cell information to the E-Mail?

So for instance, inplace of "MoreText" I would like to put the contents of say Cell D5.

Many thanks,

Bren.

Sub PSSendToRenato()
Application.DisplayAlerts = False
Dim mpOutlook As Object
Dim mpMailItem As Object
Dim mpRecipient As Object
Dim mpNameSpace As Object
ActiveWorkbook.SaveAs "C:\Documents and Settings\balexan7\Desktop\MyFile.xls"

Set mpOutlook = CreateObject("Outlook.Application")
Set mpNameSpace = mpOutlook.GetNameSpace("MAPI")
mpNameSpace.Logon , , True

Set mpMailItem = mpOutlook.CreateItem(0) 'olMailItem
Set mpRecipient = mpMailItem.Recipients.Add("balexan7")
mpRecipient.Type = 1 'olTo - cc is 2

Application.DisplayAlerts = False

With mpMailItem

.Subject = "Can you please push these through PayStation"
.Body = "MyText" & vbCrLf & "MoreText"
.Display '<<< this will not send, change to .Send if required
.Send
End With

Set mpRecipient = Nothing
Set mpMailItem = Nothing
Set mpNameSpace = Nothing
Set mpOutlook = Nothing

Application.DisplayAlerts = True
End Sub

[Tinbendr]

.Body = "MyText" & vbCrLf & "MoreText" & Sheet1.Range("D5").Value


Please use VBA tags when posting code. In the edit window, it's the Green square with VBA in it.

Thanks

[brennaboy]

That is excellent.

Just another quick question if I may...

When I tested this, it took a value of 1,089.90 from the SS and put 1089.9 in the E-Mail.

Is there anyway to keep the formatting or abolsute value rather than remove . and remove 0 at the end?

Many thanks,

Bren.

[Tinbendr]

.Body = "MyText" & vbCrLf & "MoreText" & _
Format(Sheet1.Range("D5").Value , "0.00")