Solved

Jpg image not visible - once macro has run

Posted on 2013-01-17
3
387 Views
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
    Else
      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)
      Else
         .HTMLBody = StrBody
      End If
      .Attachments.Add RangeToJPEG(rng)
      .Display
   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
   ts.Close

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
         .Paste
         .Export Filename:=SaveName, FilterName:="jpg"
         .Pictures(1).Delete
      End With
      Sourcebook.Activate
   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
   Else
      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
   CreateContainer.ChartArea.ClearContents
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
:
0
Comment
Question by:route217
  • 2
3 Comments
 

Author Comment

by:route217
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...
0
 
LVL 15

Expert Comment

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

Accepted Solution

by:
David L. Hansen earned 500 total points
ID: 38788549
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now