VBA that identifies error on refreshing pivot tables and send notfication by email

[panpluto]

Hey guys,

I want to write a VBA that would do following actions:

1. refresh all pivot tables* in the workbook
2. If one of following actions occur, send a notification via email and close the workbook
a) if the table on server doesn't exist
b) if the table contains more rows that are capabilities of Excel

*pivot tables are connected to external tables on our servers.


I don't want you to write the entire code, I would just ask for couple of advice:
1. Is it possible to do without super advance programming skills
2. How to identify that there is a problem with refreshing table?
3. Could you recommend me any website/(or less favorable book) where I can learn how to create such a VBA.


ad2. Right now, when I use simple code like:

Sub MAJ()ActiveWorkbook.RefreshAll
End Sub

Excel doesn't give me any notification in any situations a) or b). It pretends that it refreshed all tables correctly. So I don't know how to identify that I had problem with refreshing.
On the other hand, when I use "Refresh All" from toolbar on the same file I have notifications like: "Problems obtaining data" and so on.


Thanks in advance for giving me some guidelines how to solve this issue.

Regards,
Matt

 

[panpluto]

I was able to find and change for my purpose one code that send emails.

Sub MAJ()Dim CDO_Mail_Object As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String
Email_Subject = "Error on file  " & ActiveWorkbook.Name
Email_Send_From = "panpluto@gmail.com"
Email_Send_To = "panpluto@gmail.com"
Email_Cc = ""
Email_Bcc = ""
Email_Body = "There was a problem with refreshing Excel.  Path: " & ActiveWorkbook.path & "\" & ActiveWorkbook.Name
Set CDO_Mail_Object = CreateObject("CDO.Message")
On Error GoTo debugs
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'please put your server name below
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "servername"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With
With CDO_Mail_Object
Set .Configuration = CDO_Config
End With
CDO_Mail_Object.Subject = Email_Subject
CDO_Mail_Object.From = Email_Send_From
CDO_Mail_Object.To = Email_Send_To
CDO_Mail_Object.TextBody = Email_Body
'CDO_Mail_Object.cc = Email_Cc 'Use if needed
'CDO_Mail_Object.BCC = Email_Bcc 'Use if needed
'CDO_Mail_Object.AddAttachment FileToAttach 'Use if needed
CDO_Mail_Object.send
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

Now I need somehow identify when Excel has a problem with refreshing the pivot table. This will be more difficult I believe.