Trying to reference data from a column of cells in a message box

[Rogerisin]

Good Afternoon,

The following is a snippet of code I am trying to write to have the outcome ask if a (name in a) range of cells is okay or not.
Please advise. I've tried a few different ways and all have the same result: nada


Do
If UCase(Cells(I, 16)) <> "CLOSED" Then
Application.DisplayAlerts = False
intPress = MsgBox "Is " &Cells(I, 13).Value, " still the Primary?", vbQuestion + _
vbYesNoCancel, "Report Request")

[Paul_Hossler]

That's not all the code (I hope)

It's better to attach a small workbook with the macro(s) that are causing problems, the input data, and your desired output data

And before someone says it, please use CODE tags, that's the [#] icon, and paste your macro between

[Rogerisin]

Good Afternoon again all,


I am trying to write code that searches a column of both open and closed issues, then, in prep to send an email to one of two managers or two relating assistant managers, asks the user if the manager is the person to receive the email or if the asst. manager.
Column L has the primary email address
Column M has the manager
Column N has the assistant manager's email address


Once I have this solved, I can do the simple part by writing the code containing the email's body, cc, etc.

Spreadsheet and VBA Code is attached.

Cheers,

[Paul_Hossler]

Option Explicit


Sub EmailBanc1OSCKs()
'
' EmailBanc1OSCKs Macro
'

' Dim Eitem As Outlook.MailItem
Dim VAg As String
Dim EmPrime As String, EmailTo As String
Dim NameAgent As Range
Dim PrimaryRange As Range
Dim OTAgentRange As Range
Dim i As Long

With Worksheets("Sheet1") ' <<<<<<<<<< change as necessary

For i = 20 To .Cells(.Rows.Count, 16).End(xlUp).Row
If UCase(.Cells(i, 16).Value) <> "CLOSED" Then
If MsgBox("Is " & .Cells(i, 13).Value & " still the Primary?", vbQuestion + vbYesNo, "Report Request") = vbYes Then
EmailTo = .Cells(i, 12).Value
Else
EmailTo = .Cells(i, 15).Value
End If
End If

MsgBox "Test -- " & EmailTo

Next i
End With


End Sub


I changed some data in your workbook to test

[Rogerisin]

Thank you for your amazing help!

:bow:

Cheers,