Good day,
Following code sends out multiple emails (variable i, in this example it is set to 10) to multiple BCC address per email (based on the variable x, in this example it is 40 but can change as it is a variable).
Once an email is sent, I want to delete email addresses from the table before running the loop i again. Something like that
When i = 1
Step 1: send email to 40 BCC email address
Step 2: delete 40 email addresses (the ones sent in step 1) from the table
When i = 2
Step 1: send email to 40 new BCC email address
Step 2: delete 40 email addresses (the ones sent in step 1) from the table
Please advise
Code:
Option Compare Database
Sub Command0_Click()
Dim sFileName As String, sEmail As String, BccEmail As String, eBody As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tbl_Email", dbOpenSnapshot) ' Table Name
eBody = "<HTML><BODY>message</BODY></HTML>"
On Error Resume Next
rst.MoveFirst
Dim i As Integer
Dim x As Integer
For i = 1 To 10 'sending emails individually
sEmail = rst("Email_Address") 'Field Name
CCEmail = sEmail
BccEmail = ""
For x = 1 To 40 'Adding 40 email addreses to BCC
If Not rst.EOF Then
BccEmail = BccEmail & ";" & sEmail
rst.MoveNext
sEmail = rst("Email_Address")
End If
Next x
If BccEmail <> "" Then
Call vcSendEmail_Outlook_With_Attachment("Seeking BA opportunities", "To@yahoo.com", "", BccEmail, "\\fs2\Desktop\personal\Doc.docx", eBody)
Call Wait_Time
End If
Next i
rst.Close
Set rst = Nothing
End Sub