PDA

View Full Version : Excel - Send sheet & use range as body



hmltnangel
07-14-2014, 06:33 AM
Ok folks,

My VBA isnt the greatest but I can normally piece together most bits and make it work

This however has me scratching my head.

I want to click a button and have the following happen

Sheet 1 - gets sent on an email as an attachment, a range from Sheet 2 will be the body of the email.

I so far mad ethis - which fails :(


Sub Mail()
Dim OutlookApp As Object
Dim Mess As Object, Recip As String
ActiveSheet.Copy
ActiveWorkbook.SaveAs Range("C6").Value
Recip = [F28].Value
Set Sendrng = Worksheets("Sheet2").Range("B1:B10")
Set OutlookApp = CreateObject("Outlook.Application")
Set Mess = OutlookApp.CreateItem(olMailItem)
With Mess
.Subject = [C6]
.Body = [Sendrng]
.Recipients.Add Recip
.Send
End With
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub


Any help to finish it off?

hmltnangel
07-14-2014, 06:42 AM
Right, got a bit further now ..... I had a few things in the wrong order.

Now its stuck on the "Body" Line


Sub Mail()
Dim OutlookApp As Object
Dim Mess As Object, Recip As String
Recip = [F28].Value
Set Sendrng = Worksheets("Sheet2").Range("B1:B10")
ActiveSheet.Copy
ActiveWorkbook.SaveAs Range("C6").Value
Set OutlookApp = CreateObject("Outlook.Application")
Set Mess = OutlookApp.CreateItem(olMailItem)
With Mess
.Subject = [C6]
.Body = Sendrng
.Recipients.Add Recip
.Send
End With
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub

snb
07-14-2014, 08:06 AM
This might help:

http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html#L27