strEmai=.... - how to pick up Email Address field

[Baldeagle]

I have VB code that will create an email but it leaves the email address blank. I want it to pick up the Email Address from the field 'Email Address' in the query 'July Donations to Missions' which is the source of the information in the Report 'July Transfer Email (Reprint)'. I am not very proficient with VB and need some help. I have tried various things to specify the value of strEmail but so far unsuccessfully. Can someone tell me what is required please? My code is

Private Sub Command59_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 Transfer Email (Reprint)"
strEmail=?????

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.SendObject acSendReport, strDoc, acFormatPDF, strEmail, , , "Payment from Bangor Worldwide Missionary Convention", "Attached is a copy of the Email attachment sent to you earlier when we transferred money to your Bank account. ", True
DoCmd.Close acReport, strDoc

DoCmd.Close acReport, strDoc


End Sub

[ranman256]

set rs = currentdb.openrecordset("qsMyQuery")

With rst
   While Not .EOF
        strEmail = .Fields("Email]).Value & ""
        DoCmd.SendObject acSendReport, strDoc, acFormatPDF, strEmail, , , "Payment from Bangor Worldwide Missionary Convention", "Attached is a copy of the Email attachment sent to you earlier when we transferred money to your Bank account. ", True

       .MoveNext
   wend
end with

[Baldeagle]

Thanks for your response. Can you please tell me what I should replace "qsMyQuery" with? My query is called 'July Donations to Missions'. And my email field is called 'Email Address'. How do I fit that into '.Fields("Email]).Value & ""'? I'm sorry to bother you again but I would appreciate it if you could take this a step further for me.

[Baldeagle]

Maybe there was confusion with what my original code was trying to do? The report generated by the code has only one record drawn from the parameter query. I simply need to find a way to automatically select the contents of the 'Email Address' field in order to use it as the value of strEmail so that the 'To' line in the email generated by the penultimate line of my code will be populated with the correct Email address. Can anyone help with this?

[ItsMe]

Are you trying to send multiple Emails to multiple recipients? You will need to loop through a recordset and assign the email value to strEmail=?????

You will want to use code similar to post#2. However, be sure to declare your recordset object


dim rst as DAO.Recordset
set rst = currentdb.openrecordset("qsMyQuery")

With rst

[Baldeagle]

No - it is only one record that it is generated so there is no need for looping.

My problem with the earlier suggestion was that no matter what format I used to replace "qsMyQuery" I couldn't avoid compile errors!! And what do I make strEmail equal to?

Thanks for getting involved.

[ItsMe]

Perhaps this snippet is more intuitive.
https://www.accessforums.net/import-...tml#post235277

[Baldeagle]

Thanks for trying again to help me! But my code actually works to produce the email that I need to send. The only missing element is that it fails to pick up the actual email address from the query that underlies the report. As I understand it, strEmail is what is entered into the 'To' box on the email and because, in my present code, strEmail is not linked to the Email Address field in the 'July Donations to Missions' query then that is why it is left empty. Can someone tell me what I need to put in the code in the line 'strEmail = ????' in place of the question marks in order to pick up the value from the 'Email Address' field in the query? I have tried using quotation marks and/or square brackets but nothing works! What am I missing?

[ItsMe]

This indicates you are working behind a form
Private Sub Command59_Click()

What you can do is set the strEmail variable to equal the field that holds the literal text of the email address (as long as the field you need is within the form's Recordset).
strEmail = Me!NameOfField

[Baldeagle]

Thanks again. I have tried the following at the end of my code -
qsMyQuery = "July Donations to Missions"
strDelim = """" 'Delimiter appropriate to field type.
strDoc = "July Transfer Email (Reprint)"
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
strEmail = Me![EMail Address]
DoCmd.SendObject acSendReport, strDoc, acFormatPDF, strEmail, , , "Payment from Bangor Worldwide Missionary Convention", "Attached is a copy of the Email attachment sent to you earlier when we transferred money to your Bank account. ", True
DoCmd.Close acReport, strDoc

And it says that it can't find the field! But the field is in the query 'July Donations to Missions' on which the report 'July Transfer Email (Reprint)' is based. I'm baffled!! What am I doing wrong??

[June7]

The Me qualifier is alias reference to the object that the code is behind. If code is behind form, it is looking at the form for the email.

Or try:

strEmail = Reports![July Transfer Email (Reprint)]![EMail Address]

[Baldeagle]

Excellent!! That's solved it for me! It now does what is required!! Many thanks to all of you who have contributed.