Passing an argument from a query to a Macro

[benforum via AccessMonster.com]

I am trying to send a report using an email address in a DB. I wrote the
following code for it.
Private Sub Reminder_Letter_Command_Click()
On Error GoTo Err_Reminder_Letter_Command_Click

Dim stDocName As String
Dim SendTO As String


stDocName = "BG Reminder Letter"

RunQuery

MsgBox "This is After RunQuery " & SendTO
SendTO = [Queries]![BG email only from email from ae code].EmailAddress
MsgBox "This is SendTO 2 Value ==== " & SendTO

DoCmd.OpenReport stDocName, acViewNormal
DoCmd.SendObject acSendReport, stDocName, , SendTO



Exit_Reminder_Letter_Command_Click:
Exit Sub

Err_Reminder_Letter_Command_Click:
MsgBox Err.Description
Resume Exit_Reminder_Letter_Command_Click

End Sub

The query runs and produces the correct email address, however when I try to
put it into a string I receive an error message. The email field is defined
in the DB as text.

What am I doing wrong? How to correct the code?

 

[Steve Schapel]

Benforum,

First of all, this is not a macro, it is a VBA procedure.

I do not understand what 'RunQuery' means in this context. Do you have
a user-defined function called RunQuery in your database? What does it
do? What is the query?

[Queries]![BG email only from email from ae code].EmailAddress is
incorrect syntax, and will not be recognised. Ther is no Queries
collection like that. Even if there was, you would need to indicate
which record in the query you want the value from.

 

[benforum via AccessMonster.com]

Benforum,

First of all, this is not a macro, it is a VBA procedure.

I do not understand what 'RunQuery' means in this context. Do you have
a user-defined function called RunQuery in your database? What does it
do? What is the query?

[Queries]![BG email only from email from ae code].EmailAddress is
incorrect syntax, and will not be recognised. Ther is no Queries
collection like that. Even if there was, you would need to indicate
which record in the query you want the value from.

I am trying to send a report using an email address in a DB. I wrote the
following code for it.

[quoted text clipped - 31 lines]

What am I doing wrong? How to correct the code?

I have a userdefined function that activates and runs the query.
The query returns exactly one record so there is no need to specify the
record id.

So what is the correct sintax?

 

[Steve Schapel]

Benforum,

Can you please post back with the SQL view of the query that is run by
the RunQuery function? And also the SQL view of the 'BG email only from
email from ae code' query? And also the details of the RunQuery
function? Thank you, I think that will help us understand what you are
trying to do.

 

[benforum via AccessMonster.com]

Benforum,

Can you please post back with the SQL view of the query that is run by
the RunQuery function? And also the SQL view of the 'BG email only from
email from ae code' query? And also the details of the RunQuery
function? Thank you, I think that will help us understand what you are
trying to do.

Public Sub RunQuery()

DoCmd.OpenQuery "BG email only from email from ae code"

End Sub


Private Sub Reminder_Letter_Command_Click()
On Error GoTo Err_Reminder_Letter_Command_Click

Dim stDocName As String
Dim SendTO As String


stDocName = "BG Reminder Letter"
MsgBox "This is stDocName Value " & stDocName
RunQuery

MsgBox "This is After RunQuery " & SendTO
Query = "Select EmailAddress FROM [BG email only from email from ae
code]Where AEcode ="
SendTO = [Queries]![BG email only from email from ae code].EmailAddress
MsgBox "This is SendTO 2 Value ==== " & SendTO

DoCmd.OpenReport stDocName, acViewNormal
DoCmd.SendObject acSendReport, stDocName, , SendTO



Exit_Reminder_Letter_Command_Click:
Exit Sub

Err_Reminder_Letter_Command_Click:
MsgBox Err.Description
Resume Exit_Reminder_Letter_Command_Click

End Sub


Here is the SQL code of [BG email only from email from ae code]

SELECT [BG Email from AECode].EmailAddress, [BG Email from AECode].[Associate
Editor code]
FROM [BG Email from AECode];

and also the SQL of [BG Email from AECode]

SELECT [People Information A].EmailAddress, [BG New Active Papers].[Paper No],
[BG New Active Papers].[Associate Editor code]
FROM [BG New Active Papers] INNER JOIN [People Information A] ON [BG New
Active Papers].[Associate Editor code] = [People Information A].RecID
WHERE ((([BG New Active Papers]![Paper No])=[Forms]![BG Paper Info]![Paper No]
));

 

[Steve Schapel]

Benforum,

Thanks a lot for this information, which is very helpful.

The first thing to note is that the RunQuery procedure relates to [BG
email only from email from ae code] which is a *select* query. I had
assumed it would be an action query of some sort. I can't see any
purpose for this, and it can be removed from consideration.

It then appears that the above query simply reflects the data returned
by the [BG Email from AECode] query. So apparently the EmailAddress
field returned by this query is the one you want. This could be
retrieved using a DLookup function.

So, it seems to me your code can be reduced to this:

Private Sub Reminder_Letter_Command_Click()
Dim SendTo As String
SendTo = DLookup("[EmailAddress]","BG Email from AECode")
DoCmd.SendObject acSendReport, "BG Reminder Letter", , SendTo
End Sub

 

[benforum via AccessMonster.com]

Thanks Steve it worked.

Perhaps you can help me with another question.

I would like instead of emailing the letter as an attachement, to send it as
part of the email message (i.e. the text in the letter appearing in the email)
, how can I do it, assuming that the text is in rtf, and is much longer than
255Characters.

Thanks again for your help.

Ben

Benforum,

Thanks a lot for this information, which is very helpful.

The first thing to note is that the RunQuery procedure relates to [BG
email only from email from ae code] which is a *select* query. I had
assumed it would be an action query of some sort. I can't see any
purpose for this, and it can be removed from consideration.

It then appears that the above query simply reflects the data returned
by the [BG Email from AECode] query. So apparently the EmailAddress
field returned by this query is the one you want. This could be
retrieved using a DLookup function.

So, it seems to me your code can be reduced to this:

Private Sub Reminder_Letter_Command_Click()
Dim SendTo As String
SendTo = DLookup("[EmailAddress]","BG Email from AECode")
DoCmd.SendObject acSendReport, "BG Reminder Letter", , SendTo
End Sub

Public Sub RunQuery()

[quoted text clipped - 46 lines]

WHERE ((([BG New Active Papers]![Paper No])=[Forms]![BG Paper Info]![Paper No]
));

 

[Steve Schapel]

Ben,

I am not sure, I have never done this myself. But I was under the
impression that the 255 character limit does not apply if using VBA.
Therefore, just give it a try like this:

Private Sub Reminder_Letter_Command_Click()
Dim SendTo As String
Dim MsgText As String
SendTo = DLookup("[EmailAddress]","BG Email from AECode")
MsgText = Me.Message
DoCmd.SendObject acSendNoObject, , , SendTo, , , , MsgText, False
End Sub

(This implies you have a textbox named Message on the same form as where
you are calling the code from.)
I am not sure about RTF - once again, just never done it this way myself.

 

[benforum via AccessMonster.com]

Steve,

The MsgText is not on the form, it is the first record in a query called [BG
Reminder Query], the query has many fields and only a subset of them:
[Message Subject] , [Message Text], [Authors], [Paper Id] are used in the
email message, they are concateneted to form the MsgText.

What is the command that retireves the fields from the Query, and puts them
into MsgText?

Ben

Ben,

I am not sure, I have never done this myself. But I was under the
impression that the 255 character limit does not apply if using VBA.
Therefore, just give it a try like this:

Private Sub Reminder_Letter_Command_Click()
Dim SendTo As String
Dim MsgText As String
SendTo = DLookup("[EmailAddress]","BG Email from AECode")
MsgText = Me.Message
DoCmd.SendObject acSendNoObject, , , SendTo, , , , MsgText, False
End Sub

(This implies you have a textbox named Message on the same form as where
you are calling the code from.)
I am not sure about RTF - once again, just never done it this way myself.

Thanks Steve it worked.

[quoted text clipped - 4 lines]

, how can I do it, assuming that the text is in rtf, and is much longer than
255Characters.

 

[Steve Schapel]

Ben,

You could use the DLookup function again to get this data into your code.

However, if it was mine, I wouldn't do it that way, given that there are
multiple fields to retrieve. I would make a form bound to the [BG
Reminder Query]. If you can wangle it so this is the form you are
running your code from (i.e. the form that contains the Reminder Letter
Command command button), then your code can get the values you need for
the message directly by referencing the controls on the form. Make sense?

 

[benforum via AccessMonster.com]

Steve,

I used DFirst and it did the job.

Thanks for your help.

Ben

Ben,

You could use the DLookup function again to get this data into your code.

However, if it was mine, I wouldn't do it that way, given that there are
multiple fields to retrieve. I would make a form bound to the [BG
Reminder Query]. If you can wangle it so this is the form you are
running your code from (i.e. the form that contains the Reminder Letter
Command command button), then your code can get the values you need for
the message directly by referencing the controls on the form. Make sense?

Steve,

[quoted text clipped - 5 lines]

What is the command that retireves the fields from the Query, and puts them
into MsgText?