Call createJpg (reference sheet)

Question

The below code runs a macro and creates the jpeg. How can I change the name of the sheet (ex:"Strategic") for an active sheet. When I copy a sheet the code don't work anymore, because of the reference name.

With OutMail
        .SentOnBehalfOfName = "Me@Me.Com"
        .Display
        .Subject = "Strategic Sales"
        .To = "Me@Me.Com"
> Call createJpg("Strategic", "A1:F11", "Quota") '

For those that don't recognize this immediately, can we have the language that this code was written in? — Lilith Daemon, Apr 13 '16 at 14:33
http://stackoverflow.com/questions/27042842/excel-2010-paste-range-and-picture-into-outlook — Rodrigo Paixão, Apr 13 '16 at 17:27
Is excel VBA, to create an outlook mail object and embed images. I put the entire code next. http://stackoverflow.com/questions/36600953/call-createjpg-reference-sheet/36605670#36605670 — Rodrigo Paixão, Apr 14 '16 at 11:19

score 0 · Answer 1 · answered Apr 13 '16 at 17:36

It's about a code to create an outlook mail objectand embed images

Sub sendMail()
        Application.Calculation = xlManual
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        Dim TempFilePath As String

        'Create a new Microsoft Outlook session
        Set appOutlook = CreateObject("outlook.application")
        'create a new message
        Set Message = appOutlook.CreateItem(olMailItem)


        With Message
            .Subject = "My mail auto Object"

            .HTMLBody = "<span LANG=EN>" _
                & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
                & "Hello,<br ><br >The weekly dashboard is available " _
                & "<br>Find below an overview :<BR>"

            'first we create the image as a JPG file
            **Call createJpg("Dashboard", "B8:H9", "DashboardFile")**
            'we attached the embedded image with a Position at 0 (makes the attachment hidden)
            TempFilePath = Environ$("temp") & "\"
            .Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue, 0

            'Then we add an html <img src=''> link to this image
            'Note than you can customize width and height - not mandatory

            .HTMLBody = .HTMLBody & "<br><B>WEEKLY REPPORT:</B><br>" _
                & "<img src='cid:DashboardFile.jpg'" & "width='814' height='33'><br>" _
                & "<br>Best Regards,<br>Ed</font></span>"

            .To = "contact1@email.com; contact2@email.com"
            .Cc = "contact3@email.com"

            .Display
            '.Send
        End With

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        Application.Calculation = xlCalculationAutomatic
    End Sub

You need to create createJpg function which transform a range into a jpg file.


Sub createJpg(Namesheet As String, nameRange As String, nameFile As String)
    ThisWorkbook.Activate
    Worksheets(Namesheet).Activate
    Set Plage = ThisWorkbook.Worksheets(Namesheet).Range(nameRange)
    Plage.CopyPicture
    With ThisWorkbook.Worksheets(Namesheet).ChartObjects.Add(Plage.Left,

Plage.Top, Plage.Width, Plage.Height) .Activate .Chart.Paste .Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG" End With Worksheets(Namesheet).ChartObjects(Worksheets(Namesheet).ChartObjects.Count).Delete Set Plage = Nothing End Sub

Call createJpg (reference sheet)

1 Answers1