Email with .Body & .To using Variable for the .To

[tcnt9176]

I have an email challenge that I have not been able to overcome. I was using Sendmail but could not add anything to the body of the email. I switched over to the method below but have not been able to figure out how to use a variable in the .To instead of an email address. The email addresses will change everytime this macro is used so that is the reason it needs to be a variable and not just an email group. Thanks for your help!!

    Dim vAddresses As Variant
    With ActiveSheet
        vAddresses = Application.Transpose(.Range("C2:C" & _
            .Cells(Rows.Count, "C").End(xlUp).Row))
    End With

   Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
        If ActiveWorkbook.Path <> "" Then
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)

            
strbody = "[SIZE=3]" & _
"All,
" & _
"Testing.
[B]"
                      
    
            On Error Resume Next
            With OutMail
                .To = vAddresses
                .CC = 
                .BCC = ""
                .Subject = "Claim Number Validation Report- " & Format(Date, "mm/dd/yy")
                .HTMLBody = strbody
                .Attachments.Add ("[URL="file://\\abc\client_services$\1"]\\abc\[/URL]ABC Report " & Format(Date, "yyyy.mm.dd") & ".xls")
                .Display
                .Send
            End With
            On Error GoTo 0
    
            Set OutMail = Nothing
            Set OutApp = Nothing
        Else
            MsgBox "The ActiveWorkbook does not have a path, Save the file first."
        End If

[/B][/SIZE]

 

[tcnt9176]

Just checking to see if anyone has an answer to this.

 

[daverunt]

Hi,

I found this:
http://www.mrexcel.com/forum/excel-questions/633075-combine-text-strings-column.html

With ActiveSheet
        vAddresses = econcat(.Range("C2:C" & .Cells(Rows.Count, "C").End(xlUp).Row))    
End With



'Add function code:

Function econcat(oRng As Range) As String
    econcat = Join(Application.Transpose(oRng), ";")
End Function

 

[tcnt9176]

daverunt....You are AWESOME!!

Just so it is here for reference for anyone else that needs this, I just added the Join function to add commas and it worked fine.

vAddresses = Application.Transpose(.Range("C2:C" & _
            .Cells(Rows.Count, "C").End(xlUp).Row))       

vAddresses = Join(Application.Transpose(.Range("C2:C" & _
            .Cells(Rows.Count, "C").End(xlUp).Row)), ", ")