VBA WriteToOutlook with FullHTML - require late binding solution

[marc j]

Hello all and thanks in advance!

I have a need to convert the following code to a late binding format so I can distribute to non excel people.
I am looking to make this code work without the references
I have tried, but I get stuck with the inspector and the word part.

Thanks for the help!

Kind regards,
Marc

Option Explicit
Sub sendEmailUsingWordEditor()


'requires MicrosoftOutlook xx Library reference
'requires MicrosoftWord xx Library reference
'this creates an email with full html // an almost exact copy of a range on an excel sheet


'borrowed from https://www.youtube.com/watch?v=f8s-jY9y220
'Excel VBA Introduction Part 29.1 - Creating Outlook Emails - WiseOwlTutorials


Dim olApp As Outlook.Application
Dim olEmail As Outlook.MailItem
Dim olInsp As Outlook.Inspector
Dim wDoc As Word.Document


Dim strGreeting As String
Dim lastRow As Integer


'sets header
strGreeting = "Dear John," & vbNewLine


'sets email
Set olApp = New Outlook.Application
Set olEmail = olApp.CreateItem(olMailItem)


With olEmail
    .BodyFormat = olFormatRichText
    .Display
    
    .SentOnBehalfOfName = "department@company.com"
    
    .To = "john.doe@abccompany.com"
    .Subject = "Sending this cool email with colors and html"
    
    'this allows us to copy/paste the Excel data
    'this will copy range a1 to g lastrow into the email under the greeting
    Set olInsp = .GetInspector
    Set wDoc = olInsp.WordEditor
    
    wDoc.Range.InsertBefore strGreeting
    
    lastRow = ActiveSheet.Range("A999").End(xlUp).Row
    ActiveSheet.Range("A1:G" & lastRow + 5).Copy
    
    wDoc.Range(Len(strGreeting), Len(strGreeting)).Paste
    Application.CutCopyMode = False
        
End With
End Sub

 

[Domenic]

For late binding, try making the following changes in red...

Option Explicit
Sub sendEmailUsingWordEditor()


'requires MicrosoftOutlook xx Library reference
'requires MicrosoftWord xx Library reference
'this creates an email with full html // an almost exact copy of a range on an excel sheet


'borrowed from https://www.youtube.com/watch?v=f8s-jY9y220
'Excel VBA Introduction Part 29.1 - Creating Outlook Emails - WiseOwlTutorials


[COLOR=#ff0000]Dim olApp As Object 'Outlook.Application[/COLOR]
[COLOR=#ff0000]Dim olEmail As Object 'Outlook.MailItem[/COLOR]
[COLOR=#ff0000]Dim olInsp As Object 'Outlook.Inspector[/COLOR]
[COLOR=#ff0000]Dim wDoc As Object 'Word.Document[/COLOR]


Dim strGreeting As String
Dim lastRow As Integer


'sets header
strGreeting = "Dear John," & vbNewLine


'sets email
[COLOR=#ff0000]Set olApp = CreateObject("Outlook.Application")[/COLOR]
[COLOR=#ff0000]Set olEmail = olApp.CreateItem(0) '0 = olMailItem[/COLOR]


With olEmail
[COLOR=#ff0000]    .BodyFormat = 3 '3 = olFormatRichText[/COLOR]
    .Display
    
    .SentOnBehalfOfName = "department@company.com"
    
    .To = "john.doe@abccompany.com"
    .Subject = "Sending this cool email with colors and html"
    
    'this allows us to copy/paste the Excel data
    'this will copy range a1 to g lastrow into the email under the greeting
    Set olInsp = .GetInspector
    Set wDoc = olInsp.WordEditor
    
    wDoc.Range.InsertBefore strGreeting
    
    lastRow = ActiveSheet.Range("A999").End(xlUp).Row
    ActiveSheet.Range("A1:G" & lastRow + 5).Copy
    
    wDoc.Range(Len(strGreeting), Len(strGreeting)).Paste
    Application.CutCopyMode = False
        
End With
End Sub

Hope this helps!

 

[Macropod]

I have a need to convert the following code to a late binding format so I can distribute to non excel people.

So how are they going to run it without Excel?

 

[marc j]

Let me clarify, "so can distribute to people with limited knowledge with Excel, and with different versions and settings of Excel."
I will try to be more accurate in my wording.

Regards,
Marc

 

[marc j]

Thank you Domenic! Works like a charm.
We now have Excel email!

Kind regards,

Option ExplicitSub sendEmailUsingWordEditorLateBinding()


'this creates an email with full html // an almost exact copy of a range on an excel sheet


'borrowed from https://www.youtube.com/watch?v=f8s-jY9y220
'Excel VBA Introduction Part 29.1 - Creating Outlook Emails - WiseOwlTutorials


Dim olApp As Object 'Outlook.Application
Dim olEmail As Object 'Outlook.MailItem
Dim olInsp As Object 'Outlook.Inspector
Dim wDoc As Object 'Word.Document


Dim strGreeting As String
Dim lastRow As Integer


'sets heading
strGreeting = "Hi John," & vbNewLine & vbNewLine & "Here is the data you need!" & vbNewLine




Set olApp = CreateObject("Outlook.Application")
Set olEmail = olApp.CreateItem(0) '0 = olMailItem


With olEmail
    .BodyFormat = 3 'olFormatRichText
    .Display
    
    .SentOnBehalfOfName = "department@company.com"
    
    .To = "john.doe@abccompany.com"
    .Subject = "Sending this cool email with colors and html"
    
    
    'this allows us to copy/paste the Excel data
    'this will copy range A1 to D lastrow into the email under the greeting
    Set olInsp = .GetInspector
    Set wDoc = olInsp.WordEditor
    
    'I add a few lines before the copied data
    wDoc.Range.InsertBefore strGreeting & vbNewLine & vbNewLine
    
    'get the excel data
    lastRow = ActiveSheet.Range("A999").End(xlUp).Row
    ActiveSheet.Range("A1:D" & lastRow).Copy


    'I use the Length of the (strGreeting) -1 / it gave issues without -1
    wDoc.Range(Len(strGreeting) - 1, Len(strGreeting) - 1).Paste
   
    
    Application.CutCopyMode = False
     
End With
End Sub

 

[Macropod]

Let me clarify, "so can distribute to people with limited knowledge with Excel, and with different versions and settings of Excel."

That is no reason to use late binding; all you need do is compile the early-bound code on a system using the oldest version of Office you're supporting. Such code will also run more quickly than late-bound code.