Excel VBA to Outlook Screenshot picture resize.

Thank you this works perfectly. I am also creating an additional sheet where I have 3 additional tabs (3 different term quotations) which I need to screen capture into one sheet. Is this possible and is there a code I can use for this?

I don't understand the additional question.  You are using the terms "sheet" and "tab" as if they are something different, but in Excel these are typically the same concept.  You can certainly insert multiple images on a single sheet.

---

»bp

Hi Bill, Please accept my apologies if I have confused the matter slightly. When I said sheet I meant that I was creating another separate Excel document all together from the one above which you kindly helped on (though it has the same concept, screen capture to email). Except in this Excel document I will have 5 tabs (not 1) across the bottom. I would need all 5 tabs (a specified range in each which is different i.e. tab 1 = A2:N39, tab 2 = A4:N42 and so on). When I click the run command button for the VBA I would want all 5 tabs (specified screen capture areas) to be screen captured and pasted into 1 outlook email opened as a draft as above. Is this possible? I hope this explains it a little better.

Give this a try and see if it gets you started.  You will probably need to insert some text or something between the pictures (maybe just a carriage return) so that the images flow a little better.  Sample workbook attached...

Private Sub CommandButton1_Click()
    'Open a new mail item
    Dim outlookApp As Outlook.Application
    Set outlookApp = CreateObject("Outlook.Application")
    Dim outMail As Outlook.MailItem
    Set outMail = outlookApp.CreateItem(olMailItem)

    'Get its Word editor
    outMail.Display
    Dim wordDoc As Word.Document
    Set wordDoc = outMail.GetInspector.WordEditor
 
    ' Copy and paste ranges from desired sheets
    PastePic wordDoc, "Sheet1!A1:C3"
    PastePic wordDoc, "Sheet2!B5:E7"
    PastePic wordDoc, "Sheet3!A2:E8"
    PastePic wordDoc, "Sheet4!C6:F9"
    PastePic wordDoc, "Sheet5!A1:G10"
End Sub


Private Sub PastePic(wordDoc As Word.Document, rngRange As String)
    Dim r As Word.Range
    
    'Copy range of interest
    Range(rngRange).Copy

    'Paste as picture in sheet and cut immediately
    ActiveSheet.Pictures.Paste.Cut

    Set r = wordDoc.Content
    r.Collapse Direction:=wdCollapseEnd
    r.Paste
    
    i = wordDoc.InlineShapes.Count
    wordDoc.InlineShapes.Item(i).ScaleHeight = 100
    wordDoc.InlineShapes.Item(i).ScaleWidth = 100
End Sub

Select allOpen in new window

EE29048907.xlsm

---

»bp

Hi Bill, Thank you for your help. Unfortunately my VBA skills are very limited. I have only just started using it the last few days with no training. I have inputted the sub which you wrote and when I click the command button to run the sub the draft email opens up perfectly but no screen captures are included. When I click back on the Excel sheet I get the following message...

Microsoft Visual Basic

run-time error "1004"

Method 'range' of object'_worksheet' failed

I already have active x click buttons set up for single tab screen captures and I want to add the multiple tab screen capture onto sheet one. The tabs are named the following;

Tab 1 is called 1 Year Option (its range is A1:P39)
Tab 2 is called 2 Year Option (its range is A1:P39)
Tab 3 is called 3 Year Option (its range is A1:P39)
Tab 4 is called Support Options (its range is A1:A31)
Tab 5 is called System Requirements (its range is A1:J23)

Im not too sure what to do next. I have selected all the relevant references in the tools tab on VBA (I think) so not too sure what to do. Your help is really appreciated.

Thanks
Chris

Did you adjust these lines to reference your sheet names and ranges?

   PastePic wordDoc, "Sheet1!A1:C3"
    PastePic wordDoc, "Sheet2!B5:E7"
    PastePic wordDoc, "Sheet3!A2:E8"
    PastePic wordDoc, "Sheet4!C6:F9"
    PastePic wordDoc, "Sheet5!A1:G10"

---

»bp

YEs, I have amended them to show "Sheet (1 Year Option)!A1:C3" etc. I have included a word attachment screenshot of the whole script and the error flagging up.
Screenshot-of-Error.docx

In the parms, the sheet name needs to match the sheet name that shows in the tabs at the bottom of Excel.  Is "Sheet (1 Year Option)!A1:C3" really the name that shows there, or perhaps is it "1 Year Option!A1:C3"?  Or might you have spaces wrong?

---

»bp

Your right, the sheet names don't actually say Sheet 1 they just say 1 year option etc. Ill give that a try now and let you know in a minute.

Hi Bill, I have amended it and put in the correct names and cells (triple checking its all exact) and it opens up the email but does not paste anything. I have attached the actual Excel document which may help more.
VBA-Template.xlsm

Hi Bill, That is absolutely perfect, you have a real talent. Thank you so much for your help with this. I have been scouring the internet everywhere for help with this.

Phew, glad we made some progress there.

---

»bp

Yes you were fantastic, thank you very much

"Anyone looking for VB Scripting work contact Bill. He is absolutely fantastic and second to none on here".

Thank you again for your help Bill. I will recommend you to everyone I speak with on here.