Dim as picture | Type mismatch | VBA

[YourBroLucas]

Estimated forum members,

I've created the following macro to generate an outlook email and turn ranges into pictures before pasting them in the mail's body.

The thing is that from time to time (approx. 1 out of 5 attempts), it results in an error message 'Error 13, Type mismatch'.

If said error appeared each time, I'd understand. But a macro working and then not working under the exact same circumstances is beyond me.

The error always occurs here:

 Set pic2 = shGraph.Pictures.Paste

Please note that I'm using the Outlook and Word 16.0 libraries.

May your knowledge enlighten me

Thank you !

Sub Distribution2()

'[B] Creates an Outlook email with listed recipients
'[C] Fills the email with the correct screenshots & formats

'Excel
Dim wb As Workbook
Dim shSpe As Worksheet
Dim shGraph As Worksheet
Dim shGroup As Worksheet
Dim CopyRange As Range
Dim GraphRange As Range
Dim week As Integer

Set wb = ActiveWorkbook
Set shSpe = wb.Worksheets("YTD SPECIAL")
Set shGraph = wb.Worksheets("YTD Graph")
Set shGroup = wb.Worksheets("YTD GROUPE")
Set CopyRange = shSpe.Range("C6:J30")
Set GraphRange = shGraph.Range("J22:R41")
week = shSpe.Range("D3")

'Outlook
Dim appOutlook As Object
Dim myMail As Outlook.MailItem
Dim docWord

Set appOutlook = CreateObject("Outlook.Application")
Set myMail = appOutlook.CreateItem(olMailItem)
'--------------------------------------------------------
'[A] Updates graphs
'Done automatically on sheet
'--------------------------------------------------------
'[B] Convert ranges to pictures & cut

'Generate email
With myMail
    .To = "ceo@company.com; manager@company.com"
    .Subject = "Report Week W" & week & " COMPANY NAME"
    .CC = "recipient1@company.com; recipient2@company.com; recipient3@company.com; recipient4@company.com"
    .BCC = ""
    .Display

Dim pic As Picture
shSpe.Activate
CopyRange.Copy
Set pic = shSpe.Pictures.Paste
pic.Cut

'Put pictures into email's body and align
    Set docWord = myMail.GetInspector.WordEditor
    With docWord.Range
        .PasteAndFormat wdChartPicture
        .InsertParagraphAfter
        .InsertParagraphAfter
    End With

Dim pic2 As Picture
shGraph.Activate
GraphRange.Copy
Set pic2 = shGraph.Pictures.Paste
pic2.Cut

'Put picture into email's body and align
    Set docWord = myMail.GetInspector.WordEditor
    With docWord.Range
        .PasteAndFormat wdChartPicture
        .InsertParagraphAfter
        .InsertParagraphAfter
    End With

    docWord.Application.Selection.Paragraphs.Alignment = _
    wdAlignParagraphCenter
    shGroup.Activate

End With

End Sub

 

[Domenic]

First, there's no need to activate your sheet before you copy, paste, and cut. This will slow things down, and could be part of the problem. If removing them doesn't help, try adding DoEvents and pausing the macro for a few seconds after each copy, paste, and cut. Actually, you can easily do both by adding the following code to your module, and calling PauseMacro when needed...

Sub PauseMacro(ByVal secs As Long)

    Dim endTime As Single
    endTime = Timer + secs
    
    Do
        DoEvents
    Loop Until Timer > endTime

So, for example, let's say we want to pause 3 seconds, you could do something like this...

GraphRange.Copy

PauseMacro 3 'seconds

Set pic2 = shGraph.Pictures.Paste

PauseMacro 3 'seconds

pic2.Cut

PauseMacro 3 'seconds

Actually, you might find that a 1 second delay might suffice.

Hope this helps!