Create Email from Excel using Outlook Word Editor

glennon261

New Member
Joined
Sep 21, 2021
Messages
3
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I have been killing brain cells with this issue. I copied the routine from MR Excel video. My issue is when Set oWrdRng = oWrdDoc.Paragraphs.Add is executed I am returned an Error 13 Type Mismatch.

Sub sendemail()

'Declare Outlook Variables
Dim oLookApp As Outlook.Application
Dim oLookItm As Outlook.MailItem
Dim oLookIns As Outlook.Inspector

'Declare Word Variable
Dim oWrdDoc As Word.Document
Dim oWrdRng As Word.Range

'Declare Excel Variable
Dim ExcRng As Excel.Range

On Error Resume Next

'Get the Active instance of Outlook if the is one
Set oLookApp = GetObject(, "Outlook.application")
'if Outlook isnt open the create a new instance of Outlook
If Err.Number = 429 Then
Err.Clear
Set oLookApp = New Outlook.Application
End If

On Error GoTo ErrHand

'Create a new email
Set oLookItm = oLookApp.CreateItem(olMailItem)

Set ExcRng = Sheet7.Range("M2:U132")

With oLookItm

.SentOnBehalfOfName = "CapMkts@financeofamerica.com"

.To = "George@gmail.com"

.CC = ""
.Subject = "xxx Retail Pricing Report"
.Body = "ello," & "<br><br>" & "Please see comparisons below, full data is in the attached." & "<br><br>" & "Please let us know of any questions." & "<br><br>" & "Thank You."
.Attachments.Add Application.ActiveWorkbook.Path & "\IconPxReport_" & Format(Now, "yyyymmdd") & ".xlsm"


'Get the Active Inspector
Set oLookIns = .GetInspector

'Get the documant with the inspector
Set oWrdDoc = oLookIns.WordEditor



'Specify the range in the doc
Set oWrdRng = oWrdDoc.Application.ActiveDocument.Content
oWrdRng.Collapse Direction:=wdCollapseEnd

'Start New Paragraph
Set oWrdRng = oWrdDoc.Paragraphs.Add
oWrdRng.InsertBreak

ExcRng.Copy
oWrdRng.PasteSpecial DataType:=wdPasteHTML

.Display
'.send

End With

Exit Sub

ErrHand:
Debug.Print "ERROR: " & strError & "(" & Err.Number & ") " & Err.Description
Debug.Print "(" & Err.Number & ") " & Err.Description
Stop
Resume Next
Exit Sub

End Sub

1632245240840.png
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I made several changes toward the bottom of your macros. You can compare this to your original.

There were two new references I had to include here in order for this email macro to function correctly. They are the LAST TWO listed in the image below.

VBA Code:
Option Explicit

Sub sendemail()

'Declare Outlook Variables
Dim oLookApp As Outlook.Application
Dim oLookItm As Outlook.MailItem
Dim oLookIns As Outlook.Inspector

'Declare Word Variable
Dim oWrdDoc As Word.Document
Dim oWrdRng As Word.Range

'Declare Excel Variable
Dim ExcRng As Excel.Range

On Error Resume Next

'Get the Active instance of Outlook if the is one
Set oLookApp = GetObject(, "Outlook.application")
    
    'if Outlook isnt open the create a new instance of Outlook
    If Err.Number = 429 Then
        Err.Clear
        Set oLookApp = New Outlook.Application
    End If

On Error GoTo ErrHand

'Create a new email
Set oLookItm = oLookApp.CreateItem(olMailItem)

Set ExcRng = Sheet2.Range("M2:U132")

    With oLookItm
    
        .SentOnBehalfOfName = "CapMkts@financeofamerica.com"
        
        .To = "George@gmail.com"
        
        .CC = ""
        .Subject = "xxx Retail Pricing Report"
        .Body = "ello," & "<br><br>" & "Please see comparisons below, full data is in the attached." & "<br><br>" & "Please let us know of any questions." & "<br><br>" & "Thank You."
        .Attachments.Add Application.ActiveWorkbook.Path & "\IconPxReport_" & Format(Now, "yyyymmdd") & ".xlsm"
        
        
        'Get the Active Inspector
        Set oLookIns = .GetInspector
        
        'Get the documant with the inspector
        Set oWrdDoc = oLookIns.WordEditor
        
        
        
        'Specify the range in the doc
        Set oWrdRng = oWrdDoc.Application.ActiveDocument.Content
        oWrdRng.Collapse Direction:=wdCollapseEnd
        
        'Start New Paragraph
        Set oWrdRng = oWrdDoc.Paragraphs.Add
        oWrdRng.InsertBreak
        
        ExcRng.Copy
        oWrdRng.PasteSpecial DataType:=wdPasteHTML
        
        .Display
        '.send
    
    End With

Exit Sub



'Instead of using Debug Print, why not place the error into a MsgBox that can be visually presented ? Tons easier.


Dim strError As String
ErrHand:
    Debug.Print "ERROR: " & strError & "(" & Err.Number & ") " & Err.Description
    Debug.Print "(" & Err.Number & ") " & Err.Description
    'Stop                                                                                   '<<-- Do not use STOP
    Resume Next
    Exit Sub

End Sub
 

Attachments

  • MS References.jpg
    MS References.jpg
    55.3 KB · Views: 127
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top