query returns data but module uses primary key

[lotarugg]

Hi,

I have a module that uses a query to get a list of email addresses from a table. Just running the query returns the email address - great. However the module uses the record ID of email address when placing it in the 'to' field in Outlook - so I just get a number there when i need an email address. Why does it pull the key when it shows the data when using just the query? I think this is something simple in the structure of my database that I'm missing. I've removed all lookup fields at table level. Any other areas I might try?

Many thanks

 

[pbaldy]

Hard to say why the code might return an ID without seeing the code.

 

[lotarugg]

Good point -

--------------------------------------------------

Public Function SendEMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

Set fso = New FileSystemObject


Subjectline$ = InputBox$("A query has been added for your attention.", _
"We Need A Subject Line!")


If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If

BodyFile$ = InputBox$("C:\email.txt")

If BodyFile$ = "" Then
MsgBox "No body, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain��t Got No-Body!"
Exit Function
End If

If fso.FileExists(BodyFile$) = False Then
MsgBox "The body file isn��t where you say it is. " & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain��t Got No-Body!"
Exit Function
End If


Set MyBody = fspenTextFile(BodyFile, ForReading, False, TristateUseDefault)

MyBodyText = MyBody.ReadAll

MyBody.Close

Set MyOutlook = New Outlook.Application

Set db = CurrentDb()

Set MailList = db.OpenRecordset("MyEmailAddresses")

Do Until MailList.EOF

Set MyMail = MyOutlook.CreateItem(olMailItem)

MyMail.To = MailList("email")


MyMail.Subject = Subjectline$


MyMail.Body = MyBodyText


MyMail.Send

MailList.MoveNext

Loop

Set MyMail = Nothing

Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

 

[pbaldy]

Offhand that looks okay. I assume this is the offending line:

MyMail.To = MailList("email")

and that MyEmailAddresses is your query, and email is the field containing an email address? And there's no lookup field involved here? Can you post the db here, or a representative sample?

 

[lotarugg]

Yes to all however there is a lookup at the form level which saves the selected value into a field on the table being queried. The relationship is field to field not field to primary key. This is a relational database so surely I don’t have to do away with all relationships?

I’m clearly deeply misunderstanding something here.

Hard to post as so much work sensitive data not just in tables but on forms. I will try and get one cleared out and export the main parts.

Thanks for all help

 

[lotarugg]

I'm starting to think this won't work using a lookup table, but I have to ;,(

 

[lotarugg]

Replaced lookup column with list - not ideal but it works for now.