Loop macro - copy, paste, run macro

[icestorm]

Hi, I am relatively new to VBA and therefore I would like to ask you for a help with VBA macro.

a) Current state

Worksheet "Customers" contains all our customers IDs in range B4:B72
Workheet "Send to customer" contains formulas which calculate current stock for one customer(When you insert his ID in cell B6)

VBA macro "Send email" saves Worksheet "Send to customers" as PDF and send it via e-mail to customer with ID in Cell B6

The problem is, that every time you want to send email to a customer you have to manually insert his ID in cell B6 (data validation button) and run a macro "Send email", but once per month we send the current level of stock to all our customers, hence we need a loop macro to get rid of manually inserting IDs over and over again.


b) Desired state:

Loop Macro "send e-mail to all":

Logic:

1) Select "ID of customer 1" (cell B4) from worksheet "Customers", insert that ID in cell B6 in worksheet "Send to customer", wait 10s to calculate formulas, run macro "Send email"
2) Select "ID of customer 2" (cell B5) from worksheet "Customers", insert that ID in cell B6 in worksheet "Send to customer", wait 10s to calculate formulas, run macro "Send email"
...

69) Select "ID of customer 69" (cell B72) from worksheet "Customers", insert that ID in cell B6 in worksheet "Send to customer", wait 10s to calculate formulas, run macro "Send email"


Thanks in advance for your help.

[mancubus]

hi and welcome to VBAX.

i assume 'Calculation Option' is 'Automatic'.

Sub vabx_52757_EmailAll()
    
    Dim i As Long
    
    For i = 4 To 72
        Worksheets("Send to customer").Range("B6").Value = Worksheets("Customers").Range("B" & i).Value
        Application.Wait (Now + TimeValue("00:00:10"))
        Call Send_Email_Macro
    Next i

End Sub