Can't include range in body of email

[Lizard07]

Hi I would like to have the text in range A1 and the text in Columns B and C displayed as the body of my email. The macro I currently have (below) will not include Columns B and C in the body even if I get rid of range A1 in the code.

Can you not have multiple cell contents as the email's body? Help, please!


Sub Send_StatusUpdate()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim CurrFile As String
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

CurrFile = ActiveWorkbook.FullName
Rows("3:3").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="LEQO"


Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)
With olMail
.To = "lizblair07@gmail.com"
.CC = ""
.BCC = ""
.Subject = "Status Update"
.body = ActiveSheet.Range("A1").Text & ActiveSheet.Range("B:C").Text & vbCrLf
.Send
Rows("3:3").Select
Selection.AutoFilter


MsgBox ("Emails Sent For: LEQO")
End With
End Sub

 

[Trevor G]

Take a look at this sample I am using send keys to act as copy and paste

Sub EmailRange()
'*******************************************************************************
'This code requires you to Set the References. GoTo Tools > Reference >Search
'For Microsoft Outlook (A number) .Object Library and tick the box
'The following sample will look to email a filtered list of data with todays date
'Created by Trevor G
'May 2011
'*******************************************************************************
Dim OutlookApp As Outlook.Application
Dim MailSelection As Object
Dim cell As Range
Dim Subject As String
Dim EmailAddress As String
ThisWorkbook.Sheets("Karl To Elsie").Select
Range("A3").Select
Selection.AutoFilter
ActiveSheet.Range("$A$3:$j$35").AutoFilter Field:=4, Criteria1:= _
xlFilterToday, Operator:=xlFilterDynamic 'Adjust the range of cells
Range("A3").Select
ActiveCell.CurrentRegion.Copy
Set OutlookApp = CreateObject("Outlook.Application")
Set MailSelection = OutlookApp.CreateItem(0)
With MailSelection
.To = "trevor_g@xxxx.com" 'Change to email address
.Subject = "Invoice"
.Display 'Change to send once checked.
SendKeys "^({v})", True 'This is the same as using Paste
End With
' End If
'Next
End Sub

 

[Lizard07]

I've been playing around with this and I still can't figure it out, I'm receiving an object not defined error....please help

Sub Send_StatusUpdate()
Dim OutlookApp As Outlook.Application
Dim MailSelection As Object
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim cell As Range
Dim Subject As String
Dim OutMail As Outlook.Application


Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
CurrFile = ActiveWorkbook.FullName
Rows("3:3").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="LEQO"

Range("B3:C50").Select
ActiveCell.CurrentRegion.Copy
Set OutlookApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With olMail
.To = "kljklsdfjkl@email.com"
.Subject = "Status Update"
.Send
SendKeys "^({v})", True
End With
Rows("3:3").Select
Selection.AutoFilter

MsgBox ("Emails Sent For: LEQO")
End Sub

 

[John_w]

Try the code at http://www.rondebruin.nl/mail/folder3/mail4.htm.

 

[Xander117]

"CurrFile", In your first one you have it as a string, nothing in your second.

 

[Lizard07]

That worked perfectly! I think theres even some more features I can add to it to make it customizable.

Thank-you!

 

[Lizard07]

Hey John_w - is there anyway I could also add a signature to your code?

 

[John_w]

It's not my code. Do you mean a signature that Outlook can automatically append to a message? If so, I don't know, but I'm sure it's possible.

If instead you mean HTML-formatted text generated by your code, just include it in the HTMLBody property.