brennaboy
02-08-2011, 06:03 AM
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
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