excel - How do I repeat a Sub row by row in VBA? -

i have been able send emails excel through gmail, excel cells defining meta-data, body, , attachment of email.

this sub runs on selected cells. i'd ideally sub run on first row (row 2 in case), , run on next rows, until has reached end.

the end goal able automate sending of customized emails via excel.

here's have far.

sub cdo_mail_small_text_2()     dim imsg object     dim iconf object     dim strbody string     dim flds variant      set imsg = createobject("cdo.message")     set iconf = createobject("cdo.configuration")      iconf.load -1    ' cdo source defaults     set flds = iconf.fields     flds         .item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = true         .item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1         .item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "myemail"         .item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mypassword"         .item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"          .item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2         .item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25         .update     end      if sheets("data").range("g2").value = "statement"     strbody = "test" & sheets("data").range("e2").value     else     strbody = "test 2"     end if      imsg         set .configuration = iconf         .to = sheets("data").range("a2").value         .cc = ""         .bcc = ""         .replyto = sheets("data").range("d2").value         .from = sheets("data").range("c2").value & "<email>" 'this changes name, email come 'sendusername' above         .subject = sheets("data").range("b2").value         .textbody = strbody         .addattachment "" 'don't put in "", write direct path file. possible non-local?         .send     end  end sub 

any appreciated!! everyone.

you'll need 2 subs, first 1 being existing 1 send e-mail, second 1 call first 1 set of e-mail addresses.

for first one, cdo_mail_small_text_2, make these changes make "parameterized" (as opposed hardcoded version now):

' add parameters sub declaration sub cdo_mail_small_text_2(recipientaddress string, replytoaddress string, _     subject string, fromaddress string, statement string, _     valueofcolumne string)      dim imsg object     dim iconf object     dim strbody string     dim flds variant      set imsg = createobject("cdo.message")     set iconf = createobject("cdo.configuration")      iconf.load -1    ' cdo source defaults     set flds = iconf.fields     flds         .item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = true         .item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1         .item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "myemail"         .item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mypassword"         .item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"          .item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2         .item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25         .update     end      if statement = "statement"         strbody = "test" & valueofcolumne 'use sub parameter     else         strbody = "test 2"     end if      imsg         set .configuration = iconf         .to = recipientaddress 'use sub parameter         .cc = ""         .bcc = ""         .replyto = replytoaddress 'use sub parameter         .from = fromaddress 'use sub parameter         .subject = subject 'use sub parameter         .textbody = strbody          .addattachment ""          .send     end end sub 

the second one, let's call send_messages, should this:

sub send_messages()     dim recipientaddress string, replytoaddress string, _     subject string, fromaddress string, statement string, _     valueofcolumne string      ' change match length of recipient list     each in sheets("data").range("a2:a100")          recipientaddress = i.value         replytoaddress = i.offset(0,3).value         subject = i.offset(0,1).value         fromaddress = i.offset(0,2).value         statement = i.offset(0,6).value         valueofcolumne = i.offset(0,4).value          call cdo_mail_small_text_2(recipientaddress, replytoaddress, subject, _         fromaddress, statement, valueofcolumne)          ' shorter alternative (the above variable declarations wouldn't needed,         ' call cdo_mail_small_text_2(i.value, i.offset(0,3).value, i.offset(0,1).value, _         'i.offset(0,2).value, i.offset(0,6).value, i.offset(0,4).value)     next end sub 

explanation:

the first sub changed 1 hardcoded recipient addresses etc. parameter-based sub. can run other subs passing these parameters.

the second sub that. goes through every cell in a2 a100 , calls first sub data row. while doing that, i becomes cell column a, hence in first run, i equals sheets("data").range("a2"). column contains recipients, column b subject lines , on. pass subject line (and remaining parameters) cdo_mail_small_text_2 sub, use .offset(rows, cols) method. used reference cells relative distance cell, i.e. i equals a2, i.offset(0,1) equals b2 whereas i.offset(1,0) equal a3. make easier understsand, declaring variables parameters , setting them offset method. can see in code, step can skipped , offset method used in call command directly.