Excel VBA - read cell value from code

Question

task to send email from excel. Below is the example column. How I can get the email value from the loop? Thanks

enter image description here

Here is my code :

   Dim rng As Range
   Dim row As Range
   Dim cell As Range
   Dim mydate As Date
   Dim myDiff As Long

Set rng = Range("E5:E17")
Set rng2 = Range("F5:F17")
Dim cell2 As Range

Dim i As Integer
i = 5

For Each row In rng.Rows
  For Each cell In row.Cells

MsgBox cell.Value
mydate = cell.Value
myDiff = DateDiff("d", Date, mydate)

Set rng2 = Range("F" & i)
For Each cell2 In rng2
 MsgBox cell2.Value
Next cell2


If myDiff > -8 And myDiff < 8 Then
  aEmail.Send
End If

i = i + 1
  Next cell
Next row

Well, you're looking at Range("E5:E17"), and your email addresses are in column F, so it looks like you should just read the value from column F when you need the email. What exactly is your question? — Ken White
– Ken White, Commented Jul 11, 2013 at 3:26
yes. above script is only get date value only. I need the email value in the loop also. So that if the datediff is more than 8, email will send to the respective email. — user2286756
– user2286756, Commented Jul 11, 2013 at 7:08

matzone · Accepted Answer · 2013-07-11 15:50:23Z

19

I think you need this ..

Dim n as Integer   

For n = 5 to 17
  msgbox cells(n,3) '--> sched waste
  msgbox cells(n,4) '--> type of treatm
  msgbox format(cells(n,5),"dd/MM/yyyy") '--> Lic exp
  msgbox cells(n,6) '--> email col
Next

edited Jul 11, 2013 at 15:50

answered Jul 11, 2013 at 4:16

matzone

5,7293 gold badges19 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2286756 Over a year ago

No, i just want to get the email value in the loop.

user2286756 Over a year ago

Hm... i already update my code. BUT, how do i get only the email column for the current row only?

user2286756 Over a year ago

yes. that i want! I also got already. Your code more simpler. Thanks!

Collectives™ on Stack Overflow

Excel VBA - read cell value from code

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related