Outlook VBA Form not finding FOR LOOP -- Error message

M

Mitch

I'm using Outlook 2003 SP3, using a VBA Form created throug Outlook, it
basically checks some checkboxes for a value of True, then adds an email
address. It works perfectly fine when I do it as a macro.

When I plug it into the form, my FOR Loop "NEXT" isn't finding the loop. I
get a Next neesda for loop even though I have one in it.

Here's my code on Mouse click submit. [Note: NumberEmails.Value is a number
taken from a text box whos value is modifed by SpinButton]

Mind you I have replaced some string variables I hardcoding into the macro
as variables, so those have not been tested just yet, but I'd expect them to
work.

Private Sub SubmitButton_Click()

Set OutlookApp = CreateObject("Outlook.Application")
Dim objMail As Outlook.MailItem
Set objMail = OutlookApp.CreateItem(0)

Dim rcpt As Recipient

For i = 1 To NumberEmails.Value
Set objMail = Application.CreateItem(0)
If CB1.Value = True Then
Set rcpt = objMail.Recipients.Add(CB1.Tag)
If CB2.Value = True Then
Set rcpt = objMail.Recipients.Add(CB2.Tag)
If CB3.Value = True Then
Set rcpt = objMail.Recipients.Add(CB3.Tag)
If CB4.Value = True Then
Set rcpt = objMail.Recipients.Add(CB4.Tag)
If CB5.Value = True Then
Set rcpt = objMail.Recipients.Add(CB5.Tag)
If CB6.Value = True Then
Set rcpt = objMail.Recipients.Add(CB6.Tag)
If CB7.Value = True Then
Set rcpt = objMail.Recipients.Add(CB7.Tag)
If CB8.Value = True Then
Set rcpt = objMail.Recipients.Add(CB8.Tag)
If CB9.Value = True Then
Set rcpt = objMail.Recipients.Add(CB9.Tag)
If CB10.Value = True Then
Set rcpt = objMail.Recipients.Add(CB10.Tag)
If CB11.Value = True Then
Set rcpt = objMail.Recipients.Add(CB11.Tag)
If CB12.Value = True Then
Set rcpt = objMail.Recipients.Add(CB12.Tag)
If CB13.Value = True Then
Set rcpt = objMail.Recipients.Add(CB13.Tag)
If CB14.Value = True Then
Set rcpt = objMail.Recipients.Add(CB14.Tag)
If CB15.Value = True Then
Set rcpt = objMail.Recipients.Add(CB15.Tag)
If CB16.Value = True Then
Set rcpt = objMail.Recipients.Add(CB16.Tag)
If CB17.Value = True Then
Set rcpt = objMail.Recipients.Add(CB17.Tag)
If CB18.Value = True Then
Set rcpt = objMail.Recipients.Add(CB18.Tag)
If CB19.Value = True Then
Set rcpt = objMail.Recipients.Add(CB19.Tag)
If CB20.Value = True Then
Set rcpt = objMail.Recipients.Add(CB20.Tag)
Else: MsgBox ("No Emails Selected"), vbOKOnly

objMail.Subject = SubjectLine + CStr(i)
objMail.Body = "Test Message " + CStr(i)

Set myAtt = objMail.Attachments
myAtt.Add = AttachmentPath

objMail.Save
objMail.Send
Next

Set objMail = Nothing
Set myAtt = Nothing
Set rcpt = Nothing

End Sub
------
Private Sub SpinButton1_SpinDown()
If NumberEmails.Value = 1 Then NumberEmails.Value = 1 Else
NumberEmails.Value = NumberEmails.Value - 1
End Sub
------
Private Sub SpinButton1_SpinUp()
If NumberEmails.Value = 20 Then NumberEmails.Value = 20 Else
NumberEmails.Value = NumberEmails.Value + 1
End Sub
----------
 
M

Mitch

Found that my If statements were messing it up, wonder if there's a better
way to do this. A for loop to run through my 20 if statements ?

Mitch said:
I'm using Outlook 2003 SP3, using a VBA Form created throug Outlook, it
basically checks some checkboxes for a value of True, then adds an email
address. It works perfectly fine when I do it as a macro.

When I plug it into the form, my FOR Loop "NEXT" isn't finding the loop. I
get a Next neesda for loop even though I have one in it.

Here's my code on Mouse click submit. [Note: NumberEmails.Value is a number
taken from a text box whos value is modifed by SpinButton]

Mind you I have replaced some string variables I hardcoding into the macro
as variables, so those have not been tested just yet, but I'd expect them to
work.

Private Sub SubmitButton_Click()

Set OutlookApp = CreateObject("Outlook.Application")
Dim objMail As Outlook.MailItem
Set objMail = OutlookApp.CreateItem(0)

Dim rcpt As Recipient

For i = 1 To NumberEmails.Value
Set objMail = Application.CreateItem(0)
If CB1.Value = True Then
Set rcpt = objMail.Recipients.Add(CB1.Tag)
If CB2.Value = True Then
Set rcpt = objMail.Recipients.Add(CB2.Tag)
If CB3.Value = True Then
Set rcpt = objMail.Recipients.Add(CB3.Tag)
If CB4.Value = True Then
Set rcpt = objMail.Recipients.Add(CB4.Tag)
If CB5.Value = True Then
Set rcpt = objMail.Recipients.Add(CB5.Tag)
If CB6.Value = True Then
Set rcpt = objMail.Recipients.Add(CB6.Tag)
If CB7.Value = True Then
Set rcpt = objMail.Recipients.Add(CB7.Tag)
If CB8.Value = True Then
Set rcpt = objMail.Recipients.Add(CB8.Tag)
If CB9.Value = True Then
Set rcpt = objMail.Recipients.Add(CB9.Tag)
If CB10.Value = True Then
Set rcpt = objMail.Recipients.Add(CB10.Tag)
If CB11.Value = True Then
Set rcpt = objMail.Recipients.Add(CB11.Tag)
If CB12.Value = True Then
Set rcpt = objMail.Recipients.Add(CB12.Tag)
If CB13.Value = True Then
Set rcpt = objMail.Recipients.Add(CB13.Tag)
If CB14.Value = True Then
Set rcpt = objMail.Recipients.Add(CB14.Tag)
If CB15.Value = True Then
Set rcpt = objMail.Recipients.Add(CB15.Tag)
If CB16.Value = True Then
Set rcpt = objMail.Recipients.Add(CB16.Tag)
If CB17.Value = True Then
Set rcpt = objMail.Recipients.Add(CB17.Tag)
If CB18.Value = True Then
Set rcpt = objMail.Recipients.Add(CB18.Tag)
If CB19.Value = True Then
Set rcpt = objMail.Recipients.Add(CB19.Tag)
If CB20.Value = True Then
Set rcpt = objMail.Recipients.Add(CB20.Tag)
Else: MsgBox ("No Emails Selected"), vbOKOnly

objMail.Subject = SubjectLine + CStr(i)
objMail.Body = "Test Message " + CStr(i)

Set myAtt = objMail.Attachments
myAtt.Add = AttachmentPath

objMail.Save
objMail.Send
Next

Set objMail = Nothing
Set myAtt = Nothing
Set rcpt = Nothing

End Sub
------
Private Sub SpinButton1_SpinDown()
If NumberEmails.Value = 1 Then NumberEmails.Value = 1 Else
NumberEmails.Value = NumberEmails.Value - 1
End Sub
------
Private Sub SpinButton1_SpinUp()
If NumberEmails.Value = 20 Then NumberEmails.Value = 20 Else
NumberEmails.Value = NumberEmails.Value + 1
End Sub
----------
 
M

Mitch

Got it working, had to also remove = from the myAtt.Add line.

Mitch said:
Found that my If statements were messing it up, wonder if there's a better
way to do this. A for loop to run through my 20 if statements ?

Mitch said:
I'm using Outlook 2003 SP3, using a VBA Form created throug Outlook, it
basically checks some checkboxes for a value of True, then adds an email
address. It works perfectly fine when I do it as a macro.

When I plug it into the form, my FOR Loop "NEXT" isn't finding the loop. I
get a Next neesda for loop even though I have one in it.

Here's my code on Mouse click submit. [Note: NumberEmails.Value is a number
taken from a text box whos value is modifed by SpinButton]

Mind you I have replaced some string variables I hardcoding into the macro
as variables, so those have not been tested just yet, but I'd expect them to
work.

Private Sub SubmitButton_Click()

Set OutlookApp = CreateObject("Outlook.Application")
Dim objMail As Outlook.MailItem
Set objMail = OutlookApp.CreateItem(0)

Dim rcpt As Recipient

For i = 1 To NumberEmails.Value
Set objMail = Application.CreateItem(0)
If CB1.Value = True Then
Set rcpt = objMail.Recipients.Add(CB1.Tag)
If CB2.Value = True Then
Set rcpt = objMail.Recipients.Add(CB2.Tag)
If CB3.Value = True Then
Set rcpt = objMail.Recipients.Add(CB3.Tag)
If CB4.Value = True Then
Set rcpt = objMail.Recipients.Add(CB4.Tag)
If CB5.Value = True Then
Set rcpt = objMail.Recipients.Add(CB5.Tag)
If CB6.Value = True Then
Set rcpt = objMail.Recipients.Add(CB6.Tag)
If CB7.Value = True Then
Set rcpt = objMail.Recipients.Add(CB7.Tag)
If CB8.Value = True Then
Set rcpt = objMail.Recipients.Add(CB8.Tag)
If CB9.Value = True Then
Set rcpt = objMail.Recipients.Add(CB9.Tag)
If CB10.Value = True Then
Set rcpt = objMail.Recipients.Add(CB10.Tag)
If CB11.Value = True Then
Set rcpt = objMail.Recipients.Add(CB11.Tag)
If CB12.Value = True Then
Set rcpt = objMail.Recipients.Add(CB12.Tag)
If CB13.Value = True Then
Set rcpt = objMail.Recipients.Add(CB13.Tag)
If CB14.Value = True Then
Set rcpt = objMail.Recipients.Add(CB14.Tag)
If CB15.Value = True Then
Set rcpt = objMail.Recipients.Add(CB15.Tag)
If CB16.Value = True Then
Set rcpt = objMail.Recipients.Add(CB16.Tag)
If CB17.Value = True Then
Set rcpt = objMail.Recipients.Add(CB17.Tag)
If CB18.Value = True Then
Set rcpt = objMail.Recipients.Add(CB18.Tag)
If CB19.Value = True Then
Set rcpt = objMail.Recipients.Add(CB19.Tag)
If CB20.Value = True Then
Set rcpt = objMail.Recipients.Add(CB20.Tag)
Else: MsgBox ("No Emails Selected"), vbOKOnly

objMail.Subject = SubjectLine + CStr(i)
objMail.Body = "Test Message " + CStr(i)

Set myAtt = objMail.Attachments
myAtt.Add = AttachmentPath

objMail.Save
objMail.Send
Next

Set objMail = Nothing
Set myAtt = Nothing
Set rcpt = Nothing

End Sub
------
Private Sub SpinButton1_SpinDown()
If NumberEmails.Value = 1 Then NumberEmails.Value = 1 Else
NumberEmails.Value = NumberEmails.Value - 1
End Sub
------
Private Sub SpinButton1_SpinUp()
If NumberEmails.Value = 20 Then NumberEmails.Value = 20 Else
NumberEmails.Value = NumberEmails.Value + 1
End Sub
----------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top