Copying Excel directly into Outlook

[bdouglas1011]

I have been using this code and a macro to past a range of cells directly into the body of an email.

The issue is I have now added an Logo and when I use the macro everything but the Logo comes thru, but if I manually highlight and copy/paste the logo comes thru.

here is what I have been using: What need to be changed to get the logo to come thru.


Sub CovercopypasteOutlook()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set rng = Nothing
Set rng = ActiveSheet.Range("B1:I46")


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = ""
.HTMLBody = RangetoHTML(rng)
'.Send
.display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing


End Sub

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"

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

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

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

TempWB.Close savechanges:=False
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

[LJMetzger]

Hi,

Ron de Bruin's RangeToHTML function to attach an Excel Range to an Outlook e-email is awesome. See the following link to modify it add an image:
http://www.mrexcel.com/forum/excel-q...mail-body.html

Lewis

[bdouglas1011]

I am not an experienced user so would it be possible to add the corrected Code with in mine I am using to get it to work properly. I tried a few things but not getting logo thru yet. I also attached the sheet I am using if that is easier

Also, if there is an easier way to add the page to Outlook other than than what I m doing let me know. all I am doing is taking part of an excel sheet that is completed daily ( B1 thru I46) and by clicking a macro button it automatically cuts and pastes a portion of that page directly into an email body.

I also have a simple button that ads it to the clipboard for those that don't use Outlook and that one copies the logo just fine

[LJMetzger]

I'll give it a try. It may take a couple of days so please be patient.

Lewis

[LJMetzger]

I apologize, but I was unsuccessful in doing what you want. Here are some additional links that may be helpful for someone with more skill than I possess.
http://www.outlookcode.com/d/code/htmlimg.htm
http://www.excelforum.com/excel-prog...rnal-file.html
http://www.jpsoftwaretech.com/excel-...il-from-excel/

I suggest you do the following:
a. Wait a couple of days to see if you get any responses.
b. If not, close this thread (mark it as solved).
c. Open a new thread with the same question, with a link to this thread.

Hopefully someone will respond to the new thread.

Lewis

[Sean Thomas]

TRY THIS

Please Login or Register  to view this content.

[LJMetzger]

Simply Brilliant Sean (if that's not an oxymoron).

Lewis

[Sean Thomas]

Lewis,
I am afraid i cannot take credit for that one.
Way over my head how it works.

I found it on this site
http://www.office-loesung.de/ftopic380280_0_0_asc.php

[bdouglas1011]

Hey Sean, I just got to try that code and it seems to bring it over but then it says the image cannot be displayed. And I also notice that it pastes everything over in the body of the email. Not sure if that really matters. I tried to send the email on and see how it looked when received and the logo did not come through either on the email that was sent.

Any thoughts

[Sean Thomas]

Sorry,
I have had a quick look but I am not sure how this works, it might be the size or type of image that is causing a problem.

[Sean Thomas]

I have not been able to resolve the first method.
However here is another method that will copy the visible range & images to a jpeg and then add this as an image to the html body.
I know its not the same as the text being copied but it works, depending on what is required with the info in the mail.

Please Login or Register  to view this content.

[Sean Thomas]

Lewis,
did you say you used my code successfully? its just that i had the same issue as bdouglas and got the white box with a cross in it saying unable to display image.
Hence why i have now gone down the route of adding an image of the range.

Very strange and i could not find a solution to it.

[LJMetzger]

Sean said:

did you say you used my code successfully?

I do not have outlook. When I tested the software I clicked on the temporary .htm file that was created. I had both a .jpg file and an Excel Shape inside the range I was using.

However, the software creates a folder that contains:
a. a master file that lists other files that are required
b. Copies of the other files such as the .jpg file and the details of the shape.

See the attached files which contain:
a. jpg image of my range. I saw the same thing before and after.
b. .htm file. I see the same thing using the .htm file because my computer accesses the other files. My speculation is that when you look at the .htm file, there will be empty squares where the shapes and .jpg image were in my Excel sheet.

Lewis

[Sean Thomas]

Lewis,
you are correct, for some reason on the first code i applied, it is not adding the .jpg and therefore leaving a blank space.
This is a little deep for me, so i may look into it later if i get some time as i don't like leaving things not working.
Hopefully my last code will be okay for bdouglas.

[bdouglas1011]

Sean, I copied this latest one into my worksheet and when I run it I get This macro may not be avail or they are disabled and I know they are not. Now the first one is the one I get the farthest with ... it just wont display the picture.

Also I would like to Have the generated email include the active signature on file with outlook as I normally does when you create a new email

[Sean Thomas]

where did you place it? in a sheet module? best to place it in a normal module.
I will take a look at the signature part. I have some code for that, although it can get tricky if people have more than one signature on their system.

[bdouglas1011]

I think it is a normal Module: I have attached the Excel Sheet and a print screen of how it comes through you can see how it pastes in the middle of new mail instead of at the left of the page and you will notice the pictures not displaying.. I don't know if it matters the pictures are PNG I had saved as JPEG and it still came through like this.

[Sean Thomas]

Have you tried my 2nd code that copies the range & images and inserts it into the body of the mail as an image?
Is that okay as I cannot get the original code to work properly.

[bdouglas1011]

Hey that worked this time it is coming through blurry though and how do I modify to take out 'This is a pic'
.HTMLBody = "<html><p>This is a picture.</p>" & "<img src=" & "MyImage.jpg" & "'>" ' height=480 width=360>"

Do I just delete part of it?

Any way to clear up the image.?

[bdouglas1011]

I a question you may know the answer to could I not generate a form in Word for my guys to fill in the details and have it locked to where they can only enter into certain areas and then run a macro to paste into outlook. I dont have to use excel for this report?

[Sean Thomas]

Hey that worked this time it is coming through blurry though and how do I modify to take out 'This is a pic'
.HTMLBody = "<html><p>This is a picture.</p>" & "<img src=" & "MyImage.jpg" & "'>" ' height=480 width=360>"

Do I just delete part of it?

Any way to clear up the image.?

Cant do anything about the quality of the image.

change it to
.HTMLBody = "<html><img src=" & "MyImage.jpg" & "'>"

I have seen code where you can use a word document. Basically you have a word document saved, excel updates word, then you can copy that to outlook.
This gets more complicated. It might be easier just using a word document.
Alternatively can you not copy sheet 1 to a new workbook and add that to outlook?