I do have one report with macro. Previously it was runnign fine with one Private Sub (). This macro is suppose to run after refresh. So I have after refresh adn code is place under thisdocument. But when I go to Tools --> macro —> letter t is comign as macro name. if I click on edit button it will open a blank modeule. I have palced my code under this document. Am i doing soemthign wrong here…
I added one more procedure in bottom and now I am callign that in first.
When I click on refresh button of report. It is refreshign well and then after macro is being lunched but looks like when it is calling first time Emailsend procedue, it is getting hang.
Please help me out here
Private Sub Document_AfterRefresh()
On Error GoTo error_handler
'Define Variables*******
Dim Doc As Document
Dim Rep As Report
Dim i, j, k, m, n As Integer
Dim t As Integer
Dim ExcelDoc As String 'Stores the name of the Excel document to copy all report tabs to
Dim OutputPath, InputFilePath, StagingPath, Root, LogFilePath As String 'Stores the path in while the Excel files are created.
Dim myFilterVar As DocumentVariable
Dim e, intNumChoices As Integer
Dim myFilterChoices As Variant
Dim strNextValue As String
Call EmailSend(XYZ)
myvariable = “Ram”
LogTitleFlag = “Yes”
'Save report tabs as text files
Set Doc = ActiveDocument
Set myFilterVar = Doc.DocumentVariables(“Ram”)
intNumChoices = UBound(myFilterVar.Values(boUniqueValues))
myFilterChoices = myFilterVar.Values(boUniqueValues)
For e = 1 To intNumChoices
StartTime = Time
Set Rep = Doc.Reports.Item(1)
' Get the variable value
strNextValue = myFilterChoices(e)
ErrorText = ("Error in Process - Detail is printed below: " & vbCrLf & "Report Name = " & Rep.Name & vbCrLf & "Ram = " & strNextValue & vbCrLf)
Flag = "No"
For j = 0 To k
If strNextValue = ManagerSSOIDVar(j) Then
Flag = "Yes"
Exit For
End If
Next j
If Flag = “Yes” Then
MkDir (StagingPath & strNextValue)
DirName = (StagingPath & strNextValue & “”)
ExcelDoc = (strNextValue & “_” & mDate)
' build filter
Rep.AddComplexFilter myFilterVar, "= <SSOID4> = " & """" & strNextValue & """"
' recompute the report
Rep.ForceCompute
If Dir(DirName & Rep.Name & “.htm”) <> " " Then
Kill DirName & Rep.Name & “.htm”
End If
Rep.ExportAsHtml (DirName & Rep.Name & “.htm”)
Set vbExcel = CreateObject(“Excel.Application”)
With vbExcel
'Create a new workbook to import text files into
If Dir(DirName & strNextValue & " For " & mDate & “.xls”) <> " " Then
Kill DirName & strNextValue & " For " & mDate & “.xls”
End If
.Workbooks.Add
.ActiveWorkbook.SaveAs DirName & strNextValue & " For " & mDate & “.xls”
error_handler:
If Err.Number = 53 Then Resume Next
Public Sub EmailSend(Body As String, Subject As String, LogFilePathName As String, MailTo As String)
'objTextStream.WriteLine (“Called Email Function.” & vbCrLf)
'Please enter sender’s e-mail address.
'Please enter receiver’s e-mail address.
'Use semicolon between names in the receiver list.
'Please enter e-mail message.
'Body = “The Process has Failed.”
'Subject = “Error Reported in Process”
Set OlkApp = CreateObject(“Outlook.Application”)
Set NewMail = OlkApp.CreateItem(olMailItem)
Set SafeMail = CreateObject(“Redemption.SafeMailItem”) ‘’
Set SafeMail.Item = NewMail ‘’
Set Attachments = NewMail.Attachments
'PromptDialog.Hide
Attachments.Add (LogFilePathName)
With SafeMail
.To = MailTo
.Body = (Body & vbCrLf & vbCrLf)
.Subject = Subject
.Importance = 1
.Send
End With
Set OlkApp = Nothing
End Sub
045331 (BOB member since 2004-01-23)