BO VBA problem - Can't reference a document from filtered report

[DrSmyth]

Been working at some VBA coding to stick behind a BO report. Basically the coding looks at each value for a document variable called "Manager" filters by this value, saves the report and then it's supposed to e-mail it using the document variable called "E-Mail".

The problem i've got is that the way i've structured the coding means that the e-mail value does not correspond to the manager value (I.E: The reports are being e-mailed to the wrong person.

I think that where i'm going wrong is that i'm referencing the <e-mail> variable in the wrong way....

I've attached the code below, any suggestions about possible fixes or even just how to reference a single document variable value in a filtered report that isn't being used to filter the report (so only the current value is being referenced) would be appreciated.

Sub FilterAndExportMail()

Dim mydoc As Document
Dim myrpt As Report
Dim myFilterVar As DocumentVariable
Dim myFilterVar1 As DocumentVariable
Dim i, intNumChoices As Integer
Dim myFilterChoices As Variant
Dim myFilterChoices1 As Variant
Dim strNextValue As String
Dim strNextValue1 As String
Dim Maildb As Object 'the mail database
Dim username As String
Dim maildbname As String
Dim maildoc As Object
Dim attachme As Object
Dim session As Object
Dim embedobject As Object
Dim mytime As String
Dim recipient As String
Dim subject As String
Dim attachment As String
Dim BodyText As String

' Active (open) Document
Set mydoc = ActiveDocument

' Active (with focus) Report
Set myrpt = ActiveReport

' Put your variable (or query object) here
Set myFilterVar = mydoc.DocumentVariables(&quot;Manager&quot
Set myFilterVar1 = mydoc.DocumentVariables(&quot;E-Mail&quot
' find out how many resort values there are
intNumChoices = UBound(myFilterVar.Values(boUniqueValues))

' collect the number of choices in a variant variable
myFilterChoices = myFilterVar.Values(boUniqueValues)
myFilterChoices1 = myFilterVar1.Values(boUniqueValues)
For i = 1 To intNumChoices

' Get the variable value
strNextValue = myFilterChoices(i)
strNextValue1 = myFilterChoices1(i)

' build filter
myrpt.AddComplexFilter myFilterVar, &quot;=<Manager> = &quot; & &quot;&quot;&quot;&quot; & strNextValue & &quot;&quot;&quot;&quot;

' recompute the report
myrpt.ForceCompute
strNextValue1 = myFilterChoices1(i)

' now export to desired format, using the filter value as part of the name
myrpt.ExportAsRtf (&quot;F:\MI\Wills Mi\Pfs\Wills Not Signed &quot; & strNextValue & &quot;.rtf&quot

'this next bit performs the e-mailing
recipient = strNextValue1
subject = &quot;Wills Not Signed Report&quot;
attachment = &quot;F:\MI\Wills Mi\Pfs\Wills Not Signed &quot; & strNextValue & &quot;.rtf&quot;
BodyText = &quot;Diary&quot;
Set session = CreateObject(&quot;Notes.NotesSession&quot
username = session.username

Set Maildb = session.GETDATABASE(&quot;&quot;, maildbname)

If Maildb.ISOPEN = True Then

Else
Maildb.OPENMAIL
End If

Set maildoc = Maildb.CREATEDOCUMENT
maildoc.Form = &quot;Memo&quot;
maildoc.sendto = recipient
maildoc.subject = subject
maildoc.Body = BodyText


If attachment <> &quot;&quot; Then
Set attachme = maildoc.CREATERICHTEXTITEM(&quot;Attachment&quot
Set embedobject = attachme.embedobject(1454, &quot;&quot;, attachment, &quot;Attachment&quot
End If

maildoc.PostedDate = Now()
maildoc.Send 0, recipient

Set Maildb = Nothing
Set maildoc = Nothing
Set attachme = Nothing
Set session = Nothing
Next i

myrpt.AddComplexFilter myFilterVar, &quot;=(1=1)&quot;
myrpt.ForceCompute

End Sub

 

[Sridharan]

Put a break on

recipient = strNextValue1

and check the value that is there in strNextValue1. Is there a 1-1 relationship between Manager and Email Address.

Sri

 

[DrSmyth]

Yes there is a 1-1 value. I've spoke to the tech people at business objects and they inform me that the reason it isn't working is because strNextValue1 is getting it's value from the dataprovider as opposed to the actually report itself!!!

They have given me some code to find the column value from the report itself and i'm just trying to stick it together now....

Not very good at VBA, so it's taking me a while to figure out.... Dunno if you can help, but the two bits of code are as follows, the first bit remains pretty much unchanged, but the secound part takes the variable (which should be strnextvalue, from an input box and displays it in a message box (Not what i want to do with it, but it's all bo tech support would offer):

Sub FilterAndExportMail()

Dim mydoc As Document
Dim myrpt As Report
Dim myFilterVar As DocumentVariable
Dim myFilterVar1 As DocumentVariable
Dim i, intNumChoices As Integer
Dim myFilterChoices As Variant
Dim myFilterChoices1 As Variant
Dim strNextValue As String
Dim strNextValue1 As String
Dim Maildb As Object 'the mail database
Dim username As String
Dim maildbname As String
Dim maildoc As Object
Dim attachme As Object
Dim session As Object
Dim embedobject As Object
Dim mytime As String
Dim recipient As String
Dim subject As String
Dim attachment As String
Dim BodyText As String

' Active (open) Document
Set mydoc = ActiveDocument

' Active (with focus) Report
Set myrpt = ActiveReport

' Put your variable (or query object) here
Set myFilterVar = mydoc.DocumentVariables(&quot;Manager&quot
Set myFilterVar1 = mydoc.DocumentVariables(&quot;E-Mail&quot
' find out how many resort values there are
intNumChoices = UBound(myFilterVar.Values(boUniqueValues))

' collect the number of choices in a variant variable
myFilterChoices = myFilterVar.Values(boUniqueValues)
myFilterChoices1 = myFilterVar1.Values(boUniqueValues)
For i = 1 To intNumChoices

' Get the variable value
strNextValue = myFilterChoices(i)
strNextValue1 = myFilterChoices1(i)

' build filter
myrpt.AddComplexFilter myFilterVar, &quot;=<Manager> = &quot; & &quot;&quot;&quot;&quot; & strNextValue & &quot;&quot;&quot;&quot;

' recompute the report
myrpt.ForceCompute
strNextValue1 = myFilterChoices1(i)

' now export to desired format, using the filter value as part of the name
myrpt.ExportAsRtf (&quot;F:\MI\Wills Mi\Pfs\Wills Not Signed &quot; & strNextValue & &quot;.rtf&quot

'this next bit performs the e-mailing
recipient = strNextValue1
subject = &quot;Wills Not Signed Report&quot;
attachment = &quot;F:\MI\Wills Mi\Pfs\Wills Not Signed &quot; & strNextValue & &quot;.rtf&quot;
BodyText = &quot;Diary&quot;
Set session = CreateObject(&quot;Notes.NotesSession&quot
username = session.username

Set Maildb = session.GETDATABASE(&quot;&quot;, maildbname)

If Maildb.ISOPEN = True Then

Else
Maildb.OPENMAIL
End If

Set maildoc = Maildb.CREATEDOCUMENT
maildoc.Form = &quot;Memo&quot;
maildoc.sendto = recipient
maildoc.subject = subject
maildoc.Body = BodyText


If attachment <> &quot;&quot; Then
Set attachme = maildoc.CREATERICHTEXTITEM(&quot;Attachment&quot
Set embedobject = attachme.embedobject(1454, &quot;&quot;, attachment, &quot;Attachment&quot
End If

maildoc.PostedDate = Now()
maildoc.Send 0, recipient

Set Maildb = Nothing
Set maildoc = Nothing
Set attachme = Nothing
Set session = Nothing
Next i

myrpt.AddComplexFilter myFilterVar, &quot;=(1=1)&quot;
myrpt.ForceCompute

End Sub

Sub getmail()

Dim cust As String
Dim doc As Document
Dim docs As Documents
Dim dps As DataProviders
Dim dp As DataProvider
Dim cols As Columns
Dim col As Column

'get customer name
cust = InputBox(&quot;Enter a Customer's Surname:&quot;, &quot;Prompt&quot

' set variables
Set doc = ActiveDocument
Set dps = doc.DataProviders
Set dp = dps.Item(1) 'if you have more than 1 dataprovider enter the number of the data provider where the filter data is held
Set cols = dp.Columns
Set col = cols.Item(1) 'in this example the customer field is the first column in the dataprovider, you will need to enter the number of the column that you need to reference

For i = 1 To col.Count 'for each value in the column...

If cust = col.Item(i) Then 'set the filter variable to the current customer's name
Set col = cols.Item(2)
MsgBox col.Item(i)
End If
Set col = cols.Item(1)
Next i 'loop

End Sub

Wish my company would send me on a programming course!!!!!

 

[DrSmyth]

Got it to work.....

Used following code, which i think is dam useful if you have BO and Lotus Notes:

Sub FilterAndExportMail()

Dim myDoc As Document
Dim myrpt As Report
Dim myFilterVar As DocumentVariable
Dim i, intNumChoices As Integer
Dim myFilterChoices As Variant
Dim strNextValue As String
Dim StrNextValue1 As String
Dim Maildb As Object
Dim username As String
Dim maildbname As String
Dim maildoc As Object
Dim attachme As Object
Dim session As Object
Dim embedobject As Object
Dim mytime As String
Dim recipient As String
Dim subject As String
Dim attachment As String
Dim BodyText As String
Dim doc As Document
Dim docs As Documents
Dim dps As DataProviders
Dim dp As DataProvider
Dim cols As Columns
Dim col As Column

' Active (open) Document
Set myDoc = ActiveDocument

' Active (with focus) Report
Set myrpt = ActiveReport

' Put your variable (or query object) here
Set myFilterVar = myDoc.DocumentVariables(&quot;Manager&quot

' find out how many resort values there are
intNumChoices = UBound(myFilterVar.Values(boUniqueValues))

' collect the number of choices in a variant variable
myFilterChoices = myFilterVar.Values(boUniqueValues)

For i = 1 To intNumChoices

' Get the variable value
strNextValue = myFilterChoices(i)


' build filter
myrpt.AddComplexFilter myFilterVar, &quot;=<Manager> = &quot; & &quot;&quot;&quot;&quot; & strNextValue & &quot;&quot;&quot;&quot;

' recompute the report
myrpt.ForceCompute
strNextValue = myFilterChoices(i)

' now export to desired format, using the filter value as part of the name
myrpt.ExportAsHtml (&quot;F:\MI\Wills Mi\Pfs\Wills Not Signed &quot; & strNextValue & &quot;.html&quot



Set dps = myDoc.DataProviders
Set dp = dps.Item(1) 'if you have more than 1 dataprovider enter the number of the data provider where the filter data is held
Set cols = dp.Columns
Set col = cols.Item(1) 'in this example the customer field is the first column in the dataprovider, you will need to enter the number of the column that you need to reference

For j = 1 To col.Count 'for each value in the column...

If strNextValue = col.Item(j) Then 'set the filter variable to the current customer's name
Set col = cols.Item(2)
StrNextValue1 = col.Item(j)
End If
Set col = cols.Item(1)
Next j 'loop
'this next bit performs the e-mailing
recipient = StrNextValue1
subject = &quot;Wills Not Signed Report&quot;
attachment = &quot;F:\MI\Wills Mi\Pfs\Wills Not Signed &quot; & strNextValue & &quot;.rtf&quot;
BodyText = &quot;Wills Not Signed&quot;
Set session = CreateObject(&quot;Notes.NotesSession&quot
username = session.username

Set Maildb = session.GETDATABASE(&quot;&quot;, maildbname)

If Maildb.ISOPEN = True Then

Else
Maildb.OPENMAIL
End If

Set maildoc = Maildb.CREATEDOCUMENT
maildoc.Form = &quot;Memo&quot;
maildoc.sendto = recipient
maildoc.subject = subject
maildoc.Body = BodyText


If attachment <> &quot;&quot; Then
Set attachme = maildoc.CREATERICHTEXTITEM(&quot;Attachment&quot
Set embedobject = attachme.embedobject(1454, &quot;&quot;, attachment, &quot;Attachment&quot
End If

maildoc.PostedDate = Now()
maildoc.Send 0, recipient

Set Maildb = Nothing
Set maildoc = Nothing
Set attachme = Nothing
Set session = Nothing
Next i

myrpt.AddComplexFilter myFilterVar, &quot;=(1=1)&quot;
myrpt.ForceCompute

End Sub