Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send mail from excel - Copy/paste unformatted values and error
Hi Ron,
I wasn't sure if you would respond to my old question so I resubmitted. Other users have tested on their computers with low-level security settings and still not working. Can you help? Thanks! --------------- Hi Brice Try to change the security first on the machine to test if it is working then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" in message ... Hello, can somebody help me with exact code? I don't know how to fix these two issues. Thanks so much, Brice "Brice" wrote: Hi Ron, in regards to copy/past unformatted values, I went through the weblinks you provided and am still very confused. Is it possible you can still help me with both requests by providing full code? From, The Novice "Brice" wrote: Hi Ron, Replacing with "C:/TestFolder" doesn't work. Macro creates workbook with email body data and keeps workbook open. Macro also creates an email without email body details. Please advise Thanks "Ron de Bruin" wrote: Hi Brice Strange, but you can change Environ$("temp") To another folder TempFile = "C:/TestFolder" & ....................... Also, I would like to copy & paste unformatted values into the email. Is this possible? Please provide code if possible. See http://www.rondebruin.nl/mail/folder3/smallmessage.htm And read the info in the tips page (link on top of the page) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" wrote in message ... The below code (which creates email from a range/selection in excel) currently works on my computer but I would like my colleagues to use it too. When they try on their computers they receive an script error message. The debugger identifies "Environ$" as problem in VBA a script. How do I fix this? Also, I would like to copy & paste unformatted values into the email. Is this possible? Please provide code if possible. I would really appreciate your help! Thanks, Brice Code: --------------------------------------------- Function RangetoHTML(rng As Range) Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function ----------------------------------------------------------- Sub Mail_Selection_Range_Outlook_Body_Recall() ' Don't forget to copy the function RangetoHTML in the module. Dim rng As Range Dim OutApp As Object Dim OutMail As Object Set rng = Nothing On Error Resume Next 'You can also use a range if you want Set rng = Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If With Application .EnableEvents = False .ScreenUpdating = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = Sheets("Recall").Range("O9").Value .CC = " .BCC = "" .Subject = "Wire Receipt" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send as replacement to auto-send email without review End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send mail from excel - Copy/paste unformatted values and error
Send me your test workbook private and I take a look
this evening. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" wrote in message ... Hi Ron, I wasn't sure if you would respond to my old question so I resubmitted. Other users have tested on their computers with low-level security settings and still not working. Can you help? Thanks! --------------- Hi Brice Try to change the security first on the machine to test if it is working then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" in message ... Hello, can somebody help me with exact code? I don't know how to fix these two issues. Thanks so much, Brice "Brice" wrote: Hi Ron, in regards to copy/past unformatted values, I went through the weblinks you provided and am still very confused. Is it possible you can still help me with both requests by providing full code? From, The Novice "Brice" wrote: Hi Ron, Replacing with "C:/TestFolder" doesn't work. Macro creates workbook with email body data and keeps workbook open. Macro also creates an email without email body details. Please advise Thanks "Ron de Bruin" wrote: Hi Brice Strange, but you can change Environ$("temp") To another folder TempFile = "C:/TestFolder" & ....................... Also, I would like to copy & paste unformatted values into the email. Is this possible? Please provide code if possible. See http://www.rondebruin.nl/mail/folder3/smallmessage.htm And read the info in the tips page (link on top of the page) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" wrote in message ... The below code (which creates email from a range/selection in excel) currently works on my computer but I would like my colleagues to use it too. When they try on their computers they receive an script error message. The debugger identifies "Environ$" as problem in VBA a script. How do I fix this? Also, I would like to copy & paste unformatted values into the email. Is this possible? Please provide code if possible. I would really appreciate your help! Thanks, Brice Code: --------------------------------------------- Function RangetoHTML(rng As Range) Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function ----------------------------------------------------------- Sub Mail_Selection_Range_Outlook_Body_Recall() ' Don't forget to copy the function RangetoHTML in the module. Dim rng As Range Dim OutApp As Object Dim OutMail As Object Set rng = Nothing On Error Resume Next 'You can also use a range if you want Set rng = Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If With Application .EnableEvents = False .ScreenUpdating = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = Sheets("Recall").Range("O9").Value .CC = " .BCC = "" .Subject = "Wire Receipt" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send as replacement to auto-send email without review End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send mail from excel - Copy/paste unformatted values and error
Hi Ron, I emailed my test workbook. Thanks for taking a look at the code!
"Ron de Bruin" wrote: Send me your test workbook private and I take a look this evening. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" wrote in message ... Hi Ron, I wasn't sure if you would respond to my old question so I resubmitted. Other users have tested on their computers with low-level security settings and still not working. Can you help? Thanks! --------------- Hi Brice Try to change the security first on the machine to test if it is working then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" in message ... Hello, can somebody help me with exact code? I don't know how to fix these two issues. Thanks so much, Brice "Brice" wrote: Hi Ron, in regards to copy/past unformatted values, I went through the weblinks you provided and am still very confused. Is it possible you can still help me with both requests by providing full code? From, The Novice "Brice" wrote: Hi Ron, Replacing with "C:/TestFolder" doesn't work. Macro creates workbook with email body data and keeps workbook open. Macro also creates an email without email body details. Please advise Thanks "Ron de Bruin" wrote: Hi Brice Strange, but you can change Environ$("temp") To another folder TempFile = "C:/TestFolder" & ....................... Also, I would like to copy & paste unformatted values into the email. Is this possible? Please provide code if possible. See http://www.rondebruin.nl/mail/folder3/smallmessage.htm And read the info in the tips page (link on top of the page) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" wrote in message ... The below code (which creates email from a range/selection in excel) currently works on my computer but I would like my colleagues to use it too. When they try on their computers they receive an script error message. The debugger identifies "Environ$" as problem in VBA a script. How do I fix this? Also, I would like to copy & paste unformatted values into the email. Is this possible? Please provide code if possible. I would really appreciate your help! Thanks, Brice Code: --------------------------------------------- Function RangetoHTML(rng As Range) Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function ----------------------------------------------------------- Sub Mail_Selection_Range_Outlook_Body_Recall() ' Don't forget to copy the function RangetoHTML in the module. Dim rng As Range Dim OutApp As Object Dim OutMail As Object Set rng = Nothing On Error Resume Next 'You can also use a range if you want Set rng = Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If With Application .EnableEvents = False .ScreenUpdating = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = Sheets("Recall").Range("O9").Value .CC = " .BCC = "" .Subject = "Wire Receipt" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send as replacement to auto-send email without review End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send mail from excel - Copy/paste unformatted values and error
Hi Ron, I emailed my test workbook. Thanks for taking a look at the code!
"Ron de Bruin" wrote: Send me your test workbook private and I take a look this evening. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" wrote in message ... Hi Ron, I wasn't sure if you would respond to my old question so I resubmitted. Other users have tested on their computers with low-level security settings and still not working. Can you help? Thanks! --------------- Hi Brice Try to change the security first on the machine to test if it is working then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" in message ... Hello, can somebody help me with exact code? I don't know how to fix these two issues. Thanks so much, Brice "Brice" wrote: Hi Ron, in regards to copy/past unformatted values, I went through the weblinks you provided and am still very confused. Is it possible you can still help me with both requests by providing full code? From, The Novice "Brice" wrote: Hi Ron, Replacing with "C:/TestFolder" doesn't work. Macro creates workbook with email body data and keeps workbook open. Macro also creates an email without email body details. Please advise Thanks "Ron de Bruin" wrote: Hi Brice Strange, but you can change Environ$("temp") To another folder TempFile = "C:/TestFolder" & ....................... Also, I would like to copy & paste unformatted values into the email. Is this possible? Please provide code if possible. See http://www.rondebruin.nl/mail/folder3/smallmessage.htm And read the info in the tips page (link on top of the page) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" wrote in message ... The below code (which creates email from a range/selection in excel) currently works on my computer but I would like my colleagues to use it too. When they try on their computers they receive an script error message. The debugger identifies "Environ$" as problem in VBA a script. How do I fix this? Also, I would like to copy & paste unformatted values into the email. Is this possible? Please provide code if possible. I would really appreciate your help! Thanks, Brice Code: --------------------------------------------- Function RangetoHTML(rng As Range) Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function ----------------------------------------------------------- Sub Mail_Selection_Range_Outlook_Body_Recall() ' Don't forget to copy the function RangetoHTML in the module. Dim rng As Range Dim OutApp As Object Dim OutMail As Object Set rng = Nothing On Error Resume Next 'You can also use a range if you want Set rng = Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If With Application .EnableEvents = False .ScreenUpdating = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = Sheets("Recall").Range("O9").Value .CC = " .BCC = "" .Subject = "Wire Receipt" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send as replacement to auto-send email without review End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send mail from excel - Copy/paste unformatted values and error
Hi Ron, I emailed my test workbook. Thanks for taking a look at the code!
"Ron de Bruin" wrote: Send me your test workbook private and I take a look this evening. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" wrote in message ... Hi Ron, I wasn't sure if you would respond to my old question so I resubmitted. Other users have tested on their computers with low-level security settings and still not working. Can you help? Thanks! --------------- Hi Brice Try to change the security first on the machine to test if it is working then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" in message ... Hello, can somebody help me with exact code? I don't know how to fix these two issues. Thanks so much, Brice "Brice" wrote: Hi Ron, in regards to copy/past unformatted values, I went through the weblinks you provided and am still very confused. Is it possible you can still help me with both requests by providing full code? From, The Novice "Brice" wrote: Hi Ron, Replacing with "C:/TestFolder" doesn't work. Macro creates workbook with email body data and keeps workbook open. Macro also creates an email without email body details. Please advise Thanks "Ron de Bruin" wrote: Hi Brice Strange, but you can change Environ$("temp") To another folder TempFile = "C:/TestFolder" & ....................... Also, I would like to copy & paste unformatted values into the email. Is this possible? Please provide code if possible. See http://www.rondebruin.nl/mail/folder3/smallmessage.htm And read the info in the tips page (link on top of the page) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" wrote in message ... The below code (which creates email from a range/selection in excel) currently works on my computer but I would like my colleagues to use it too. When they try on their computers they receive an script error message. The debugger identifies "Environ$" as problem in VBA a script. How do I fix this? Also, I would like to copy & paste unformatted values into the email. Is this possible? Please provide code if possible. I would really appreciate your help! Thanks, Brice Code: --------------------------------------------- Function RangetoHTML(rng As Range) Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function ----------------------------------------------------------- Sub Mail_Selection_Range_Outlook_Body_Recall() ' Don't forget to copy the function RangetoHTML in the module. Dim rng As Range Dim OutApp As Object Dim OutMail As Object Set rng = Nothing On Error Resume Next 'You can also use a range if you want Set rng = Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If With Application .EnableEvents = False .ScreenUpdating = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = Sheets("Recall").Range("O9").Value .CC = " .BCC = "" .Subject = "Wire Receipt" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send as replacement to auto-send email without review End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send mail from excel - Copy/paste unformatted values and error
Hi Ron, I emailed my test workbook. Thanks for taking a look at the code!
"Ron de Bruin" wrote: Send me your test workbook private and I take a look this evening. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" wrote in message ... Hi Ron, I wasn't sure if you would respond to my old question so I resubmitted. Other users have tested on their computers with low-level security settings and still not working. Can you help? Thanks! --------------- Hi Brice Try to change the security first on the machine to test if it is working then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" in message ... Hello, can somebody help me with exact code? I don't know how to fix these two issues. Thanks so much, Brice "Brice" wrote: Hi Ron, in regards to copy/past unformatted values, I went through the weblinks you provided and am still very confused. Is it possible you can still help me with both requests by providing full code? From, The Novice "Brice" wrote: Hi Ron, Replacing with "C:/TestFolder" doesn't work. Macro creates workbook with email body data and keeps workbook open. Macro also creates an email without email body details. Please advise Thanks "Ron de Bruin" wrote: Hi Brice Strange, but you can change Environ$("temp") To another folder TempFile = "C:/TestFolder" & ....................... Also, I would like to copy & paste unformatted values into the email. Is this possible? Please provide code if possible. See http://www.rondebruin.nl/mail/folder3/smallmessage.htm And read the info in the tips page (link on top of the page) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brice" wrote in message ... The below code (which creates email from a range/selection in excel) currently works on my computer but I would like my colleagues to use it too. When they try on their computers they receive an script error message. The debugger identifies "Environ$" as problem in VBA a script. How do I fix this? Also, I would like to copy & paste unformatted values into the email. Is this possible? Please provide code if possible. I would really appreciate your help! Thanks, Brice Code: --------------------------------------------- Function RangetoHTML(rng As Range) Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function ----------------------------------------------------------- Sub Mail_Selection_Range_Outlook_Body_Recall() ' Don't forget to copy the function RangetoHTML in the module. Dim rng As Range Dim OutApp As Object Dim OutMail As Object Set rng = Nothing On Error Resume Next 'You can also use a range if you want Set rng = Sheets("Recall").Range("$N$11:$O$20").SpecialCells (xlCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If With Application .EnableEvents = False .ScreenUpdating = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = Sheets("Recall").Range("O9").Value .CC = " .BCC = "" .Subject = "Wire Receipt" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send as replacement to auto-send email without review End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Send mail from excel - Copy/paste unformatted values and error mes | Excel Programming | |||
Macro to Paste Unformatted Text Into Excel | Excel Programming | |||
Error send mail with CDO | Excel Programming | |||
formatted sheets get unformatted when send via email attachment? | Excel Worksheet Functions | |||
Excel Copy and Paste into Outlook e-mail | Excel Programming |