Paste a range of cells into Email Body AS AN IMAGE - VBA

[matthec2]

Hi all

I am using the below code to send a PDF to a group of managers.

I want to include in the email body, a summary table from one of the tabs.

I want paste as an IMAGE as this keeps all of the formatting and size ratio that makes it easy to read.

The source tab is called DATA and the range of cells I wan to copy and paste is called AA_PASTE_COPY.

Could you tell me where I need to add this in the below code.

Many thanks!

Sub BayEmailPDF()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("A1")
 
  ' Define PDF filename
  PdfFile = ""
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & Range("PDFname").Value & ".pdf"
 
  ' Export activesheet as PDF
  Sheets(Array("Bay", "Coast & Country")).Select
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
    Sheets("LIST").Select
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = Range("EmailSubject").Value
    .To = Range("EmailAddressTo").Value ' <-- Put email of the recipient here
    .CC = "" ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "The report is attached in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
End Sub

 

[crazydragon84]

1. can it be an attachment or does it have to be in body
2. how are you copying the image? is it saved somewhere?
3. Why are you creating a PDF File or is that something you need separately?

 

[matthec2]

Hi Crazy Dragon

1. I want it to be in the body of the email. It is just a simple 5 line table summary that will give top level performance, rather than the user having to open the attached PDF.

2. I just want to copy a range of cells in the spreadsheet - I have found pasting as an image into the body will keep the size and format.

3. The PDF gets attached to the email as part of the code. This is 10 page detailed summary of week performance.

THANKS!

 

[John_w]

Try adapting the code at https://www.rondebruin.nl/win/s1/outlook/bmail3.htm