Email reports using 2 addresses for several individuals

[Baldeagle]

In my database for our charity there is a field for email addresses. Most of the contacts have a single email address and we can send them their individual reports without any problem. However a number of the contacts have 2 email addresses and these are stored in the email address field with a semicolon between the 2 addresses. However the system can't send emails to these contacts! Are we storing the addresses in the wrong way? Or is there some reason why Outlook won't regard the 2 addresses as separate email addresses - does it think that it is one strange email address that it doesn't recognise? Any guidance would be appreciated.

[burrina]

If you are sending the emails via a form then yes, I would separate them and then adjust your code accordingly. Personally I would use a ListBox for this.
Perhaps post you code for sending.

[Baldeagle]

Thanks for the response. Below is the code that will send an email to each contact with an attachment (which is a report which is specific to that contact). This coding is called from a button on a form and is designed to automatically send the emails to all selected records. The problem arises where the 'Email' field has 2 email addresses separated by a semicolon. Why should Outlook not issue these particular emails?
Private Sub Command62_Click() Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Dim strEmail As String

strDelim = """" 'Delimiter appropriate to field type.
strDoc = "July Cheque Email"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
strWhere = ""
strDescrip = ""

If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible column.
strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
strEmail = """" & .Column(1, varItem) & ""

End If

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Name of Mission] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Email: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.SendObject acSendReport, strDoc, acFormatPDF, strEmail, , , "Payment from Bangor Worldwide Missionary Convention", "Attached is a Notice of money sent to the Bangor Worldwide Missionary Convention for your Organisation. ", True
DoCmd.Close acReport, strDoc
Next

End With
DoCmd.Close acReport, strDoc


End Sub

[orange]

I agree that it is a good practice to store 1 fact per field. If you design your table to store multiple facts with a known separator, then you could have code to extract the field, separate each fact and use that fact accordingly. That's possible, but your system becomes dependent on your logic and would be considered extremely customized and harder to maintain than well designed tables.

[Baldeagle]

I agree that it is a good practice to store 1 fact per field. If you design your table to store multiple facts with a known separator, then you could have code to extract the field, separate each fact and use that fact accordingly. That's possible, but your system becomes dependent on your logic and would be considered extremely customized and harder to maintain than well designed tables.

Does that mean that 2 email addresses separated by a semicolon will not be read by Outlook properly? If 2 email addresses separated by a semicolon are typed into Outlook (into the 'To' field) the email will go to both. Why would the same not apply if the email is generated via Access?

[ItsMe]

I don't see a semicolon in the following
strEmail = """" & .Column(1, varItem) & ""

If it is in a single field for some addresses then I would continue the convention when you concatenated multiple fields.

[Baldeagle]

I don't see a semicolon in the following
strEmail = """" & .Column(1, varItem) & ""

If it is in a single field for some addresses then I would continue the convention when you concatenated multiple fields.

Not quite sure I understand your point. Both email addresses separated by a semicolon are already in the field that is in 'Column 1' so the code doesn't actually need to concatenate anything.

[ItsMe]

So I see. The loop is for multiple emails. Then why the quotation qualifiers? That is probably the issue. You get to the field that has more than one email and you are wrapping the entire field in quotes.

[Baldeagle]

That sounds as though you may have spotted the problem. But my grasp of Visual Basic is very limited - I have been copying and pasting and adapting in order to get to where I have got to!! Would the following deal with the issue?
strEmail = Column(1, varItem)
or are some of the quotation marks required either for cases where there is only 1 email address or for the limited number of cases where there are 2 addresses? Clarification of this might just solve my problem!

[ItsMe]

I think the first column starts with an index of 0

so
strEmail = Column(1, varItem)

for the second column if your listbox has multiple columns and the email address is in the second column.

[Baldeagle]

I have just tried that but it says that there is a compile error - 'Sub or Function not defined'. Is there something else needed?
I tried it again with the code as it was originally and it actually opens Outlook and shows the 2 email addresses (separated by the semicolon) in the 'To' field. But when it tries to send the email it says 'Microsoft Outlook does not recognise "........ ; ......." (where the dots represent the 2 correct email addresses). Does this throw any more light on the problem?

[ItsMe]

I see why you are using the qualifiers. It is because you are using an alias here
strWhere = "[Name of Mission] IN (" & Left$(strWhere, lngLen) & ")"

If you are going to add this to the To address, you need the " qualifiers to tell the email program what the Display Name is and what part is the actual address.

To maintain this Display Name, you will need to validate whether or not the field has multiple email addresses by using something like the InStr() function.
It would be something like this
strEmail = """" & .Column(1, varItem) & ""
if instr(strEmail, ";") <>0 then

Insert Additional code to parse and add qualifiers

end if

Only thing, this would break when you get to the adding alias thing. So maybe a Boolean to indicate multiple vs. single when it comes to adding the alias.

[Baldeagle]

I'm afraid you have lost me here!! The only thing that is needed for the actual email address element is the single or double email address(es) drawn from the Column 1 field. The column 0 field has the Name of Mission. The use of the field 'Name of Mission' is so that the relevant report is the one that is attached to each email. Does this assist in your understanding of the coding? As I said earlier I am not able to write VB and I don't understand your reference to Boolean. Are you able to go the extra mile and provide me with the coding that you think is necessary to resolve the issue? Sorry to be a nuisance but I think I can see some light at the end of the tunnel!

[ItsMe]

I suggest you get one thing working first. Do a test with
strEmail = Column(1, varItem)

In order to get this to work you will need to eliminate the code that adds the Mission name to the To address and parses the extra "

so comment out some lines of code by using the following. I think I did it correctly

''Remove trailing comma. Add field name, IN operator, and brackets.
'lngLen = Len(strWhere) - 1
'If lngLen > 0 Then
'strWhere = "[Name of Mission] IN (" & Left$(strWhere, lngLen) & ")"
 lngLen = Len(strDescrip) - 2
 If lngLen > 0 Then
 strDescrip = "Email: " & Left$(strDescrip, lngLen)
 End If
'End If

[Baldeagle]

I'm afraid it still doesn't like 'strEmail = Column(1,varItem) - still getting the compile error - 'Sub or Function not defined'