Solved: Paste to Email - how to resize - Qlik Community

Report Inappropriate Content · ‎2014-02-19

Hi All,

I have got a macro that will take a screen shot from an app and paste it into the body of an Email.

The screen shot that is displayed in the email is really small, and cannot be read.

How can I resize the image?

This is my code:

'Approved Email

Sub SendEmail()
    Set objOL = CreateObject("Outlook.Application")
    Set objMsg = objOL.CreateItem(olMailItem)

   MsgBox "Creating Email"
    objMsg.subject = "Trade Position - Approved"


    Set myInspector = objMsg.GetInspector
    Set myDoc = myInspector.WordEditor
    myInspector.WordEditor.Content.InsertBefore chr(13)
    ActiveDocument.ActiveSheet.CopyBitmapToClipboard TRUE ' Scrren Shot
    ' myDoc.Range.Paste
    myInspector.WordEditor.Content.InsertAfter chr(13)
    myInspector.WordEditor.Range().Paste()
    myInspector.WordEditor.Content.InsertBefore chr(13)
    myInspector.WordEditor.Content.InsertBefore chr(13)
    myInspector.WordEditor.Content.InsertBefore "Approved as below:"

    objMsg.to = "name.name@here.com"

   'objMsg.to.InputBox("Enter email address in name.surname@here.com format","Email Address")
    objMsg.Display 'Show Email
    ' ActiveDocument.GetApplication.WaitForIdle

    ' MsgBox "Send Email"
' objMsg.Send 'Send Email

MsgBox "Email Sent"

End sub

Thank you

Report Inappropriate Content · ‎2014-02-19

Sorted it.

I got rid of all the Copy/Paste parts and added this:

ActiveDocument.ActiveSheet.ExportBitmapToFile "U:\Approval.PNG"

objMsg.Attachments.Add "U:\Approval.PNG"
objMsg.HTMLBody = "<html>Approved as below:" & _
"<img src='cid:Approval.PNG' height=1000 width=1500>"

My Code is now this:

'Approved Email

Sub SendEmail()
    Set objOL = CreateObject("Outlook.Application")
    Set objMsg = objOL.CreateItem(olMailItem)

   MsgBox "Creating Email"
    objMsg.subject = "Trade Position - Approved"


    Set myInspector = objMsg.GetInspector
    Set myDoc = myInspector.WordEditor
    myInspector.WordEditor.Content.InsertBefore chr(13)
    ActiveDocument.ActiveSheet.ExportBitmapToFile "U:\Approval.PNG"

msgbox "Screen capture is stored in U:\Approval.PNG."

 myInspector.WordEditor.Content.InsertAfter chr(13)

 'Take the PNG and paste into the body of the email
 objMsg.Attachments.Add "U:\Approval.PNG"
 objMsg.HTMLBody = "<html>Approved as below:" & _
 "<img src='cid:Approval.PNG' height=1000 width=1500>"

    objMsg.to = "name.name@here.com"

    'objMsg.to.InputBox("Enter email address in name.surname@here.com format","Email Address")
    objMsg.Display 'Show Email
    ' ActiveDocument.GetApplication.WaitForIdle

    ' MsgBox "Send Email"
' objMsg.Send 'Send Email

MsgBox "Email Sent"

Well done me

End sub

View solution in original post

marcus_sommer · ‎2014-02-19

For this you need to resize within your outlook - look for examples like this: How to resize all/multiple images in Word and adapted it for vbscript. Perhaps there are alternatively possibilities like using a link within the body or other paste-variants: https://www.google.de/search?q=vba+outlook+paste+picture&ie=utf-8&oe=utf-8&rls=org.mozilla:de:offici... You could also put the picture as attachment.

- Marcus

Report Inappropriate Content · ‎2014-02-19

Sorted it.

I got rid of all the Copy/Paste parts and added this:

ActiveDocument.ActiveSheet.ExportBitmapToFile "U:\Approval.PNG"

objMsg.Attachments.Add "U:\Approval.PNG"
objMsg.HTMLBody = "<html>Approved as below:" & _
"<img src='cid:Approval.PNG' height=1000 width=1500>"

My Code is now this:

'Approved Email

Sub SendEmail()
    Set objOL = CreateObject("Outlook.Application")
    Set objMsg = objOL.CreateItem(olMailItem)

   MsgBox "Creating Email"
    objMsg.subject = "Trade Position - Approved"


    Set myInspector = objMsg.GetInspector
    Set myDoc = myInspector.WordEditor
    myInspector.WordEditor.Content.InsertBefore chr(13)
    ActiveDocument.ActiveSheet.ExportBitmapToFile "U:\Approval.PNG"

msgbox "Screen capture is stored in U:\Approval.PNG."

 myInspector.WordEditor.Content.InsertAfter chr(13)

 'Take the PNG and paste into the body of the email
 objMsg.Attachments.Add "U:\Approval.PNG"
 objMsg.HTMLBody = "<html>Approved as below:" & _
 "<img src='cid:Approval.PNG' height=1000 width=1500>"

    objMsg.to = "name.name@here.com"

    'objMsg.to.InputBox("Enter email address in name.surname@here.com format","Email Address")
    objMsg.Display 'Show Email
    ' ActiveDocument.GetApplication.WaitForIdle

    ' MsgBox "Send Email"
' objMsg.Send 'Send Email

MsgBox "Email Sent"

Well done me

End sub

Report Inappropriate Content · ‎2014-02-20

Thanks for the additional info Marcus.

My solution works well and the customer is very happy with the result.