How to Change my code to late binding?

leostamato

New Member
Joined
Nov 7, 2018
Messages
2
Code:
Sub CommentsEmail()


Dim template As Workbook
Dim dashboard As Worksheet
Dim comments As Worksheet
Dim OutApp As Object
Dim OutMail As Object
Dim olApp As Outlook.Application
Dim mymail As Outlook.mailItem
Dim objSel As Word.Selection
Dim commentsrange As Range
Dim branch As String
Dim Sendto As String


UpdateScreen = False


Shell ("Outlook")


Set olApp = New Outlook.Application
Set mymail = olApp.CreateItem(olMailItem)
Set template = ActiveWorkbook
Set dashboard = template.Worksheets("Dashboard")
Set comments = template.Worksheets("Comments")
branch = dashboard.Cells(1, 25)
Sendto = comments.Cells(2, 10)
Set commentsrange = comments.Range(Cells(7, 1), Cells(52, 4))


template.Activate




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


'OutMail.Display
Dim wordDoc As Word.Document
Set wordDoc = OutMail.GetInspector.WordEditor


Set objSel = wordDoc.Windows(1).Selection


        'construct the body of the email here
        With objSel


            'first text
            .InsertAfter "Dear All," & vbCrLf
            .Move wdParagraph, 1


            'second text
            .InsertAfter vbCrLf & "See below the Comments for Flash Indicator - " & branch & vbCrLf & vbCrLf
            .Move wdParagraph, 1


            'copy a range and paste a picture
            commentsrange.Copy ''again, you need to modify your target range
            .PasteAndFormat wdChartPicture
            .Move wdParagraph, 1


            .InsertAfter vbCrLf & "Let us know of any questions." & vbCrLf & vbCrLf
            .Move wdParagraph, 1


            .InsertAfter vbCrLf & "Kind Regards," & vbCrLf
        End With
        
        OutMail.To = OutMail.To & ";" & Sendto


        With OutMail
    
         .Subject = "Comments on Flash Indicator Results - " & branch
         .Attachments.Add (ActiveWorkbook.FullName)
         .Display
        End With
    
On Error GoTo 0
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    


        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        Exit Sub
    
End Sub
 
Last edited by a moderator:

leostamato

New Member
Joined
Nov 7, 2018
Messages
2
is there anyone that can help me on this issue? I have stopped here:

Code:
[COLOR=#242729][FONT=Consolas]Sub CommentsEmail2()[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">
Dim template As Workbook
Dim dashboard As Worksheet
Dim comments As Worksheet
Dim OlaApp As Object
Dim OleMail As Object

Dim TempFilePath As String
Dim xHTMLBody As String

Dim commentsrange As Range
Dim branch As String
Dim Sendto As String

UpdateScreen = False

Set template = ActiveWorkbook
Set dashboard = template.Worksheets("Dashboard")
Set comments = template.Worksheets("Comments")
Set commentsrange = comments.Range(Cells(7, 1), Cells(52, 4))

branch = dashboard.Cells(1, 25)
Sendto = comments.Cells(2, 10)

template.Activate

 On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then Set olApp = CreateObject("Outlook.Application")
    On Error GoTo 0
    Set OleMail = olApp.CreateItem(0)

Call createJpg(ActiveSheet.comments, commentsrange, "DashboardFile")
        TempFilePath = Environ$("temp") & "\"
        xHTMLBody = "<span LANG=EN>" _
                & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
                & "Hello, this is the data range that you want:<br> " _
                & "<br>" _
                & "<img src='cid:DashboardFile.jpg'>" _
                & "<br>Best Regards!</font></span>"
        With OleMail
            .Subject = "test"
            .HTMLBody = xHTMLBody
          .Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue
          .Attachments.Add (ActiveWorkbook.FullName)
            .To = " "
            .Cc = " "
            .Display
        End With




        Set OleMail = Nothing
        Set OlaApp = Nothing


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

End Sub

Sub createJpg(SheetName As String, commentsrange As String, nameFile As String)
    Dim xRgPic As Range
    ThisWorkbook.Activate
    Worksheets(comments).Activate
    Set xRgPic = ThisWorkbook.Worksheets(comments).Range(Cells(7, 1), Cells(52, 4))
    xRgPic.CopyPicture
    With ThisWorkbook.Worksheets(comments).ChartObjects.Add(xRgPic.Left, xRgPic.Top, xRgPic.Width, xRgPic.Height)
        .Activate
        .Chart.Paste
        .Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
    End With
    Worksheets(comments).ChartObjects(Worksheets(comments).ChartObjects.Count).Delete
Set xRgPic = Nothing </code>[COLOR=#242729][FONT=Consolas]End Sub
[/FONT][/COLOR]
 

Forum statistics

Threads
1,082,126
Messages
5,363,321
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top