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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

685 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