How do I SKIP a command with an IF CONDITION in a LOOP

[MikeCDPQ]

This is an extract of a script that works very well. I would just like to improve it somewhat.

The script loops through a spreadsheet, creates folders, creates new files, and sends them by email according to certain conditions.

In current state of things, an email address is always found, either vendor or buyer. If no address were to be found I would get an error message ("Email not sent, no address found to mail to&quot and that would kill my loop.
Lotus notes must have a RECIPIENT

I would like to be able to keep looping if:
Column J is not blank (which means "DO NOT SEND EMAIL&quot
How would I change bolded portion here below.

Do
EmailVendorNo = EmailList.Sheets("list&quot.Range("A" + CStr(j)).Value


If (EmailVendorNo = CurrentVendorNo) Then
RECIPIENT= EmailList.Sheets("list&quot.Range("f" + CStr(j)).Value
Subject = "Aged Report"
End If


If (EmailList.Sheets(&quot;list&quot.Range(&quot;j&quot; + CStr(j))) <> &quot;&quot; Then
With Workbooks(&quot;VendorEmailAddresses.xls&quot.Worksheets(&quot;Buyers&quot
BuyerRecipient = WorksheetFunction.VLookup(Cells(5, 1).Value, .Range(.Cells(1, 1), .Cells(19, 2)), 2, False)
End With
RECIPIENT= BuyerRecipient
Subject = &quot;Report not sent to vendor as per buyer's request&quot;
End If

....... there are 2 more if conditions after this point


j = j + 1

Loop Until EmailVendorNo = currentVendorNo Or EmailVendorNo = &quot;&quot;

'Start Lotus Notes application

Const EMBED_ATTACHMENT = 1454

Set session = CreateObject(&quot;Notes.NotesSession&quot
Set db = session.GetDatabase(&quot;&quot;, &quot;&quot
Call db.OpenMail

Set doc = db.CreateDocument
msg = &quot;Please find herewith...&quot; & vbCrLf & _
Call doc.ReplaceItemValue(&quot;SendTo&quot;, RECIPIENT
)
Call doc.ReplaceItemValue(&quot;Subject&quot;, Subject)

Basically what I want:
If there is an instruction on spreadsheet (DO NOT SEND AUTOMATICALLY) simply skip that vendor and keep looping through others.

Any suggestions anyone ???

Thanks.

 

[SkipVought]

Hi,
Here's come pseudocode...

do
...
  if NOT (DO NOT SEND AUTOMATICALLY) then
    'email statements

  end if
...
loop until(exit_condition)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MikeCDPQ]

Hi, SkipVought,

I came up with the following which I feel is exactly what I need but it does not work. It still sends the email to one vendor that has the DO NOT SEND column mark with and X (DoNotSend <>&quot;&quot

Any idea of what I am missing.

Do
EmailVendorNo = EmailList.Sheets(&quot;list&quot.Range(&quot;A&quot; + CStr(j)).Value
DoNotSend = EmailList.Sheets(&quot;list&quot.Range(&quot;j&quot; + CStr(j))

If (EmailVendorNo = CurrentVendorNo And DoNotSend = &quot;&quot Then
Recipient = EmailList.Sheets(&quot;list&quot.Range(&quot;f&quot; + CStr(j)).Value
Subject = &quot;McKesson - Aged Report / McKesson - Rapport de produits agés&quot;
ElseIf (EmailVendorNo = CurrentVendorNo And DoNotSend <> &quot;&quot Then
End If


If (EmailList.Sheets(&quot;list&quot.Range(&quot;f&quot; + CStr(j)) = &quot;&quot; And DoNotSend = &quot;&quot;) Then
With Workbooks(&quot;vendoremailaddressesTesting.xls&quot.Worksheets(&quot;Buyers&quot
BuyerRecipient = WorksheetFunction.VLookup(Cells(5, 1).Value, .Range(.Cells(1, 1), .Cells(19, 2)), 2, False)
End With
Recipient = BuyerRecipient
Subject = &quot;Aged Report delivery failure; Vendor's email address missing&quot;
ElseIf (EmailList.Sheets(&quot;list&quot.Range(&quot;f&quot; + CStr(j)) = &quot;&quot; And DoNotSend <> &quot;&quot;) Then
End If


If EmailVendorNo = &quot;&quot; Then
With Workbooks(&quot;vendoremailaddressesTesting.xls&quot.Worksheets(&quot;Buyers&quot
BuyerRecipient = WorksheetFunction.VLookup(Cells(5, 1).Value, .Range(.Cells(1, 1), .Cells(19, 2)), 2, False)
End With
Recipient = BuyerRecipient
Subject = &quot;Aged Report delivery failure; Vendor not found on email list&quot;
End If

j = j + 1

Loop Until EmailVendorNo = CurrentVendorNo Or EmailVendorNo = &quot;&quot;


Thanks for your help once again.

Michel

 

[SkipVought]

whatever works for you.

Basically, though, when programming, significant bit of code that are identical, ought to be in a single place. Makes for better maintainablilty.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com