Microsoft Office Forums - View Single Post

MattMurdock · 08-06-2012, 04:10 PM

Hi guys I have an email macro that creates its .To field with a long list of email addresses in a column. My problem is that it seems the .To field only seems to be able to read one cell at a time. Is there a workaround available besides using CONCATENATE? This list is for over 50 addresses and that would be very annoying.

Heres my code

Code:

Sub PackagingtoMDEmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
   
  Call SaveAs
  UserForm1.Show
  
    On Error Resume Next
    With OutMail
        .To = Range("A58:A120")
        .CC = ""
        .BCC = ""
        .Subject = "PIN REQUEST-" & Range("C16").Value & " " & Range("N15")
        .Body = ActiveSheet.TextBox1
    If Range("N15").Value = "***RUSH" Then
        .Importance = 2
        .Priority = 2
    End If
        .display
        .Attachments.Add ActiveWorkbook.FullName
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub