Basic Excel macro problem with outlook
 

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

--
Message posted from http://www.ExcelForum.com

Basic Excel macro problem with outlook
 

Hi Mike

Try this

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"mike0021 " wrote in message ...
Hi, I am having a problem with a script.

I am just trying to send a range of cells from my spreadsheet through
Outlook. The code works if I just send ONE cell, but if I send the
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,

Mike


---
Message posted from http://www.ExcelForum.com/

Basic Excel macro problem with outlook
 

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 " wrote in message
...
Hi, I am having a problem with a script.

I am just trying to send a range of cells from my spreadsheet through
Outlook. The code works if I just send ONE cell, but if I send the
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,

Mike


---
Message posted from http://www.ExcelForum.com/

Basic Excel macro problem with outlook
 

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 = "
.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

--
Message posted from http://www.ExcelForum.com

Basic Excel macro problem with outlook
 

Hi Mike

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"mike0021 " wrote in message ...
hey Ron,

Your page is great!!!!
I found a great script to use, but it is set up for all of the
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... but
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 = "
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 Function


---
Message posted from http://www.ExcelForum.com/