Send Email from Excel-CodePudding

I am trying to send email from Excel, which send attachment using pivot table range saved in Data sheet from A2 and gets dynamic every day. but i am getting error as obejct does not support this method. getting error at "TO" field

Sub emailtest()

Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As String
Dim rngSubject As String
Dim rngBody As String
Dim LastRow As Long
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)


Sheets("Data").Select


LastRow = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row



With objMail
.To = Worksheets("Data").Range("A2: A" & LastRow - 1)
.Subject = "Sell Fail Trade"
.Body = "Please find today's sell report"
.Attachments.Add "C\Temp Folder"
.Display 'Instead of .Display, you can use .Send to send the email _
            or .Save to save a copy in the drafts folder
End With

Set objOutlook = Nothing
Set objMail = Nothing



End Sub

Can someone please help me to fix the error.

CodePudding user response：

The To-property expects a single string as parameter. If you have more that one person that should receive the mail, you need to concatenate the names with ;

if LastRow-1 is larger than 3, you have more than one cell in your range and Worksheets("Data").Range("A2: A" & LastRow - 1) will give you a 2-dimensional array of values. And yes, it is 2-dimensional, even if it contains only one column.

There is a nice function in VBA, join that can concatenate the content of an array, but in this case it cannot be used as it works only with 1-dimensional arrays.

To you will have to loop over the range manually to create the to-string. I suggest to create a simple, reusable function for that (could be used, for example, also for the cc field).

Function join2D(a As Variant, Optional delimiter As String = ";") As String
    ' Check if a is a single value
    If (VarType(a) And vbArray) = 0 Then
        join2D = a
        Exit Function
    End If
    
    Dim i As Long, j As Long
    For i = LBound(a, 1) To UBound(a, 1)
        For j = LBound(a, 2) To UBound(a, 2)
            Dim v
            v = a(i, j)
            If Not IsEmpty(v) And Not IsError(v) Then
                join2D = join2D & IIf(join2D = "", "", delimiter) & CStr(v)
            End If
        Next j
    Next i
End Function

You then write in the routine that creates the mail:

.To = Join2D(Worksheets("Data").Range("A2: A" & LastRow - 1))

CodePudding user response：

The To property is a string property. It is a semicolon-delimited String list of display names for the To recipients for the Outlook item. So, you need to extract a string from the Range object in Excel. For example, you may try using the Range.Value property which returns or sets a Variant value that represents the value of the specified range.

.To = Worksheets("Data").Range("A2: A" & LastRow - 1).Value

.To = Worksheets("Data").Range("A2: A" & LastRow - 1).Value2

If the target range is multidimensional (or consists of multiple cells) you need to build the To string based on range values going through each cell and only then assign it to the To property.