referencing relative cell

trevorc · #1 08-11-2019, 06:56 PM

Hi all,

I'm trying to create an auto mailing system, it mostly works but i need to add a check to see if the recipient has already received the email
when i added the code in red below it halts and says not supported, or with xCell.selection it says type mismatch. Otherwise it works ok without the extra code.
Any help or pointers in the right direction is appreciated.
regards
Trevor

Code:

With oExcelEmailApp
    t = InputBox("Enter cell range of E-mail addresses", "Cell Selection")
    For Each xCell In Range(t)
        If xCell.Value Like "*@*" And xCell.Col + 2 = "No" Then
            If xEmailAddr = "" Then
                xEmailAddr = xCell.Value
            Else
                xEmailAddr = xEmailAddr & " ; " & xCell.Value
            End If
        End If
    Next
 End With

NoSparks · #2 08-11-2019, 09:31 PM

try

Code:

        If xCell.Value Like "*@*" And xCell.offset(,2).value = "No" Then

trevorc · #3 08-12-2019, 11:56 AM

That's perfect, thank you very much.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Relative link (same folder) from Excel cell into Word file	SparFuX	Word	1	12-18-2018 03:22 PM
Referencing a cell and getting its content and colour	H28Sailor	Excel	5	09-12-2017 11:41 PM
Referencing to a cell in table	naeemakhtar	Word VBA	1	06-02-2016 08:46 PM
INDIRECT and Relative Referencing?	tinfanide	Excel	7	03-15-2016 06:23 AM
Setting autofilter criteria using columns and cells relative to the active cell in another sheet	SarahL	Excel Programming	7	06-11-2015 10:26 AM