VBA Loop that sends emails via Outlook with a PDF attachment is being considered "spam"

[Vanaiike]

Hello,
I have a pivot table in Excel.
I created a loop that selects 1 filter of the pivot table.
For each loop it creates a PDF on my desktop, then takes it as an attachment, sends an email in outlook, deletes the PDF, then goes to the next filter on the pivot table.
Its probably sending 300 to 400 emails.
It works great.

The problem i am having is that once the email is in outlook, i believe the email servers (either mine or the recipients) is not delivering all the emails because it thinks its spam.
Who sends so many emails... with an attachment... SPAM People.

I tried to add a wait period on the loop, not sure if it did it properly, but i don't see the excel to outlook slowing down.


Sub Loop_PivotItems()

Dim FileName As String
Dim WSHShell As Object
Dim DesktopPath As String
Set WSHShell = CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")
Set WSHShell = Nothing


'Loop through every PivotItem in the PageField (Filter) of the Pivot Table
For Each PivotItem In ActiveSheet.PivotTables(1).PageFields("Customer for statements").PivotItems
'Select the PivotItem
ActiveSheet.PivotTables(1).PageFields("Customer for statements").CurrentPage = PivotItem.Value

'Do whatever you need here....
If ActiveWindow.SelectedSheets.Count > 1 Then
MsgBox "There is more then one sheet selected," & vbNewLine & _
"ungroup the sheets and try the macro again"
Else
'For a fixed range use this line
FileName = RDB_Create_PDF(Range("A:I"), DesktopPath & "\" & Range("J1").Value, True, False)

If FileName <> "" Then
RDB_Mail_PDF_Outlook FileName, Range("J2").Value, Range("J1").Value, _
Range("J3").Value _
& vbNewLine & Range("J4").Value, True
Else
MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
"Microsoft Add-in is not installed" & vbNewLine & _
"You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
"The path to Save the file in arg 2 is not correct" & vbNewLine & _
"You didn't want to overwrite the existing PDF if it exist"
End If
End If

Application.Wait (Now + TimeValue("0:00:03"))

Kill FileName

Application.Wait (Now + TimeValue("0:00:03"))

Next

MsgBox "Mail send - thanks ", vbInformation, "We are going global"


End Sub


------------------

Here is the message error i am getting. from outlook.

Your message did not reach some or all of the intended recipients.
The following recipient(s) cannot be reached:

'xxxxx@xxxx.com' on 3/19/2020 5:37 PM

554 6.6.0 Error sending message for delivery.

 

[6StringJazzer]

Waiting 6 seconds between emails isn't going to make any difference to your problem.

The bounce notice is a generic message. It looks to me like the recipient's SMTP server bounced it. I think you would get a different message if your own SMTP server refused to send it. Are you getting this for all emails or just some of them? It is possible that you got yourself black-listed. I would contact your ISP and tell them what's happening. They can either help you fix it or tell you why you can't do this.

 

[mole999]

3-400 emails will be considered spam by a number of services, and with auto blacklisting once there its not going to get through. You could look at the bounce backs and see if any that didn't get through can be sent an ordinary mail, and then with a PDF attachment, though you will need forgiving end users for being spammed with multiple emails

 

[Vanaiike]

The problem i have is i don't feel the 6 sec is coded correctly.
I feel its not happening between each email, because i see the emails going in the outbox of outlook way too fast.
I may have not put the "application wait" in the loop.

 

[Vanaiike]

@6

Waiting 6 seconds between emails isn't going to make any difference to your problem.

The bounce notice is a generic message. It looks to me like the recipient's SMTP server bounced it. I think you would get a different message if your own SMTP server refused to send it. Are you getting this for all emails or just some of them? It is possible that you got yourself black-listed. I would contact your ISP and tell them what's happening. They can either help you fix it or tell you why you can't do this.

I am only getting in for some messages.
No pattern on which ones.
Also some message get the return message, but there is a draft created on the YAHOO webmail ...
Also some get the email but i get the message.

I think i just need maybe 10 secs or even 30 seconds and run it for 1hr, so the system thinks its a "regular" humain doing it.
But the code wait does not seem to be a the good spot.
Because i don't see a slow down even if i put 30 secs

 

[Vanaiike]

3-400 emails will be considered spam by a number of services, and with auto blacklisting once there its not going to get through. You could look at the bounce backs and see if any that didn't get through can be sent an ordinary mail, and then with a PDF attachment, though you will need forgiving end users for being spammed with multiple emails

The email changes everytime. So each individual gets one at a time.
I'm sending customer statements weekly.

 

[6StringJazzer]

I think i just need maybe 10 secs or even 30 seconds and run it for 1hr, so the system thinks its a "regular" humain doing it.
But the code wait does not seem to be a the good spot.
Because i don't see a slow down even if i put 30 secs

The code looks OK to me. It should wait 6 seconds every time through the loop. Can you show the code for RDB_Mail_PDF_Outlook ? If you are sending these through Outlook, maybe OUtlook is queueing them and sending them all at once, rather than sending each one when your code creates the email.

 

[Vanaiike]

I did a wait of 30 seconds. and it worked.
So i'll just bring it down on time until i get the errors messages.
Thanks