Pastespecial from excel to outlook using VBA

[kian_000]

Hi guys, I need some help in my vba code. I am trying to pastespecial a screenshot of my excel file into email. For some reason unknown, I always get an error message when I try to compile the code- compile error can't find project or library. Strange enough, when I change the code to paste instead of PasteSpecial DataType:=wdPasteMetafilePicture, the code works fine. Can I know the reason behind it? Also, how can i make the pastespecial work in this case?

Sub SendEmail()

Dim sh As Worksheet
Dim Scrn As Range
Dim objOutlookApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim objMailDocument As Word.Document
Dim dtToday As Date

dtToday = DateValue(Now())

Set sh = ThisWorkbook.Sheets("Summary")
Set Scrn = sh.Range("Screen")

'çopy screenshot
Scrn.Copy

Set objOutlookApp = CreateObject("Outlook.Application")
Set objMail = objOutlookApp.CreateItem(0)

'objMail.Display
With objMail
  .Subject = "subject " & dtToday
  .To = "myemail@mail.com"
  .Display
End With

Set objMailDocument = objMail.GetInspector.WordEditor
'Paste the copied screenshot
objMailDocument.Range(0, 0).PasteSpecial DataType:=wdPasteMetafilePicture


End Sub

 

[Dan_W]

Something else is happening, because the error message you provided would not be solved by the change in code you suggested. This particular compile error results from the fact that the project/workbook containing the above code does not have any reference set to either/both the Outlook library and/or the Word library. This can be set in Tools->References, and then make sure both the Microsoft Outlook Object Library and Microsoft Word Object Library are both selected:

Your code worked for me once I added the references. If what you're after is an image capture of the designated range, try the following code to see if that gives you a better result:

Sub SendEmail()

Dim sh As Worksheet
Dim Scrn As Range
Dim objOutlookApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim objMailDocument As Word.Document
Dim dtToday As Date

dtToday = DateValue(Now())

Set sh = ThisWorkbook.ActiveSheet
Set Scrn = sh.Range("ScreenR")

'çopy screenshot
Scrn.CopyPicture xlScreen, xlBitmap

Set objOutlookApp = New Outlook.Application
Set objMail = objOutlookApp.CreateItem(0)

'objMail.Display
With objMail
  .Subject = "subject " & dtToday
  .To = "myemail@mail.com"
  .Display
End With

Set objMailDocument = objMail.GetInspector.WordEditor
'Paste the copied screenshot
objMailDocument.Range(0, 0).Paste
End Sub

 

[kian_000]

Something else is happening, because the error message you provided would not be solved by the change in code you suggested. This particular compile error results from the fact that the project/workbook containing the above code does not have any reference set to either/both the Outlook library and/or the Word library. This can be set in Tools->References, and then make sure both the Microsoft Outlook Object Library and Microsoft Word Object Library are both selected:

View attachment 64153

Your code worked for me once I added the references. If what you're after is an image capture of the designated range, try the following code to see if that gives you a better result:

Sub SendEmail()

Dim sh As Worksheet
Dim Scrn As Range
Dim objOutlookApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim objMailDocument As Word.Document
Dim dtToday As Date

dtToday = DateValue(Now())

Set sh = ThisWorkbook.ActiveSheet
Set Scrn = sh.Range("ScreenR")

'çopy screenshot
Scrn.CopyPicture xlScreen, xlBitmap

Set objOutlookApp = New Outlook.Application
Set objMail = objOutlookApp.CreateItem(0)

'objMail.Display
With objMail
  .Subject = "subject " & dtToday
  .To = "myemail@mail.com"
  .Display
End With

Set objMailDocument = objMail.GetInspector.WordEditor
'Paste the copied screenshot
objMailDocument.Range(0, 0).Paste
End Sub

Hi Dan_W,

Thanks for replying. Really appreciate it. I actually did add the word reference 16.0 to my code. Referring to my screenshot as attached, I got flagged for a compile error at objMailDocument.Range(0, 0).PasteSpecial DataType:=wdPasteMetafilePicture. If i had not include the word reference 16.0 at the beginning, I would get flagged earlier at Dim objMailDocument As Word.Document. Strangely, since I cleared the the earlier line, why am I still getting flagged for using Pastespecial?

On your suggestion, your code works for me since it is using paste at the end. The problem I have with that is that the screenshot becomes too small for the readers. Hence I am wondering, is there a zoom/enlarge function to your method, so that the picture will be big enough for my readers, in the right scale, such that they do not need to double click on the email to read it properly.

Regards,
Gabzel

 

[Dan_W]

It's very odd - what other libraries do you have referenced? Also, is it VBA that has wdPasteMetafilePicture highlighted, or did you do that? It's very odd that it would highlight that given that it is only a constant. You can replace it with the number 3, and it will have the same effect.

As it transpires, you don't actually need a reference to Word at all. Try the following code and see if the image it produces is better. If it's the same, then it sounds as though what you need to do is make the image physically bigger, no?

Sub SendEmail()

    Dim sh As Worksheet
    Dim Scrn As Range
    Dim objOutlookApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Dim objMailDocument As Object
    Dim dtToday As Date
   
    dtToday = DateValue(Now())
   
    Set sh = ThisWorkbook.ActiveSheet
    Set Scrn = sh.Range("ScreenR")
   
    Scrn.Select
    Application.ActiveWindow.Zoom = True
   
    'çopy screenshot
    Scrn.CopyPicture xlScreen, xlBitmap
   
    Set objOutlookApp = New Outlook.Application
    Set objMail = objOutlookApp.CreateItem(0)
   
    'objMail.Display
    With objMail
        .Subject = "subject " & dtToday
        .To = "myemail@mail.com"
        .Display
    End With
   
    Set objMailDocument = objMail.GetInspector.WordEditor
    'Paste the copied screenshot
    objMailDocument.Range(0, 0).Paste

End Sub

 

[kian_000]

You can refer to the attached pic for the list of reference that I have on. It is VBA that highlight

 wdPasteMetafilePicture

. Very odd. I want to paste as picture (enhanced metafile) instead as it look bigger.
Didn't know I can use numbers to replicate datatypes choices. Anyhow, based on the above, if I want Picture (enhanced Metafile), i should be using

datatype:=5.

? However, now I get an error message. "Run-time error '5342'. The specified data is unavailable.". Strange enough, if i use

datatype:=3

or

datatype:=2

, the code is executable.

 

[kian_000]

I think I found a workaround solution to my problem. Using your suggestion, I paste as bitmap then resize it to the way I like it. Code is finally working for me now. Thanks for your help. Man, I need more practice.

Sub SendEmail()

Dim sh As Worksheet
Dim Scrn As Range
Dim objOutlookApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim objMailDocument As Word.Document
Dim dtToday As Date

dtToday = DateValue(Now())

Set sh = ThisWorkbook.Sheets("Summary")
Set Scrn = sh.Range("Screen")
'Application.ActiveWindow.Zoom = False


'çopy screenshot
Scrn.CopyPicture xlScreen, xlBitmap


Set objOutlookApp = CreateObject("Outlook.Application")
Set objMail = objOutlookApp.CreateItem(0)

'objMail.Display
With objMail
  .Subject = "myfilename " & dtToday
  .To = "myemail@outlook.com"
  .Display
End With

Set objMailDocument = objMail.GetInspector.WordEditor

'Paste the copied screenshot
objMailDocument.Range(0, 0).Paste

'resize screenshot
With objMailDocument
  .InlineShapes(1).Height = 1000
  .InlineShapes(1).Width = 1000
End With


End Sub