Ok, basically i've had to re-jig all of the code and I've come up with this... The problem basically was that the e-mail address was being looked up from the entire list of e-mail addresses in an alphabetical order and not the corresponding e-mail address that tied in with the main variable i was using to produce the report, and what i now have is this:
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("Manager"
' 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, "=<Manager> = " & """" & strNextValue & """"
' recompute the report
myrpt.ForceCompute
strNextValue = myFilterChoices(i)
' now export to desired format, using the filter value as part of the name
myrpt.ExportAsHtml ("F:\MI\Wills Mi\Pfs\Wills Not Signed " & strNextValue & ".html"
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 = "Wills Not Signed Report"
attachment = "F:\MI\Wills Mi\Pfs\Wills Not Signed " & strNextValue & ".rtf"
BodyText = "Wills Not Signed"
Set session = CreateObject("Notes.NotesSession"
username = session.username
Set Maildb = session.GETDATABASE("", maildbname)
If Maildb.ISOPEN = True Then
Else
Maildb.OPENMAIL
End If
Set maildoc = Maildb.CREATEDOCUMENT
maildoc.Form = "Memo"
maildoc.sendto = recipient
maildoc.subject = subject
maildoc.Body = BodyText
If attachment <> "" Then
Set attachme = maildoc.CREATERICHTEXTITEM("Attachment"
Set embedobject = attachme.embedobject(1454, "", attachment, "Attachment"
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, "=(1=1)"
myrpt.ForceCompute
End Sub
Which works
