vba - Can't figure out Object required error -


the purpose of code send email list of addresses. determine start point, user form appear asking stating column , row (with plans improve, i'll add other options in).

i had code working, however, made tweaks , not keep getting object required error i've tried figuring out hours without luck. please can take @ code , suggest might going wrong?

nb. tried declaring variables didn't fix issue.

the error on loop until username.value = ""

sub cmdgo_click()  application.displayalerts = false  = cmbrow  if = ""     exit sub end if  username = cmbcolumn  if username = ""     exit sub end if  select case username      case "a", "a"         username = cells(i, "a").value     case "b", "b"         username = cells(i, "b").value     case "c", "c"         username = cells(i, "c").value     case "d", "d"         username = cells(i, "d").value     case "e", "e"         username = cells(i, "e").value  end select   dim outapp object     dim outmail object     set outapp = createobject("outlook.application")     set outmail = outapp.createitem(0)     on error resume next     outmail          .to = username         .cc = ""         .bcc = ""         .importance = 1         .subject = "hello"         .htmlbody = "message"  'display shows each email before sending           .display 'send sends email automatically '         .send      end     on error goto 0     set outmail = nothing     set outapp = nothing  application.displayalerts = true  = + 1  loop until username.value = ""  end sub 

there few syntax , understanding mistakes in code. have listed them below , refactored code bit work desire.

  1. it's best use option explicit @ top of each module , explicitly declare variables desired type
  2. place setting of username inside loop resets each time new email based on i = i+1 counter.

rest of comments in code:

option explicit  sub cmdgo_click()  application.displayalerts = false  if cmbrow = "" or cmbcolumn = ""      exit sub end if  dim long = cmbrow  dim usernamecol string 'created new variable column letter can used later in loop , removed `select case` block. usernamecol = cmbcolumn  'set outlook outside loop since need call once, doing in loop creates unneccesary processing dim outapp object dim outmail object set outapp = createobject("outlook.application")  until len(cells(i, usernamecol).value) = 0 'will stop when blank cell appears      dim username string     username = cells(i, usernamecol).value 'always user whatever column choosen      set outmail = outapp.createitem(0) 'this goes here because new email needed each time     on error resume next     outmail          .to = username         '.cc = "" 'you can remove lines because not putting in field         '.bcc = "" 'you can remove lines because not putting in field         .importance = 1         .subject = "hello"         .htmlbody = "message"          'display shows each email before sending         .display         'send sends email automatically         '.send      end     on error goto 0      = + 1  loop   'destroy outlook when finished processing mails set outmail = nothing set outapp = nothing  application.displayalerts = true  end sub 

Comments

Popular posts from this blog

php - Wordpress website dashboard page or post editor content is not showing but front end data is showing properly -

javascript - Get parameter of GET request -

javascript - Twitter Bootstrap - how to add some more margin between tooltip popup and element -