Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Jpg image not visible - once macro has run

Posted on 2013-01-17
Last Modified: 2013-01-17
Hi Experts

The following macro was excellently provided to me by one of the experts on this site.
However, once the macro has run the attachment file .jpg is blank and also I wish to edit the macro so the data is in the body of the email as opposed to an attachment.

Option explicit

Dim container                 As Chart
Dim containerbook             As Workbook
Dim Sourcebook                As Workbook

Sub Mail_Daily()

   Dim rng                    As Range
   Dim Outapp                 As Object
   Dim OutMail                As Object
   Dim StrBody                As String
   Dim StrBodySig             As String
   Dim strpath                As String
   Dim Test                   As String
   Dim sPath                  As String
   Dim Folder                 As Object
   Dim oFSO                   As Object
   Dim strExtension           As String
   Dim SignaturePath          As String
   Dim SignaturePathFound     As String

   Application.DisplayAlerts = False
   Application.ScreenUpdating = False

   Set rng = Nothing
   On Error Resume Next

   Set rng = Selection.SpecialCells(xlCellTypeVisible)

   Set rng = Sheets("Blackberry").Range("B4:H30").SpecialCells(xlCellTypeVisible)
   On Error GoTo 0

   If rng Is Nothing Then
      MsgBox "The selection is not a range or the sheet is protected" & _
             vbNewLine & "please correct and try again.", vbOKOnly
      Exit Sub
   End If

   With Application
      .EnableEvents = False
      .ScreenUpdating = False
   End With

   Set Outapp = CreateObject("Outlook.Application")
   Set OutMail = Outapp.CreateItem(0)

   StrBody = "<p style='font-family:calibri;font-size:12'>" & "Dear All" & "<br><br>" & _
             "Please find attached the Daily R&WO Performance Dashboard for - " & Format(Now, "dd mmmm yyyy") & "." & "<br>" & _
             "Please find below an image developed for users viewing this email on a Blackberry device who are unable to open pdf attachments this image" & "<br>" & _
             "shows the key metrics for each of the functional teams, highlighting Current Rolling Week and MTD figures and associated RAG statuses." & "<br>" & _
             "<br>If you have any difficulties viewing the table above via a Blackberry device, please contact sender:- Details Below." & "<p style>"

   strpath = CreateObject("WScript.Shell").specialfolders("AppData") & "\Microsoft\Signatures\"

   ' these go on every line and give the main information about the file

   strExtension = Dir(strpath & "\*.htm")
   SignaturePathFound = "No"
   If strExtension = "" Then
      MsgBox ("You dont have an outlook signature set up - please update")
        Exit Sub
      SignaturePath = strpath & strExtension
    SignaturePathFound = "Yes"
   End If

   On Error Resume Next
   With OutMail
      .To = ""
      .CC = ""
      .Subject = "Executive Summary Report - " & Format(Now, "dd mmmm yyyy") & ""

      If SignaturePathFound = "Yes" Then
         .HTMLBody = StrBody & GetBoiler(SignaturePath)
         .HTMLBody = StrBody
      End If
      .Attachments.Add RangeToJPEG(rng)
   End With
   On Error GoTo 0

   With Application
      .EnableEvents = True
      .ScreenUpdating = True
   End With

   Set OutMail = Nothing
   Set Outapp = Nothing

End Sub
Function GetBoiler(ByVal sFile As String) As String

   Dim fso                    As Object
   Dim ts                     As Object
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
   GetBoiler = ts.ReadAll

End Function
Function RangeToJPEG(rng As Range) As String
   Dim varReturn              As Variant
   Dim MyAddress              As String
   Dim SaveName               As Variant
   Dim MySuggest              As String
   Dim Hi                     As Integer
   Dim Wi                     As Integer

   Set Sourcebook = rng.Parent.Parent
   Set containerbook = Workbooks.Add(1)
   containerbook.Sheets(1).Name = "JPGcontainer"
   MySuggest = sShortname(rng.Worksheet.Name)
   Set container = CreateContainer(containerbook)
   MyAddress = rng.Address
   If MyAddress <> "A1" Then
      SaveName = CreateObject("WScript.Shell").specialfolders("MyDocuments") & "\temp.jpg"
      If Dir(SaveName) <> "" Then Kill SaveName
      With rng
         .CopyPicture Appearance:=xlScreen, Format:=xlBitmap
         Hi = .Height + 4  'adjustment for gridlines
         Wi = .Width + 6   'adjustment for gridlines
      End With
      MakeAndSizeChart container, ih:=Hi, iv:=Wi
      With container
         .Export Filename:=SaveName, FilterName:="jpg"
      End With
   End If
   RangeToJPEG = SaveName
   On Error Resume Next
   Application.StatusBar = False
   containerbook.Close False
End Function
Function sShortname(ByVal Original As String) As String
   Dim i                      As Integer
   sShortname = ""
   i = InStr(Trim$(Original), " ")
   If i = 0 Then
      sShortname = Original
      sShortname = Left$(Original, i - 1)
   End If
End Function

Private Function CreateContainer(ByRef wbk As Workbook) As Chart
   Set container = wbk.Charts.Add
   With container
      .ChartType = xlColumnClustered
      .SetSourceData Source:=wbk.Worksheets(1).Range("A1")
      .Location Where:=xlLocationAsObject, Name:=wbk.Sheets(2).Name
   End With
   Set CreateContainer = ActiveChart
End Function

Sub MakeAndSizeChart(ByRef cht As Chart, ih As Integer, iv As Integer)
   Dim Hincrease              As Single
   Dim Vincrease              As Single
   Hincrease = ih / cht.ChartArea.Height
   cht.Parent.ShapeRange.ScaleHeight Hincrease, _
                                     msoFalse, msoScaleFromTopLeft
   Vincrease = iv / cht.ChartArea.Width
   cht.Parent.ShapeRange.ScaleWidth Vincrease, _
                                    msoFalse, msoScaleFromTopLeft
End Sub
Question by:route217
  • 2

Author Comment

ID: 38787606
The problem here is that the jpg container is a empty/blank worksheet..

Also this macro is trying to add a chart on the specified range - where as I have a data on the range in the form of a table...

The data range is in the first sub procedure...
LVL 15

Expert Comment

by:David L. Hansen
ID: 38788537
Why not just take a screen shot and paste it into the email?
LVL 15

Accepted Solution

David L. Hansen earned 500 total points
ID: 38788549

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question