Visual Basic - Screen shot then insert into email

Question

Visual Basic - Screen shot then insert into email

5.3k views Asked by Speckled_Hen At 10 June 2015 at 13:09

I am new to using Visual Basic. I am using Visual Basic in MS Excel. I am struggling to work out have to paste the screen shot into the email. I have the following, could you please tell what I need to enter.

Thanks

    Sub AddToNaughtyList()
 '
    ' AddToNaughtyList Macro
    '
    Range("Y3:Z3").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("U2:V2").Select
    Selection.Copy
    Range("Y3:Z3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E2").Select

    Dim aOutlook As Object
    Dim aEmail As Object
    Dim StudentName As String
    Dim SendAddress As String

 ' copy picture

    Range("C6:S39").Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

'setup email

    Set aOutlook = CreateObject("Outlook.Application")
    Set aEmail = aOutlook.CreateItem(0)
    StudentName = ActiveCell.Value
    SendAddress = Range("D5")


'Cells(1, "A").Value

'Set Address

    aEmail.To = SendAddress

'Set Subject

    aEmail.Subject = "Weekly Progress Report for " & StudentName

'Set Body for mail

    aEmail.HTMLBody = "<font face=calibri><html><body>Nat's *Student needs to attend Friday Detention* text goes here.</font><br /><br />" _

'paste into email???????????

aEmail.Display

Original Q&A

There are 1 answers

**Eugene Astafiev** · Answer 1 · 2015-06-10T13:45:54+00:00

I see the two possible solutions:

Outlook uses Word for rendering/editing message bodies. So, you can use the Word object model for pasting images from the clipboard. The WordEditor property of the Inspector class returns an instance of the Document class from the Word object model which represents the message body. The Paste or PasteSpecial methods of the Selection class insert the contents of the Clipboard. See Chapter 17: Working with Item Bodies for more information.
Save the screenshot as a file on the disk and then attach it to the mail item as an attachment. The Add method of the Attachments class creates a new attachment in the Attachments collection. Then you can mention it in the message body. Note, you need to assign cid attribute to the attached image to add a reference in the body.

For example:

  Attachment attachment = newMail.Attachments.Add(@"E:\Pictures\image001.jpg", OlAttachmentType.olEmbeddeditem, null, "Some image display name");

  string imageCid = "image001.jpg@123";

  attachment.PropertyAccessor.SetProperty("http://schemas.microsoft.com/mapi/proptag/0x3712001E", imageCid);

 newMail.HTMLBody = String.Format("<body><img src=\"cid:{0}\"></body>", imageCid);

TechQA.

Visual Basic - Screen shot then insert into email

There are 1 answers

Related Questions in EXCEL

Related Questions in VBA

Related Questions in OUTLOOK

Related Questions in SCREENSHOT

Popular Questions

Popular Tags

Trending Questions