Code to create email with screenshot below email body

[mik1996]

Hi,

I have created a code in excel to create an email with a link and paste a screenshot within the email but im having trouble pasting the screenshot below the email body (ideally i would like it a line below the hyperlink, probably a stupid question but is there a way to ammend the code so the screenshot is pasted below the hyperlink?

Thank you in advance, please see code below:

Sub Compose_Email_TEST()

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

Dim Wb1 As Workbook
Set Wb1 = ThisWorkbook
 

'back to the first sheet
ThisWorkbook.Sheets(1).Select
    
'get the name shown in the upper right hand corner of Excel to use as the signature
Dim OwnerName As String
OwnerName = Application.UserName

'get the workbook name
Dim WorkbookName As String
WorkbookName = Wb1.Name

'get the location where the spreadsheet is saved
Dim FileLoc As String
FileLoc = Wb1.FullName


'time to start Mircosoft Outlook if it hasn't already been started
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String

    On Error Resume Next
    
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
   
'for non-html email
'xMailBody = "Team, " & vbNewLine & vbNewLine & "Please check and sign this DCN:" & vbNewLine & "Thanks," & vbNewLine & OwnerName
    
    
'for html email. This is the body of the email
xMailBody = "Hi All, <br><br>" & "BS1495 Funding is ready for auth:" & "<br><br>" & _
"<a href=" & Chr(34) & FileLoc & Chr(34) & " > " & WorkbookName & " </a> " _
& "<br><br>" & "Thanks," & "<br><br>" & OwnerName
  
'fill in each section of the newly created email message

    On Error Resume Next
    With xOutMail
        .To = "controlevidence.finops@landg.com"
        .CC = ""
        .BCC = ""
        .Subject = WorkbookName
        '.Body = xMailBody
        .HTMLBody = xMailBody
        .Display   'or use .Send
        
    Application.SendKeys "(^v)"
    
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing

  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[Micron]

You mean that code does paste in an image - it just doesn't go where you want it to? Doesn't paste anything in for me (I'm presuming you mean to paste from clipboard with that SendKeys line). Should work if you use the html image tags and a path to the picture instead of trying to paste an image from the clipboard. Everything I found about embedding pics in email body had to do with creating a pic from a range.

 

[mik1996]

You mean that code does paste in an image - it just doesn't go where you want it to? Doesn't paste anything in for me (I'm presuming you mean to paste from clipboard with that SendKeys line). Should work if you use the html image tags and a path to the picture instead of trying to paste an image from the clipboard. Everything I found about embedding pics in email body had to do with creating a pic from a range.

Thank you, yes that is exactly what I mean. It’s basically for work as a team we probably send over 100 emails a day with a link and a screenshot so if I could fix this it would be a massive help! Would using html image tags and path involve saving the image? Sorry I’m new to this
Becuase atm we don’t save any of our screenshots so I was hoping there was a way that it could be done without needing for it to be saved which is why I just used paste from the clipboard in the current coding

 

[Micron]

If the image is part of a spreadsheet then how to paste a range as an image is covered elsewhere, but you haven't said what this pic is. At first I assumed it was an actual picture on the clipboard. Now I'm thinking it's not...

we don’t save any of our screenshots

Yes image tag would need to reference a file, but now that I think of it, the pic needs to be embedded in the email. Simply referencing it would cause the recipient to not get the image because the path would not be valid.

 

[mik1996]

If the image is part of a spreadsheet then how to paste a range as an image is covered elsewhere, but you haven't said what this pic is. At first I assumed it was an actual picture on the clipboard. Now I'm thinking it's not...

Yes image tag would need to reference a file, but now that I think of it, the pic needs to be embedded in the email. Simply referencing it would cause the recipient to not get the image because the path would not be valid.

Thanks sorry for not explaining properly the image is a screenshot (using the snip tool) of a database that is pasted into the excel sheet and then pasted again into the body of the email if that makes sense

 

[Micron]

Then I guess it's an actual image on the clipboard. That I have no concrete idea about, except to say that if you choose Paste from the email window there is an option to paste as bitmap and the paste goes into the body.

 

[mik1996]

Thanks for all your help, would I be able write the paste as bitmap into the code by any chance?

 

[Micron]

Suggest you research DoCmd.RunCommand, which is often used to invoke Ribbon commands.
I got as far asDoCmd.RunCommand acCmdPasteSpecial but don't know how to make use of it.