Email Address Truncation Issue 1

[MikeC14081972]

I have an access db (2000) which is used for sending emails via Lotus Notes.

I'm building a string of email address from a recordset based on a table.

When passing the string (SendTo) to Lotus notes using

objNotesDoc.ReplaceItemValue "SendTo", SendTo

The string is being cut short. However if I step through the code and after the string has been compiled i view the string

?SendTo

The complete string is shown. I now it's not an issue with the length of string allowed by Notes as I can cut and paste the string from the Imediate window to Notes with no issues.

Anyone got any ideas?????

Thanks

 

[Remou]

Is it possible that there are "gaps" in the string? Outlook can get upset with, for example:

Name;;Name

 

[MikeC14081972]

Remou thanks for the quick reply.

Yes the last email address being truncated does have spaces.

Any idea how to get round this?

 

[Remou]

I am not sure, but Trim or Replace may suit.

 

[MikeC14081972]

Tried those to no avail

 

[Remou]

Can you post a short sample string with a little code? You will need to replace the actual addresses with something innocuous, a@a.a, for example.

 

[MikeC14081972]

Here's the code that builds the string

strRecipient = "SELECT DISTINCT tbl_FMBooks.Notifications " _
                      & "FROM (tbl_FMEntity INNER JOIN tbl_FMBooks ON tbl_FMEntity.Level7Name = tbl_FMBooks.Level7) " _
                      & "INNER JOIN tbl_Holdings ON tbl_FMBooks.Book_Number = tbl_Holdings.Book_Number " _
                      & "WHERE (((tbl_FMEntity.Level7Name)= '" & rsBusArea.Fields("level7name") & "') AND " _
                      & "((tbl_Holdings.[Notification ID])='" & NtID & "'))"
        
        Set rsRecipient = db.OpenRecordset(strRecipient)
        
        strRecipient = ""
        
        Do While (Not rsRecipient.EOF)
            If SendTo <> "" Then
                If Right(SendTo, 1) = "," Then
                    SendTo = SendTo & rsRecipient.Fields(0)
                Else
                    SendTo = SendTo & "," & rsRecipient.Fields(0)
                End If
            Else
                SendTo = rsRecipient.Fields(0)
            End If
  rsRecipient.MoveNext
        Loop
        
        If Right(SendTo, 1) <> "," Then
            SendTo = SendTo & ","
        End If

Email address are taken from a table, will changing these help incase the address is the issue?

 

[Remou]

I am not sure why you need the final comma. In Outlook addresses are separated by semi-colons.

I can only guess at the cause of the problem. It would be worth trying with a set, sample list or a very small recordset (SELECT Top 10).

 

[MikeC14081972]

The issue was occuring without the comma, but that in as a trial to see if made a difference just haven't removed it yet.

Have come up with a manual work around for now and will post back if I find a solution.

Thanks for your help, have a star