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
|