Solved

Edit macro to refer to range of data when copying to outlook as opposed to an attachment

Posted on 2013-01-17
2
424 Views
Last Modified: 2013-01-21
Hi Experts

The following vba code attaches a jpg file to the out going email when run from ms excel..the out going email attachment is not a chart but a range of data in a excel worksheet...

What I would like to macro to do is:-
1. Copy the range of data as per sub routine  mail_Daily
2. Paste the result as a jpg, png or gif file - what ever the best option into the out going email body
3. Send email....


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 Comments
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
Comment Utility
the following bit of code uses the MailEnvelope in Excel.
It is very simple and should be adaptable for your needs...

Sub Send_Range()
   
   ' Select the range of cells on the active worksheet.
   ActiveSheet.Range("A1:B5").Select
   
   ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True
   
   ' Set the optional introduction field thats adds
   ' some header text to the email body. It also sets
   ' the To and Subject lines. Finally the message
   ' is sent.
   With ActiveSheet.MailEnvelope
      .Introduction = "This is a sample worksheet."
      .Item.To = "E-Mail_Address_Here"
      .Item.Subject = "My subject"
      .Item.Send
   End With
End Sub

Open in new window

0
 

Author Comment

by:route217
Comment Utility
Many thanks barman much appreciated
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

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…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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

7 Experts available now in Live!

Get 1:1 Help Now