CODEWITHSUNDEEP
excelvbaoutlook
khyati dedhia
khyati dedhia

Reputation: 81

Send email to email addresses in a range of cells

I am trying to send email from Excel which sends an attachment using pivot table range saved in Data sheet from A2 and is dynamic every day.

At the .TO field I get the error

Object does not support this method.

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

Upvotes: 1

Views: 324

Answers (2)

FunThomas
FunThomas

Reputation: 29146

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))

Upvotes: 1

Eugene Astafiev
Eugene Astafiev

Reputation: 49397

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

or

.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.

Upvotes: 0

Related Questions