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.
- it's best use option explicit @ top of each module , explicitly declare variables desired type
- 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
Post a Comment