Outlook VBA Form not finding FOR LOOP -- Error message

  • Thread starter Thread starter Mitch
  • Start date Start date
Status
Not open for further replies.
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

----------
 
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" wrote:


> 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
> ----------
 
Got it working, had to also remove = from the myAtt.Add line.

"Mitch" wrote:


> 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" wrote:
>
> > 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
> > ----------
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
H Custom Outlook Contact Form VBA Outlook VBA and Custom Forms 1
D create an html table in outlook custom form 2010 using vba in MsAccess Outlook VBA and Custom Forms 7
G Adding a contact to Outlook with a custom form using Access VBA Outlook VBA and Custom Forms 1
M VBA Code to extract data from an Outlook Form Using Outlook 0
J VBA Form in Outlook to capture data and complile a mail Outlook VBA and Custom Forms 2
J Outlook custom form - VBS call VBA macro Outlook VBA and Custom Forms 3
M VBA Form how to load in Outlook 2003 Outlook VBA and Custom Forms 4
efire9207 VBA Outlook Contacts Outlook VBA and Custom Forms 6
B Requesting VBA code to make Outlook prompt for confirmation when deleting a task? Outlook VBA and Custom Forms 4
J Outlook VBA to send from Non-default Account & Data Files Outlook VBA and Custom Forms 4
P VBA to add email address to Outlook 365 rule Outlook VBA and Custom Forms 0
M Outlook 2016 outlook vba to look into shared mailbox Outlook VBA and Custom Forms 0
D Outlook VBA forward the selected email to the original sender’s email ID (including the email used in TO, CC Field) from the email chain Outlook VBA and Custom Forms 2
L Fetch, edit and forward an email with VBA outlook Outlook VBA and Custom Forms 2
BartH VBA no longer working in Outlook Outlook VBA and Custom Forms 1
W Can vba(for outlook) do these 2 things or not? Outlook VBA and Custom Forms 2
richardwing Outlook 365 VBA to access "Other Actions" menu for incoming emails in outlook Outlook VBA and Custom Forms 0
J Outlook Rules VBA Run a Script - Multiple Rules Outlook VBA and Custom Forms 0
C Outlook (desktop app for Microsoft365) restarts every time I save my VBA? Using Outlook 1
E Outlook 365 Outlook/VBA Outlook VBA and Custom Forms 11
J VBA for outlook to compare and sync between calendar Outlook VBA and Custom Forms 1
E Outlook VBA change GetDefaultFolder dynamically Outlook VBA and Custom Forms 6
S vba outlook search string with special characters Outlook VBA and Custom Forms 1
U Outlook 2019 VBA run-time error 424 Outlook VBA and Custom Forms 2
G VBA to save selected Outlook msg with new name in selected network Windows folder Outlook VBA and Custom Forms 1
F Excel VBA to move mails for outlook 365 on secondary mail account Outlook VBA and Custom Forms 1
K Outlook Office 365 VBA download attachment Outlook VBA and Custom Forms 2
V vBA for searching a cell's contents in Outlook and retrieving the subject line Outlook VBA and Custom Forms 1
B vBA for exporting excel file from outlook 2016 Outlook VBA and Custom Forms 3
S Excel vba code to manage outlook web app Using Outlook 10
S Problem Checking the available stores in my Inbox (Outlook VBA) Outlook VBA and Custom Forms 0
S Outlook VBA How to adapt this code for using in a different Mail Inbox Outlook VBA and Custom Forms 0
O VBA Outlook Message Attachment - Array Index Out of Bounds Outlook VBA and Custom Forms 0
J Want to learn VBA Macros for Outlook. What book can you recommend? Outlook VBA and Custom Forms 2
M Outlook 2013 reminder email by using Outlook vba Outlook VBA and Custom Forms 2
D Outlook VBA error extracting property data from GetRules collection Outlook VBA and Custom Forms 10
O Email not leaving Outbox when using Excel VBA to sync Outlook account Outlook VBA and Custom Forms 4
L Moving emails with similar subject and find the timings between the emails using outlook VBA macro Outlook VBA and Custom Forms 1
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0
N How can I increase/faster outlook VBA Macro Speed ? Using Outlook 2
N Outlook Email Rule execution through shortcut keys (VBA codes) Using Outlook 1
A VBA Code in Outlook disappears after first use Outlook VBA and Custom Forms 1
dweller Outlook 2010 Rule Ignores VBA Script Outlook VBA and Custom Forms 2
G Outlook VBA and Google Calendar ("Events") Outlook VBA and Custom Forms 1
J VBA Outlook : Subject line : Cut and Paste name to heading , number to very end of the body of Email Outlook VBA and Custom Forms 1
B Advanced Search in MS Outlook by VBA and SQL Outlook VBA and Custom Forms 2
K Outlook Archive to PST Files by Date Range VBA Script? Outlook VBA and Custom Forms 1
J Help Please!!! Outlook 2016 - VBA Macro for replying with attachment in meeting invite Outlook VBA and Custom Forms 9
S Find a cell value in excel using outlook vba Using Outlook 1
J Execute Add-In Button from VBA Outlook 2016 Outlook VBA and Custom Forms 1

Similar threads

Back
Top