Basic Excel macro problem with outlook

[mike0021]

Hi, I am having a problem with a script.

I am just trying to send a range of cells from my spreadsheet throug
Outlook. The code works if I just send ONE cell, but if I send th
range it just returns "TRUE" in the body. Any idea what is up?

Sub Email()
Dim o
Dim m
Dim Msg As String
Dim emailAddress As String



Application.DisplayAlerts = False


Set o = CreateObject("Outlook.Application")
Set m = o.CreateItem(0)


emailAddress = Cells(24, 1)
m.To = emailAddress

m.Subject = " Partials Report "
Msg = Range("A1", "C5").Select


m.body = Msg

m.Send

End Sub



Thanks all,

Mik

 

[Ron de Bruin]

Hi Mike

Try this

http://www.rondebruin.nl/sendmail.htm#Outlook

 

[Bob Phillips]

Mike,

I think you have problems with the approach.

Check Ron's page for various techniques www.rondebruin.nl/sendmail.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[mike0021]

hey Ron,

Your page is great!!!!
I found a great script to use, but it is set up for all of th
worksheets in the workbook, and I just want it to use one worksheet
How can I edit?


I commented out the lines that called for all of the worksheets... bu
I cannot tell it just to use "calcs". Any suggestions?

Thanks so much!!!


Sub Outlook_Mail_every_Worksheet2()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim ws As Worksheet
Application.ScreenUpdating = False
Set olApp = New Outlook.Application


'Sheets("Calcs").Select


ws = ThisWorkbook.Worksheets("Calcs")




'For Each ws In ThisWorkbook.Worksheets
'If ws.Range("a1").Value Like "*@*" Then
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "" & ws.Name
.HTMLBody = SheetToHTML(ws)
.Send
End With
Set olMail = Nothing
' End If
'Next ws
Set olApp = Nothing
Application.ScreenUpdating = True
End Sub


Public Function SheetToHTML(sh As Worksheet)

Dim TempFile As String
Dim Nwb As Workbook
Dim myshape As Shape
Dim fso As Object
Dim ts As Object
sh.Copy
Set Nwb = ActiveWorkbook
For Each myshape In Nwb.Sheets(1).Shapes
myshape.Delete
Next
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss"
& ".htm"
Nwb.SaveAs TempFile, xlHtml
Nwb.Close False
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
SheetToHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Functio

 

[Ron de Bruin]

Hi Mike

http://www.rondebruin.nl/sendmail.htm#body