SirScott13
Board Regular
- Joined
- Sep 21, 2012
- Messages
- 63
- Office Version
- 365
- Platform
- Windows
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:
The offending part of the code is the following:
Any help would be appreicated.
Thanks in advance.
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:
Code:
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.