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

Vanaiike

New Member
Joined
Mar 19, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
817
Office Version
  1. 365
Platform
  1. Windows
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

New Member
Joined
Mar 19, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

New Member
Joined
Mar 19, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

@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

New Member
Joined
Mar 19, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jan 27, 2010
Messages
817
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 19, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top