CODEWITHSUNDEEP
vbaexcel
Ed Briscoe
Ed Briscoe

Reputation: 117

Get cell info from another column in the row

How do I make it so the email address for the recipient name is taken from a different column.

I have the names written out in a column and I want to check the date in the same row for each person and if its 1 month out then send an email to that person. I can only reference one specific cell but I need it for each row as it iterates down the P column.

Sub Workbook_Open()

Dim Cell As Range
Dim objDate As Date

For Each Cell In Range("P3:P4").Cells

    If Cell.Value <= Date + 30 Then

        'MsgBox "Going to expire in 1 month"

        Dim appOutlook As Outlook.Application
        Dim mitOutlookMsg As Outlook.MailItem
        Dim recOutlookRecip As Outlook.Recipient

        ' Step 1: Initialize an Outlook session.
        Set appOutlook = CreateObject("Outlook.Application")
        ' Step 2: Create a new message.
        Set mitOutlookMsg = appOutlook.CreateItem(olMailItem)
        With mitOutlookMsg
            ' Step3: Add the To recipient(s) to message.
            Set recOutlookRecip = .Recipients.Add(Cells(3, 2))
            recOutlookRecip.Type = olTo
            'Set valid properties like Subject, Body, and Importance of the message.
            .Subject = "Test123"
            '.Body = "Test"
            .BodyFormat = olFormatHTML
            .HTMLBody = " TEST EMAIL "        
            .Importance = olImportanceHigh 'High importance    
            ' Resolve every Recipient's name
            For Each recOutlookRecip In .Recipients
                recOutlookRecip.Resolve
                If Not recOutlookRecip.Resolve Then
                    mitOutlookMsg.Display
                End If
            Next
            .Send
        End With
        Set mitOutlookMsg = Nothing
        Set appOutlook = Nothing       

    Else

    End If
Next Cell

End Sub

Upvotes: 0

Views: 171

Answers (1)

Eric Harlan
Eric Harlan

Reputation: 384

I think what you're looking for is: Range.Offset(row, col)

For example:

 For Each Cell In Range("P3:P4").Cells
    'cell.Value refers to P3:P4
    myDate = cell.Value

    'cell.Offset(0, 1).Value refers to the column one to the right of cell
    myName = cell.Offset(0, 1).Value
Next cell

Upvotes: 1

Related Questions