Copy Excel Range into Outlook Template

[fxstrat]

Hey everyone,

I have an email template (.oft file) with a heading and below it I would like to paste a picture of my table in Excel using a VBA macro.

I looked at this great website: https://www.rondebruin.nl/win/s1/outlook/bmail2.htm
But I was only able to paste my table (in non-picture form) in a new email, not into my template. Would anyone be able to please help?

Many thanks in advance!

 

[kpasa]

Take a look here on Stack Overflow. Similar answer was provided.

https://stackoverflow.com/questions/42091390/vba-range-to-jpg-picture

 

[fxstrat]

Take a look here on Stack Overflow. Similar answer was provided.

https://stackoverflow.com/questions/42091390/vba-range-to-jpg-picture

Thanks kpasa. That code has helped me save my range as a jpg, but I am struggling to paste that into my outlook .oft template. Would you have any assistance on this?

Thanks again for your input!

 

[kpasa]

Thanks kpasa. That code has helped me save my range as a jpg, but I am struggling to paste that into my outlook .oft template. Would you have any assistance on this?

I'm not sure how your'e calling up the template as the active message. I create new message in outlook from excel using the following Sub:

Sub ComposeEmail(emailTO, emailCC, emailBCC, emailSubject, HTMLbody, AttachmentPath As String)


    Dim signature As String
    
    Dim OutApp As Object
    Dim OutMail As Object


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    On Error Resume Next
    
    With OutMail
        .BodyFormat = 2 'html format
        .Display
    End With
        signature = OutMail.HTMLbody
    With OutMail
        .TO = emailTO
        .CC = emailCC
        .BCC = emailBCC
        .Subject = emailSubject
        .HTMLbody = HTMLbody & signature
        If varAttachment = "" Then
            'No attachemnt, do nothing
        Else
            .Attachments.Add AttachmentPath
        End If
        '.Display
        'Application.Wait (Now + TimeValue("0:00:03"))
        '.Send
    End With
    
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing


End Sub

In order to add the jpg in the message itself, you add the jpg as an attachement and then embed the image in the html body using the img tag:

.Attachments.Add JpgFilePath, olByValue, 0
.HTMLBody = .HTMLBody &

At this point it just becomes a battle of setting up the HTML. I'd be happy to do it for you. Just send me the .oft text.

Edit: Since the page is interpreting the img tag, here it is in plain text: <i m g s r c ='cid:PictureName.jpg'" & "width=width height=heigth><mg src="cid:PictureName.jpg" "="" &="" "width="width" height="heigth</html"></mg>

 

[kpasa]

3rd Time's the charm:
img src=
'cid:PictureName.jpg'" &
"width=width height=heigth

 

[fxstrat]

Thanks for getting back to me kpasa. Really appreciate the help. My .oft template does not have text at the top but a logo picture, could this be inserted using HTML?

Also I have no requirement to attach the picture of the range, just paste it. Would it be possible to do this by saving it to a temp location rather than to a specific directory so it can be deleted after?

I have attempted to put the code together below. Unforetunetly I got slightly confused by the "img src=" code you sent and it brings up and error. Apologies for all the questions, as you can see my VBA skills are very basic. Thanks again for your help!



Sub Export()


Dim ws As Worksheet
Dim Rng As Range
Dim Chrt As Chart


Set ws = ActiveSheet
Set Rng = Range("B2:H11")


ws.Range(Rng).CopyPicture
Set Chrt = Charts.Add


With Chrt
.Paste
.Export Filename = "C:\Users\Admin\Desktop\Case.jpg", Filtername:="JPG"
End With


End Sub




Sub ComposeEmail()




Dim signature As String
Dim OutApp As Object
Dim OutMail As Object


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

On Error Resume Next

With OutMail
.BodyFormat = 2 'html format
.Display
End With
signature = OutMail.HTMLbody

With OutMail
.TO = "hi@gmail.com"
.CC = ""
.BCC = ""
.Subject = "emailSubject"
.HTMLbody = HTMLbody & signature
img src = "C:\Users\Admin\Desktop\Case.jpg"
'& "width=width height=heigth"
If varAttachment = "" Then
'No attachemnt, do nothing
Else
.Attachments.Add AttachmentPath
End If
.Display
'Application.Wait (Now + TimeValue("0:00:03"))
'.Send
End With

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing




End Sub

 

[kpasa]

I'll private message you. You're almost there. And yes, you can delete the jpg after sending.