Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Basic Filters Problem | Setting up and Configuration of Excel | |||
Formatting problem with basic excel sheet | New Users to Excel | |||
Basic problem with EXCEL 2002 | Excel Discussion (Misc queries) | |||
Set up basic Macro in EXcel to use in other applications | Excel Discussion (Misc queries) | |||
Excel Macro Basic Help | Excel Programming |