Error when creating mailmessage

Solved/Closed
Report
Posts
13
Registration date
Monday March 11, 2013
Status
Member
Last seen
August 14, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

Using Excel and Outlook 2007 I want to create an emailmessage with a Macro.
But I am running into an error I can not find any "comprehensible" documentation about anywhere online.

The error is a runtime error and has error code -2147467259 (80004005)

The error message displayed
(translated from dutch so might not be the exact english error message)
= "The lower limit of the matrix should be zero."

The error occurs at the line: ".Body = Copyrng"
If I replace "Copyrng" in this line with "" the code works properly, so it has something to do with the range I selected, or not?

What is the solution?

Here is my code:
Private Sub Copy()
'Set PreRequisits
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.ReferenceStyle = xlA1
    Dim KeyCells As Range
    Dim Chainrng As Range
    Dim PriceRng As Range
    Dim Copyrng As Range
    lastrow = Range("A65536").End(xlUp).Row
    Set KeyCells = Range("A1:J30")
    Set Chainrng = Range("C:C")
    Set PriceRng = Range("D:D")
    Set Copyrng = Range("A2:J" & lastrow)
    
'Autofit column widths
    Columns("A:J").Select
    Columns("A:J").EntireColumn.autofit
    PriceRng.ColumnWidth = "10.14"
    Chainrng.ColumnWidth = "11.57"

'Create Mail Item and view before sending
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Mailrng As Range
    Set Mailrng = Selection
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = "Recipient"
        .CC = ""
        .BCC = ""
        .Subject = "Subject"
        .Body = Copyrng
        .Display
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.ReferenceStyle = xlR1C1
End Sub


Thanks in advance,

Pluggie

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I think the issue is that you have a range of names that you want to send email to. The names has to be in one cell ( i think)