|
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
I want to adapt this:
.HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Hi Corey,
See Ron de Bruin's example code at: http://www.rondebruin.nl/mail/folder3/mail4.htm --- Regards, Norman "Corey" wrote in message ... I want to adapt this: .HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Thanks.
I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. Corey.... "Norman Jones" wrote in message ... Hi Corey, See Ron de Bruin's example code at: http://www.rondebruin.nl/mail/folder3/mail4.htm --- Regards, Norman "Corey" wrote in message ... I want to adapt this: .HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Hi Corey,
I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. The suggested code includes the line: Set source = Selection Try changing Selection to your required range. --- Regards, Norman "Corey" wrote in message ... Thanks. I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. Corey.... "Norman Jones" wrote in message ... Hi Corey, See Ron de Bruin's example code at: http://www.rondebruin.nl/mail/folder3/mail4.htm --- Regards, Norman "Corey" wrote in message ... I want to adapt this: .HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Thnaks again Norman, but i cannot get ONLY a range of cells to email instead
of the whole activesheet. Code current below: Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value ' address in sheet .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value ' cc address in sheet .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value ' subject line info in sheet ' .Body = bodyStr.("Sheet1").Range("B45:I107") <----------------- Tried this to no avail also .HTMLBody = SheetToHTML(ActiveSheet) ' <----------------- WANT TO SET THIS TO SEND IN BODY AS HTML ONLY RANGE("B45:I107") NOT WHOLE SHEET ' .Attachments.Add () Add a file address here to add an attachment later .Display '.send to auto send without prompting End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub I looked at the 'Set source = Selection' but i could not get it to work either. Any idea's ? Corey.... "Norman Jones" wrote in message ... Hi Corey, I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. The suggested code includes the line: Set source = Selection Try changing Selection to your required range. --- Regards, Norman "Corey" wrote in message ... Thanks. I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. Corey.... "Norman Jones" wrote in message ... Hi Corey, See Ron de Bruin's example code at: http://www.rondebruin.nl/mail/folder3/mail4.htm --- Regards, Norman "Corey" wrote in message ... I want to adapt this: .HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Hi Corey
Look at this link http://www.rondebruin.nl/mail/folder3/mail4.htm You see this line in the code Set source = Selection Change that to Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") Note that I use the Function RangetoHTML in this example -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thnaks again Norman, but i cannot get ONLY a range of cells to email instead of the whole activesheet. Code current below: Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value ' address in sheet .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value ' cc address in sheet .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value ' subject line info in sheet ' .Body = bodyStr.("Sheet1").Range("B45:I107") <----------------- Tried this to no avail also .HTMLBody = SheetToHTML(ActiveSheet) ' <----------------- WANT TO SET THIS TO SEND IN BODY AS HTML ONLY RANGE("B45:I107") NOT WHOLE SHEET ' .Attachments.Add () Add a file address here to add an attachment later .Display '.send to auto send without prompting End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub I looked at the 'Set source = Selection' but i could not get it to work either. Any idea's ? Corey.... "Norman Jones" wrote in message ... Hi Corey, I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. The suggested code includes the line: Set source = Selection Try changing Selection to your required range. --- Regards, Norman "Corey" wrote in message ... Thanks. I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. Corey.... "Norman Jones" wrote in message ... Hi Corey, See Ron de Bruin's example code at: http://www.rondebruin.nl/mail/folder3/mail4.htm --- Regards, Norman "Corey" wrote in message ... I want to adapt this: .HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Thanks Ron.
I put this in the code, but still get the whole sheet in the email body. Do i drop off somehting here ? ..HTMLBody = SheetToHTML(ActiveSheet) <----- Corey.... "Ron de Bruin" wrote in message ... Hi Corey Look at this link http://www.rondebruin.nl/mail/folder3/mail4.htm You see this line in the code Set source = Selection Change that to Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") Note that I use the Function RangetoHTML in this example -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thnaks again Norman, but i cannot get ONLY a range of cells to email instead of the whole activesheet. Code current below: Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value ' address in sheet .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value ' cc address in sheet .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value ' subject line info in sheet ' .Body = bodyStr.("Sheet1").Range("B45:I107") <----------------- Tried this to no avail also .HTMLBody = SheetToHTML(ActiveSheet) ' <----------------- WANT TO SET THIS TO SEND IN BODY AS HTML ONLY RANGE("B45:I107") NOT WHOLE SHEET ' .Attachments.Add () Add a file address here to add an attachment later .Display '.send to auto send without prompting End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub I looked at the 'Set source = Selection' but i could not get it to work either. Any idea's ? Corey.... "Norman Jones" wrote in message ... Hi Corey, I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. The suggested code includes the line: Set source = Selection Try changing Selection to your required range. --- Regards, Norman "Corey" wrote in message ... Thanks. I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. Corey.... "Norman Jones" wrote in message ... Hi Corey, See Ron de Bruin's example code at: http://www.rondebruin.nl/mail/folder3/mail4.htm --- Regards, Norman "Corey" wrote in message ... I want to adapt this: .HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Code i am now using in module3:
Sub Macro3() ' ' Macro3 Macro ' Macro recorded 24/06/2006 by Corey Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") ' <------------------------- .Body = bodyStr .HTMLBody = RangetoHTML2 ' <------------------------- ' .Attachments.Add () Add a file address here to add an attachment later .Display '.send to auto send without prompting End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub Public Function RangetoHTML2() ' <----------------------------- ONWARDS ' You can't use this function in Excel 97 Dim fso As Object Dim ts As Object Dim TempFile As String TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" With ActiveWorkbook.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=ActiveSheet.Name, _ source:=ActiveSheet.UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML2 = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Kill TempFile End Function Corey.... "Corey" wrote in message ... Thanks Ron. I put this in the code, but still get the whole sheet in the email body. Do i drop off somehting here ? .HTMLBody = SheetToHTML(ActiveSheet) <----- Corey.... "Ron de Bruin" wrote in message ... Hi Corey Look at this link http://www.rondebruin.nl/mail/folder3/mail4.htm You see this line in the code Set source = Selection Change that to Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") Note that I use the Function RangetoHTML in this example -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thnaks again Norman, but i cannot get ONLY a range of cells to email instead of the whole activesheet. Code current below: Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value ' address in sheet .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value ' cc address in sheet .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value ' subject line info in sheet ' .Body = bodyStr.("Sheet1").Range("B45:I107") <----------------- Tried this to no avail also .HTMLBody = SheetToHTML(ActiveSheet) ' <----------------- WANT TO SET THIS TO SEND IN BODY AS HTML ONLY RANGE("B45:I107") NOT WHOLE SHEET ' .Attachments.Add () Add a file address here to add an attachment later .Display '.send to auto send without prompting End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub I looked at the 'Set source = Selection' but i could not get it to work either. Any idea's ? Corey.... "Norman Jones" wrote in message ... Hi Corey, I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. The suggested code includes the line: Set source = Selection Try changing Selection to your required range. --- Regards, Norman "Corey" wrote in message ... Thanks. I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. Corey.... "Norman Jones" wrote in message ... Hi Corey, See Ron de Bruin's example code at: http://www.rondebruin.nl/mail/folder3/mail4.htm --- Regards, Norman "Corey" wrote in message ... I want to adapt this: .HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Please read
Look at this link http://www.rondebruin.nl/mail/folder3/mail4.htm You see this line in the code Set source = Selection Change that to Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") Note that I use the Function RangetoHTML in this example -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thanks Ron. I put this in the code, but still get the whole sheet in the email body. Do i drop off somehting here ? .HTMLBody = SheetToHTML(ActiveSheet) <----- Corey.... "Ron de Bruin" wrote in message ... Hi Corey Look at this link http://www.rondebruin.nl/mail/folder3/mail4.htm You see this line in the code Set source = Selection Change that to Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") Note that I use the Function RangetoHTML in this example -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thnaks again Norman, but i cannot get ONLY a range of cells to email instead of the whole activesheet. Code current below: Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value ' address in sheet .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value ' cc address in sheet .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value ' subject line info in sheet ' .Body = bodyStr.("Sheet1").Range("B45:I107") <----------------- Tried this to no avail also .HTMLBody = SheetToHTML(ActiveSheet) ' <----------------- WANT TO SET THIS TO SEND IN BODY AS HTML ONLY RANGE("B45:I107") NOT WHOLE SHEET ' .Attachments.Add () Add a file address here to add an attachment later .Display '.send to auto send without prompting End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub I looked at the 'Set source = Selection' but i could not get it to work either. Any idea's ? Corey.... "Norman Jones" wrote in message ... Hi Corey, I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. The suggested code includes the line: Set source = Selection Try changing Selection to your required range. --- Regards, Norman "Corey" wrote in message ... Thanks. I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. Corey.... "Norman Jones" wrote in message ... Hi Corey, See Ron de Bruin's example code at: http://www.rondebruin.nl/mail/folder3/mail4.htm --- Regards, Norman "Corey" wrote in message ... I want to adapt this: .HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Hi Corey,
Try something like: Sub Mail_Selection_Outlook_Body() ' You must add a reference to the Microsoft outlook Library ' Don't forget to copy the function RangetoHTML in the module. ' Is not working in Office 97 Dim source As Range Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set source = Nothing On Error Resume Next Set source = ThisWorkbook.Sheets("Sheet1").Range("A1:D20") On Error GoTo 0 If source Is Nothing Then MsgBox "The selection is not a range or the sheet is protect" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If If ActiveWindow.SelectedSheets.Count 1 Or _ source.Cells.Count = 1 Or _ source.Areas.Count 1 Then MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _ "You have more than one sheet selected." & vbNewLine & _ "You only selected one cell." & vbNewLine & _ "You selected more than one area." & vbNewLine & vbNewLine & _ "Please correct and try again.", vbOKOnly Exit Sub End If Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value .HTMLBody = RangetoHTML(source) .Send 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing Application.ScreenUpdating = True End Sub Public Function RangetoHTML(source As Range) ' You can't use this function in Excel 97 Dim fso As Object Dim ts As Object Dim TempFile As String TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" With ActiveWorkbook.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=ActiveSheet.Name, _ source:=source.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Kill TempFile End Function --- Regards, Norman "Corey" wrote in message ... Thanks Ron. I put this in the code, but still get the whole sheet in the email body. Do i drop off somehting here ? .HTMLBody = SheetToHTML(ActiveSheet) <----- Corey.... "Ron de Bruin" wrote in message ... Hi Corey Look at this link http://www.rondebruin.nl/mail/folder3/mail4.htm You see this line in the code Set source = Selection Change that to Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") Note that I use the Function RangetoHTML in this example -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thnaks again Norman, but i cannot get ONLY a range of cells to email instead of the whole activesheet. Code current below: Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value ' address in sheet .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value ' cc address in sheet .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value ' subject line info in sheet ' .Body = bodyStr.("Sheet1").Range("B45:I107") <----------------- Tried this to no avail also .HTMLBody = SheetToHTML(ActiveSheet) ' <----------------- WANT TO SET THIS TO SEND IN BODY AS HTML ONLY RANGE("B45:I107") NOT WHOLE SHEET ' .Attachments.Add () Add a file address here to add an attachment later .Display '.send to auto send without prompting End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub I looked at the 'Set source = Selection' but i could not get it to work either. Any idea's ? Corey.... "Norman Jones" wrote in message ... Hi Corey, I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. The suggested code includes the line: Set source = Selection Try changing Selection to your required range. --- Regards, Norman "Corey" wrote in message ... Thanks. I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. Corey.... "Norman Jones" wrote in message ... Hi Corey, See Ron de Bruin's example code at: http://www.rondebruin.nl/mail/folder3/mail4.htm --- Regards, Norman "Corey" wrote in message ... I want to adapt this: .HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Hi
My advise is not correct Add this line Range("B45:I107").Select before Set source = Nothing I update the site soon with range example -- Regards Ron De Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Please read Look at this link http://www.rondebruin.nl/mail/folder3/mail4.htm You see this line in the code Set source = Selection Change that to Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") Note that I use the Function RangetoHTML in this example -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thanks Ron. I put this in the code, but still get the whole sheet in the email body. Do i drop off somehting here ? .HTMLBody = SheetToHTML(ActiveSheet) <----- Corey.... "Ron de Bruin" wrote in message ... Hi Corey Look at this link http://www.rondebruin.nl/mail/folder3/mail4.htm You see this line in the code Set source = Selection Change that to Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") Note that I use the Function RangetoHTML in this example -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thnaks again Norman, but i cannot get ONLY a range of cells to email instead of the whole activesheet. Code current below: Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value ' address in sheet .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value ' cc address in sheet .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value ' subject line info in sheet ' .Body = bodyStr.("Sheet1").Range("B45:I107") <----------------- Tried this to no avail also .HTMLBody = SheetToHTML(ActiveSheet) ' <----------------- WANT TO SET THIS TO SEND IN BODY AS HTML ONLY RANGE("B45:I107") NOT WHOLE SHEET ' .Attachments.Add () Add a file address here to add an attachment later .Display '.send to auto send without prompting End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub I looked at the 'Set source = Selection' but i could not get it to work either. Any idea's ? Corey.... "Norman Jones" wrote in message ... Hi Corey, I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. The suggested code includes the line: Set source = Selection Try changing Selection to your required range. --- Regards, Norman "Corey" wrote in message ... Thanks. I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. Corey.... "Norman Jones" wrote in message ... Hi Corey, See Ron de Bruin's example code at: http://www.rondebruin.nl/mail/folder3/mail4.htm --- Regards, Norman "Corey" wrote in message ... I want to adapt this: .HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Ron,
Tried the below, but I STILL get the entire worksheet being emailed and not the only the range selected. Code is below: Sub Macro3() ' ' Macro3 Macro ' Macro recorded 24/06/2006 by Corey Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value Range("B45:I107").Select ' <--------------------- Set source = Nothing ' <----------------------- .Body = bodyStr .HTMLBody = RangetoHTML2 ' .Attachments.Add () Add a file address here to add an attachment later .Display '.send to auto send without prompting End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub Public Function RangetoHTML2() ' You can't use this function in Excel 97 Dim fso As Object Dim ts As Object Dim TempFile As String TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" With ActiveWorkbook.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=ActiveSheet.Name, _ source:=ActiveSheet.UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML2 = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Kill TempFile End Function ?? Corey.... "Ron de Bruin" wrote in message ... Hi My advise is not correct Add this line Range("B45:I107").Select before Set source = Nothing I update the site soon with range example -- Regards Ron De Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Please read Look at this link http://www.rondebruin.nl/mail/folder3/mail4.htm You see this line in the code Set source = Selection Change that to Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") Note that I use the Function RangetoHTML in this example -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thanks Ron. I put this in the code, but still get the whole sheet in the email body. Do i drop off somehting here ? .HTMLBody = SheetToHTML(ActiveSheet) <----- Corey.... "Ron de Bruin" wrote in message ... Hi Corey Look at this link http://www.rondebruin.nl/mail/folder3/mail4.htm You see this line in the code Set source = Selection Change that to Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") Note that I use the Function RangetoHTML in this example -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thnaks again Norman, but i cannot get ONLY a range of cells to email instead of the whole activesheet. Code current below: Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value ' address in sheet .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value ' cc address in sheet .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value ' subject line info in sheet ' .Body = bodyStr.("Sheet1").Range("B45:I107") <----------------- Tried this to no avail also .HTMLBody = SheetToHTML(ActiveSheet) ' <----------------- WANT TO SET THIS TO SEND IN BODY AS HTML ONLY RANGE("B45:I107") NOT WHOLE SHEET ' .Attachments.Add () Add a file address here to add an attachment later .Display '.send to auto send without prompting End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub I looked at the 'Set source = Selection' but i could not get it to work either. Any idea's ? Corey.... "Norman Jones" wrote in message ... Hi Corey, I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. The suggested code includes the line: Set source = Selection Try changing Selection to your required range. --- Regards, Norman "Corey" wrote in message ... Thanks. I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. Corey.... "Norman Jones" wrote in message ... Hi Corey, See Ron de Bruin's example code at: http://www.rondebruin.nl/mail/folder3/mail4.htm --- Regards, Norman "Corey" wrote in message ... I want to adapt this: .HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Norman,
I tried the code you posted below but get an error as below: "Norman Jones" wrote in message ... Hi Corey, Try something like: Sub Mail_Selection_Outlook_Body() ' You must add a reference to the Microsoft outlook Library ' Don't forget to copy the function RangetoHTML in the module. ' Is not working in Office 97 Dim source As Range Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set source = Nothing On Error Resume Next Set source = ThisWorkbook.Sheets("Sheet1").Range("A1:D20") On Error GoTo 0 If source Is Nothing Then MsgBox "The selection is not a range or the sheet is protect" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If If ActiveWindow.SelectedSheets.Count 1 Or _ source.Cells.Count = 1 Or _ source.Areas.Count 1 Then MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _ ' <---------------- I GET A SYNTAX ERROR HERE....... ? "You have more than one sheet selected." & vbNewLine & _ "You only selected one cell." & vbNewLine & _ "You selected more than one area." & vbNewLine & vbNewLine & _ "Please correct and try again.", vbOKOnly <------------------ TO HERE..... Exit Sub End If Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value .HTMLBody = RangetoHTML(source) .Send 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing Application.ScreenUpdating = True End Sub Public Function RangetoHTML(source As Range) ' You can't use this function in Excel 97 Dim fso As Object Dim ts As Object Dim TempFile As String TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" With ActiveWorkbook.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=ActiveSheet.Name, _ source:=source.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Kill TempFile End Function --- Regards, Norman "Corey" wrote in message ... Thanks Ron. I put this in the code, but still get the whole sheet in the email body. Do i drop off somehting here ? .HTMLBody = SheetToHTML(ActiveSheet) <----- Corey.... "Ron de Bruin" wrote in message ... Hi Corey Look at this link http://www.rondebruin.nl/mail/folder3/mail4.htm You see this line in the code Set source = Selection Change that to Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") Note that I use the Function RangetoHTML in this example -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thnaks again Norman, but i cannot get ONLY a range of cells to email instead of the whole activesheet. Code current below: Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value ' address in sheet .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value ' cc address in sheet .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value ' subject line info in sheet ' .Body = bodyStr.("Sheet1").Range("B45:I107") <----------------- Tried this to no avail also .HTMLBody = SheetToHTML(ActiveSheet) ' <----------------- WANT TO SET THIS TO SEND IN BODY AS HTML ONLY RANGE("B45:I107") NOT WHOLE SHEET ' .Attachments.Add () Add a file address here to add an attachment later .Display '.send to auto send without prompting End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub I looked at the 'Set source = Selection' but i could not get it to work either. Any idea's ? Corey.... "Norman Jones" wrote in message ... Hi Corey, I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. The suggested code includes the line: Set source = Selection Try changing Selection to your required range. --- Regards, Norman "Corey" wrote in message ... Thanks. I can see the code there, but cannot still find the code to Select ONLY cells say (B45:I107) Is it there some where, as i cannot see any reference to cell ranges. Corey.... "Norman Jones" wrote in message ... Hi Corey, See Ron de Bruin's example code at: http://www.rondebruin.nl/mail/folder3/mail4.htm --- Regards, Norman "Corey" wrote in message ... I want to adapt this: .HTMLBody = SheetToHTML(ActiveSheet) How can i only have a selected range of cells, or a selected page sent in the body of an email instaed of the entire sheet as it currently does? Any idea's ?? I want to send a range of ("A45:I107") or Page 1 Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Hi Corey,
I tried the code you posted below but get an error as below: <---------------- I GET A SYNTAX ERROR HERE....... ? "You have more than one sheet selected." & vbNewLine & _ "You only selected one cell." & vbNewLine & _ "You selected more than one area." & vbNewLine & vbNewLine & _ "Please correct and try again.", vbOKOnly <------------------ TO HERE The suggsted code works for me. Your problem is merely one of line breaeks: the problem section was intended as a single line of code separated by the underscore line break character. Replace the problem lines by copymg and pasting the following: MsgBox "An Error occurred :" _ & vbNewLine & vbNewLine _ & "You have more than one sheet selected." _ & vbNewLine & "You only selected one cell." _ & vbNewLine & "You selected more than one area." _ & vbNewLine & vbNewLine _ & "Please correct and try again.", vbOKOnly --- Regards, Norman |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Thanks You Norman and Ron.
Your last post corrected the syntax error i was getting. Macro worked exactly as required. Thanks for your help, appreciate it very much. I can now move another post i need to fix. Regards Corey.... "Norman Jones" wrote in message ... Hi Corey, I tried the code you posted below but get an error as below: <---------------- I GET A SYNTAX ERROR HERE....... ? "You have more than one sheet selected." & vbNewLine & _ "You only selected one cell." & vbNewLine & _ "You selected more than one area." & vbNewLine & vbNewLine & _ "Please correct and try again.", vbOKOnly <------------------ TO HERE The suggsted code works for me. Your problem is merely one of line breaeks: the problem section was intended as a single line of code separated by the underscore line break character. Replace the problem lines by copymg and pasting the following: MsgBox "An Error occurred :" _ & vbNewLine & vbNewLine _ & "You have more than one sheet selected." _ & vbNewLine & "You only selected one cell." _ & vbNewLine & "You selected more than one area." _ & vbNewLine & vbNewLine _ & "Please correct and try again.", vbOKOnly --- Regards, Norman |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
I update the site
http://www.rondebruin.nl/mail/folder3/mail4.htm Hi Corey I changed the function and macro example -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thanks You Norman and Ron. Your last post corrected the syntax error i was getting. Macro worked exactly as required. Thanks for your help, appreciate it very much. I can now move another post i need to fix. Regards Corey.... "Norman Jones" wrote in message ... Hi Corey, I tried the code you posted below but get an error as below: <---------------- I GET A SYNTAX ERROR HERE....... ? "You have more than one sheet selected." & vbNewLine & _ "You only selected one cell." & vbNewLine & _ "You selected more than one area." & vbNewLine & vbNewLine & _ "Please correct and try again.", vbOKOnly <------------------ TO HERE The suggsted code works for me. Your problem is merely one of line breaeks: the problem section was intended as a single line of code separated by the underscore line break character. Replace the problem lines by copymg and pasting the following: MsgBox "An Error occurred :" _ & vbNewLine & vbNewLine _ & "You have more than one sheet selected." _ & vbNewLine & "You only selected one cell." _ & vbNewLine & "You selected more than one area." _ & vbNewLine & vbNewLine _ & "Please correct and try again.", vbOKOnly --- Regards, Norman |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Ron, Just found i am getting an error now due to the Sheet name. What i need to reference is NOT BY Sheet NAME but by Active Sheet then Range in that Sheet, SEE Comments Below with Arrows. Sub Macro3() ' You must add a reference to the Microsoft outlook Library ' Don't forget to copy the function RangetoHTML in the module. ' Is not working in Office 97 Dim source As Range Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set source = Nothing On Error Resume Next Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:J107") <=============== Want to Replace ("Sheet1") with ActiveSheet + Range("B45:J107") On Error GoTo 0 If source Is Nothing Then MsgBox "The selection is not a range or the sheet is protect" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If If ActiveWindow.SelectedSheets.Count 1 Or _ source.Cells.Count = 1 Or _ source.Areas.Count 1 Then MsgBox "An Error occurred :" _ & vbNewLine & vbNewLine _ & "You have more than one sheet selected." _ & vbNewLine & "You only selected one cell." _ & vbNewLine & "You selected more than one area." _ & vbNewLine & vbNewLine _ & "Please correct and try again.", vbOKOnly Exit Sub End If Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value <===== ActiveSheet + Range instead of ("Sheet1") .CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value <==== ActiveSheet + Range instead of ("Sheet1") .BCC = "" .Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value <====ActiveSheet + Range instead of ("Sheet1") .HTMLBody = RangetoHTML(source) .Display 'or use .Send End With Set OutMail = Nothing Set OutApp = Nothing Application.ScreenUpdating = True End Sub Public Function RangetoHTML(source As Range) ' You can't use this function in Excel 97 Dim fso As Object Dim ts As Object Dim TempFile As String TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" With ActiveWorkbook.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=ActiveSheet.Name, _ source:=source.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Kill TempFile End Function Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Hi Corey,
Ron, Just found i am getting an error now due to the Sheet name. What i need to reference is NOT BY Sheet NAME but by Active Sheet then Range in that Sheet, SEE Comments Below with Arrows. If you follow Ron's link you will see that he has responded to your need to mail a specified range in the body of an outlook email. More specifically, Ron has today posted a revised procedure and an updated, more flexible RangetoHTML function. If you plug your specific data into Ron's new code, you will obtain the following: '============= Public Sub Mail_Selection_Outlook_Body() Dim sh As Worksheet Dim rng As Range Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set sh = ActiveSheet Set rng = sh.Range("B45:J107") Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = sh.Range("B53").Value .CC = sh.Range("E53").Value .BCC = "" .Subject = sh.Range("B55").Value .HTMLBody = RangetoHTML(sh, rng) .Display 'or use .Send End With Set OutMail = Nothing Set OutApp = Nothing Application.ScreenUpdating = True End Sub '--------------------- Public Function RangetoHTML(sh As Worksheet, rng As Range) 'Changed by Ron de Bruin 25-June-2006 ' You can't use this function in Excel 97 Dim TempFile As String Dim Nwb As Workbook Dim fso As Object Dim ts As Object sh.Copy Set Nwb = ActiveWorkbook With Nwb.Sheets(1) On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With TempFile = Environ$("temp") & "/" & _ Format(Now, "dd-mm-yy h-mm-ss") & ".htm" With Nwb.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=sh.Name, _ source:=rng.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With Nwb.Close False Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Set Nwb = Nothing Kill TempFile End Function '<<============= Note that the above code should replace your problematic code. Clearly, if the code works for you, you should thank Ron. --- Regards, Norman |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Thanks Norman for the feedback and Ron for the updated code.
It works precisely as required now. Although as i perfect the ultimate goal i have, i keep finding problems. When i create an new sheet and click on the email button, i get the sheet and the relative data in the body of the email, but the LOGO on the sheet does not show. Is there a way to include a jpg logo ? Corey.... "Norman Jones" wrote in message ... Hi Corey, Ron, Just found i am getting an error now due to the Sheet name. What i need to reference is NOT BY Sheet NAME but by Active Sheet then Range in that Sheet, SEE Comments Below with Arrows. If you follow Ron's link you will see that he has responded to your need to mail a specified range in the body of an outlook email. More specifically, Ron has today posted a revised procedure and an updated, more flexible RangetoHTML function. If you plug your specific data into Ron's new code, you will obtain the following: '============= Public Sub Mail_Selection_Outlook_Body() Dim sh As Worksheet Dim rng As Range Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set sh = ActiveSheet Set rng = sh.Range("B45:J107") Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = sh.Range("B53").Value .CC = sh.Range("E53").Value .BCC = "" .Subject = sh.Range("B55").Value .HTMLBody = RangetoHTML(sh, rng) .Display 'or use .Send End With Set OutMail = Nothing Set OutApp = Nothing Application.ScreenUpdating = True End Sub '--------------------- Public Function RangetoHTML(sh As Worksheet, rng As Range) 'Changed by Ron de Bruin 25-June-2006 ' You can't use this function in Excel 97 Dim TempFile As String Dim Nwb As Workbook Dim fso As Object Dim ts As Object sh.Copy Set Nwb = ActiveWorkbook With Nwb.Sheets(1) On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With TempFile = Environ$("temp") & "/" & _ Format(Now, "dd-mm-yy h-mm-ss") & ".htm" With Nwb.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=sh.Name, _ source:=rng.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With Nwb.Close False Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Set Nwb = Nothing Kill TempFile End Function '<<============= Note that the above code should replace your problematic code. Clearly, if the code works for you, you should thank Ron. --- Regards, Norman |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Thought i would mention, the LOGO (jpg) is actually in the Customer Header.
Corey,,,, "Corey" wrote in message ... Thanks Norman for the feedback and Ron for the updated code. It works precisely as required now. Although as i perfect the ultimate goal i have, i keep finding problems. When i create an new sheet and click on the email button, i get the sheet and the relative data in the body of the email, but the LOGO on the sheet does not show. Is there a way to include a jpg logo ? Corey.... "Norman Jones" wrote in message ... Hi Corey, Ron, Just found i am getting an error now due to the Sheet name. What i need to reference is NOT BY Sheet NAME but by Active Sheet then Range in that Sheet, SEE Comments Below with Arrows. If you follow Ron's link you will see that he has responded to your need to mail a specified range in the body of an outlook email. More specifically, Ron has today posted a revised procedure and an updated, more flexible RangetoHTML function. If you plug your specific data into Ron's new code, you will obtain the following: '============= Public Sub Mail_Selection_Outlook_Body() Dim sh As Worksheet Dim rng As Range Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set sh = ActiveSheet Set rng = sh.Range("B45:J107") Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = sh.Range("B53").Value .CC = sh.Range("E53").Value .BCC = "" .Subject = sh.Range("B55").Value .HTMLBody = RangetoHTML(sh, rng) .Display 'or use .Send End With Set OutMail = Nothing Set OutApp = Nothing Application.ScreenUpdating = True End Sub '--------------------- Public Function RangetoHTML(sh As Worksheet, rng As Range) 'Changed by Ron de Bruin 25-June-2006 ' You can't use this function in Excel 97 Dim TempFile As String Dim Nwb As Workbook Dim fso As Object Dim ts As Object sh.Copy Set Nwb = ActiveWorkbook With Nwb.Sheets(1) On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With TempFile = Environ$("temp") & "/" & _ Format(Now, "dd-mm-yy h-mm-ss") & ".htm" With Nwb.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=sh.Name, _ source:=rng.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With Nwb.Close False Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Set Nwb = Nothing Kill TempFile End Function '<<============= Note that the above code should replace your problematic code. Clearly, if the code works for you, you should thank Ron. --- Regards, Norman |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
The code delete all shapes before it make html Corey.
It is not possible to send shapes in the body with my code. Look at the KB on top if my webpage If you use 2002/2003 you can use the code in the KB -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Thought i would mention, the LOGO (jpg) is actually in the Customer Header. Corey,,,, "Corey" wrote in message ... Thanks Norman for the feedback and Ron for the updated code. It works precisely as required now. Although as i perfect the ultimate goal i have, i keep finding problems. When i create an new sheet and click on the email button, i get the sheet and the relative data in the body of the email, but the LOGO on the sheet does not show. Is there a way to include a jpg logo ? Corey.... "Norman Jones" wrote in message ... Hi Corey, Ron, Just found i am getting an error now due to the Sheet name. What i need to reference is NOT BY Sheet NAME but by Active Sheet then Range in that Sheet, SEE Comments Below with Arrows. If you follow Ron's link you will see that he has responded to your need to mail a specified range in the body of an outlook email. More specifically, Ron has today posted a revised procedure and an updated, more flexible RangetoHTML function. If you plug your specific data into Ron's new code, you will obtain the following: '============= Public Sub Mail_Selection_Outlook_Body() Dim sh As Worksheet Dim rng As Range Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set sh = ActiveSheet Set rng = sh.Range("B45:J107") Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = sh.Range("B53").Value .CC = sh.Range("E53").Value .BCC = "" .Subject = sh.Range("B55").Value .HTMLBody = RangetoHTML(sh, rng) .Display 'or use .Send End With Set OutMail = Nothing Set OutApp = Nothing Application.ScreenUpdating = True End Sub '--------------------- Public Function RangetoHTML(sh As Worksheet, rng As Range) 'Changed by Ron de Bruin 25-June-2006 ' You can't use this function in Excel 97 Dim TempFile As String Dim Nwb As Workbook Dim fso As Object Dim ts As Object sh.Copy Set Nwb = ActiveWorkbook With Nwb.Sheets(1) On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With TempFile = Environ$("temp") & "/" & _ Format(Now, "dd-mm-yy h-mm-ss") & ".htm" With Nwb.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=sh.Name, _ source:=rng.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With Nwb.Close False Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Set Nwb = Nothing Kill TempFile End Function '<<============= Note that the above code should replace your problematic code. Clearly, if the code works for you, you should thank Ron. --- Regards, Norman |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Ron,
Thanks for the reply, AGAIN. Sorry to be persistantly questioning. I am running Office 2002, and the MS code did the trick to include my Logo. It somewhat operates differently to your code as it diplays ALL the active sheet in the display email prompt, BUT ONLY emails the selected RANGE. Where your code ONLY displayed the actual emailed RANGES. I prefered your code but it wont include the logo. It is a little scary sending the email with the MS code as the 1st page of the active sheet has COSTING information that is not for the recipients eyes, but they ONLY recieve the page 2 Quote page, as BOTH pages are viewed before sending, but ONLY the page 2 Quote is sent. Thanks for your asistance with this task. Cheers Corey.... |
Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Hi Corey
has COSTING information Copy this on a diferent sheet. You can send the whole sheet then in the body or as a attachment http://www.rondebruin.nl/mail/folder2/mail2.htm -- Regards Ron De Bruin http://www.rondebruin.nl "Corey" wrote in message ... Ron, Thanks for the reply, AGAIN. Sorry to be persistantly questioning. I am running Office 2002, and the MS code did the trick to include my Logo. It somewhat operates differently to your code as it diplays ALL the active sheet in the display email prompt, BUT ONLY emails the selected RANGE. Where your code ONLY displayed the actual emailed RANGES. I prefered your code but it wont include the logo. It is a little scary sending the email with the MS code as the 1st page of the active sheet has COSTING information that is not for the recipients eyes, but they ONLY recieve the page 2 Quote page, as BOTH pages are viewed before sending, but ONLY the page 2 Quote is sent. Thanks for your asistance with this task. Cheers Corey.... |
| All times are GMT +1. The time now is 10:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com