asked on

Editing an agent that sends Newsletter

Experts,

I have the following code that will send a newsletter email, based on what is in the Interest Profile (taken from a tutorial book titled Notes 6 Projects). This works great -- and it uses the fields from one document, but I want the agent to search on other fields in other documents and return one newsletter with all docs that fit the criteria in the fields in the Interest Profile. So, I want to know where in the code I have to adjust to add more fields from other documents. I don't see a reference to a form, so I think this is doable, so long as I add the new fields in the Interest Profle -- correct?

Any suggestions? (I want to add like 6 more fields from two other documents.)

Thanks in advance,
AV

Start
===================================================

Sub Initialize
      'Declaration
      Dim ns As New NotesSession
      Dim db As NotesDatabase
      Dim ProfileView As NotesView
      Dim ProfileDoc As NotesDocument
      Dim NotesDoc As NotesDocument
      Dim NewsLet As NotesNewsletter
      Dim DocCollection As NotesDocumentCollection

      Dim ByAuthor As Variant
      Dim ByCategory As Variant
      Dim BySubject As Variant
      Dim ByClassification As Variant
      Dim ByLocation As Variant

      'Declaration of variable, to form a formatted string for search query
      Dim strAuthor As String
      Dim strCategories As String
      Dim strSubject As String
      Dim strClassification As String
      Dim strLocation As String
      Dim IsCriteriaNull As Variant
      Dim SearchQry As String

      'Initialization
      Set db = ns.CurrentDatabase
      Set ProfileView = db.GetView("(InterestProfile)")
      Set ProfileDoc = ProfileView.GetFirstDocument

      If ProfileDoc Is Nothing Then Exit Sub      'if no user profiles then quit the process

      Do While Not (ProfileDoc Is Nothing)
            If db.IsFTIndexed = True Then            'If Db is Full text Index do Ft search
                  If Trim(ProfileDoc.CreatedByDiscuss(0)) <> "" Then
                        ByAuthor = ProfileDoc.CreatedByDiscuss
                        strAuthor = ""
                        Forall value In ByAuthor            'Iterate through the Values in the Authors field
                              If Trim(strAuthor) = "" Then
                                    strAuthor = value
                              Else
                                    strAuthor = strAuthor & "," & value
                              End If
                        End Forall
                        SearchQry = "field CreatedByDiscuss contains " & strAuthor
                  End If

                  If Trim(ProfileDoc.CategoryDiscuss(0)) <> "" Then
                        ByCategory = ProfileDoc.CategoryDiscuss
                        strCategories = ""
                        Forall value In ByCategory
                              If Trim(strCategories) = "" Then
                                    strCategories = value
                              Else
                                    strCategories = strCategories & "," & value
                              End If
                        End Forall
                        qry = "field CategoryDiscuss contains (" & strCategories & ")"
                        If Trim(SearchQry) = "" Then
                              SearchQry = qry
                        Else
                              SearchQry = SearchQry & " Or " & qry
                        End If
                  End If

                  If Trim(ProfileDoc.SubjectDiscuss(0)) <> "" Then
                        BySubject = ProfileDoc.SubjectDiscuss
                        strSubject = ""
                        Forall value In BySubject
                              If Trim(strSubject) = "" Then
                                    strSubject = value
                              Else
                                    strSubject = strSubject & "," & value
                              End If
                        End Forall
                        qry = "field SubjectDiscuss contains (" & strSubject & ")"
                        If Trim(SearchQry) = "" Then
                              SearchQry = qry
                        Else
                              SearchQry = SearchQry & " Or " & qry
                        End If
                  End If

                  If Trim(ProfileDoc.ClassificationDiscuss(0)) <> "" Then
                        ByClassification = ProfileDoc.ClassificationDiscuss
                        strClassification = ""
                        Forall value In ByClassification
                              If Trim(strClassification) = "" Then
                                    strClassification = value
                              Else
                                    strClassification = strClassification & "," & value
                              End If
                        End Forall
                        qry = "field ClassificationDiscuss contains (" & strClassification & ")"
                        If Trim(SearchQry) = "" Then
                              SearchQry = qry
                        Else
                              SearchQry = SearchQry & " Or " & qry
                        End If
                  End If

                  If Trim(ProfileDoc.LocationDiscuss(0)) <> "" Then
                        ByLocation = ProfileDoc.LocationDiscuss
                        strLocation = ""
                        Forall value In ByLocation
                              If Trim(strLocation) = "" Then
                                    strLocation = value
                              Else
                                    strLocation = strLocation & "," & value
                              End If
                        End Forall
                        qry = "field LocationDiscuss contains (" & strLocation & ")"
                        If Trim(SearchQry) = "" Then
                              SearchQry = qry
                        Else
                              SearchQry = SearchQry & " Or " & qry
                        End If
                  End If

                  If Trim(SearchQry) <> "" Then
                        SearchQry = SearchQry & " and (not(field Form contains InterestProfile))"
                        Print SearchQry
                        Set DocCollection = db.UnprocessedFTSearch(SearchQry, 0)
                  End If
            Else            'Formula Search code starts from here................

                  IsCriteriaNull = True            'Intialize it to True assumes the Search Criteria is null
                  If Trim(ProfileDoc.CreatedByDiscuss(0)) <> "" Then
                        IsCriteriaNull = False      'Flag set to false, i.e Search Criteria is not Null, to allow the search as some criteria is available to carry on search
                        ByAuthor = ProfileDoc.CreatedByDiscuss
                        strAuthor = ""                  're-intializing
                        Forall value In ByAuthor
                              st = """" & value & """"      'Formating of the string such as it would be Enclosed by a Double quotes e.g "Sashi"
                              If Trim(strAuthor) = "" Then
                                    strAuthor = st
                              Else
                                    strAuthor = strAuthor & ":" & st      'Formating the string to create a Stringlist delimited with Semi colon : e.g "Sashi":"Sanjeev":"Praveen"
                              End If
                        End Forall
                  End If

                  If Trim(ProfileDoc.CategoryDiscuss(0)) <> "" Then
                        IsCriteriaNull = False
                        ByCategory = ProfileDoc.CategoryDiscuss
                        strCategories = ""
                        Forall value In ByCategory
                              st = """" & value & """"
                              If Trim(strCategories) = "" Then
                                    strCategories = st
                              Else
                                    strCategories = strCategories & ":" & st
                              End If
                        End Forall
                  End If
=========================================
End

ASKER CERTIFIED SOLUTION

marilyng

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sjef Bosman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AliciaVee

ASKER

marilyng,

Yes, your comments do make sense, and I agree that this code should be split. I get what you are saying - let me look closer at it and see what I can figure out.

sjef,

The database has a discussion board, and the interest profile uses fields in the form that are used to post comments (these equal to 3 forms, main topic, response, and response to response). This database also has other forms that will hold other data for reference (like a document library), so if a users creates an interest profile and chooses to be notified whenever a document of category "Education" or category "Mentoring" or category "Volunteering" is posted, I'd also like the newsletter to provide other documents that may have the same categories defined. Is that possible?

Sjef Bosman

Ah, I get it. But isn't that more or less the same as a Database Subscription that's already present in Notes?

Look in the Designer Help database, the Index, under Subscriptions: Enabling Subscriptions

marilyng

Well, of course. Duh. forgot about those.

AliciaVee

ASKER

marilyng and sjef,

Sorry I haven't been back here to close this post out. I did finish what I needed with the help of both of your input. Funny, Help has a lot of information, but sometimes I don't know how to search for it. It is very senstive to what you type and I get a lot of invalid searches. Maybe I should use the Index more often! Good idea.

Thanks again for all of your help.
AliciaV