Call a Variable Email address from a cell with Outmail

[SirScott13]

I am trying to CC an email address which is entered into a cell in a spreadsheet. The email address can be variable. I've been banging my head against a wall trying to figure this out. Here is my code:

Private Sub CommandButton5_Click()
    Dim ArrQues() As Variant
    Dim ArrInput(6) As Variant
    Dim FoundCell As Range
    Dim lngLstRow As Long
    Dim strPNfromSales As String
    Dim strRGfromSales As String
    Dim FirstAddress As String
    Dim Msg As Variant
    Dim Msg1 As String
        
    strPNfromSales = ComboBox3.Text
    strRGfromSales = ComboBox8.Text
    ArrQues = Array("What is the Restocking Fee?", _
                    "Are there any Special Instructions from the Vendor?", _
                    "Are Replacement Parts on Order?", _
                    "What is the Replacement PO#?", _
                    "What is the Vendor RGA?", _
                    "What is the Return Number in Profit?")


Msg1 = ""
    With Sheets("Data")
        Set FoundCell = .Columns(2).Find(strPNfromSales, LookIn:=xlValues, LookAt:=xlWhole)
        If Not FoundCell Is Nothing Then
            FirstAddress = FoundCell.Address
            Do
                If FoundCell.Offset(0, 5).Value = strRGfromSales Then
                    lngLstRow = FoundCell.Row
                    GoTo DataInput
                End If
                Set FoundCell = .Columns(2).FindNext(FoundCell)
            Loop Until FoundCell.Address = FirstAddress
            Msg1 = strRGfromSales
        
        Else
            Msg1 = strPNfromSales
        End If
    End With
    
    
DataInput:
    If Len(Msg1) = 0 Then
        
        For k = LBound(ArrQues) To UBound(ArrQues)
            ArrInput(k) = get_Input(ArrQues(k), ArrQues(k), 1)
            If ArrInput(k) = False Then Exit Sub
        Next k
        
        With Sheets("Data")
            'output array to range
            .Range("L" & lngLstRow).Value = ArrInput(0)
            .Range("Q" & lngLstRow).Value = ArrInput(1)
            .Range("R" & lngLstRow).Value = ArrInput(2)
            .Range("S" & lngLstRow).Value = ArrInput(3)
            .Range("T" & lngLstRow).Value = ArrInput(4)
            .Range("U" & lngLstRow).Value = ArrInput(5)
        End With
        
        MyAddress = ActiveCell.Row


        Set OutApp = CreateObject("Outlook.Application")
            OutApp.Session.Logon
            Set OutMail = OutApp.CreateItem(0)
           
        With OutMail
                .To = "smcmillan@wyattseal.com"
                .CC = Range("AA" & lngLstRow).Text
                .BCC = ""
                .Subject = "Vendor Return Number " & ArrInput(5) & " has been entered."
                .Body = "A Vendor Return has been entered.  Layne will send paperwork for " & strPNfromSales & " to be returned.  " & "The Vendor RGA # is " & ArrInput(4) & ".  Sales Department, Please enter your replacement order at this time.  "
                .Send 'or use .Display
        End With
    
            Set OutMail = Nothing
            Set OutApp = Nothing
     
            Dim Msg2, Style, Title
            Msg2 = "E-mail has been sent " & Chr(13) & Chr(10) & "Press OK to continue."
            Style = vbOKOnly + vbInformation
            Title = "Open Issues List"
            Response = MsgBox(Msg2, Style, Title, Help, Ctxt)
        
        Else
        
        Msg = MsgBox(Msg1 & Chr(10) & _
                   " Record Not Found", 16, "Record Not Found")
    End If


End Sub

The offending part of the code is the following:

        Set OutApp = CreateObject("Outlook.Application")            OutApp.Session.Logon
            Set OutMail = OutApp.CreateItem(0)
           
        With OutMail
                .To = "smcmillan@wyattseal.com"
                .CC = Range("AA" & lngLstRow).Text
                .BCC = ""
                .Subject = "Vendor Return Number " & ArrInput(5) & " has been entered."
                .Body = "A Vendor Return has been entered.  Layne will send paperwork for " & strPNfromSales & " to be returned.  " & "The Vendor RGA # is " & ArrInput(4) & ".  Sales Department, Please enter your replacement order at this time.  "
                .Send 'or use .Display
        End With

Any help would be appreicated.

Thanks in advance.

 

[jerbaldw]

This is what I use for a .TO and .CC for two people. just modify the code with your parameters. I did not go through the code fully, assuming that the only problem is with how you referenced the .CC range this should work.

Dim wbuk As Workbook
Dim wsht As Worksheet
Set wbuk = ActiveWorkbook
Set wsht = wbuk.Worksheets("[COLOR=#0000ff]Enter Worksheet Name Here[/COLOR]")
'

'<......>

'
With OutMail
        .To = smcmillan@wyattseal.com
        .CC = wsht.[COLOR=#0000ff]Range("C14").Value [/COLOR]& "; " & [COLOR=#0000ff]wsht.Range("C20").Value

[/COLOR]'<......>
[COLOR=#0000ff]
[/COLOR]End Sub