create variable dynamically from SQL statement

[dharkangel]

Hi,

I have no idea how to do what I currently need to do which is, at a top level, to send a summary email to each user in my database. So the email would look like this

**************************************************
Dear Smith, John

You currently have the following actions due:
Issue ID: 2 - set up meeting with vendor
Issue ID: 125 - Update schedule
Issue ID: 29 - delete outdated meetings
Issue ID: 99 - contact managers for all-hands meeting
*******************************************************

I am using the CDO object, to get more specific I am trying to figure out how to create the body of the email where it shows everything due for a specific user. I already created an SQL statement that gets the data, I just don't know how to build a variable that will hold this data so that I can pass it to the CDO attribute.

Below is a code snippet:

UserName = (Name pulled from a previous query)

MySQL = "SELECT [Issue ID] ,[Title] FROM QPTActions WHERE [Assigned To] = '" & UserName & "';"

ActionsRecordSet.Open MySQL, , adOpenStatic

ActionsRecordSetCount = ActionsRecordSet.RecordCount

'THIS CODE DID NOT WORK BUT IT WAS WORTH A TRY
EmailBody = ""

While Not ActionsRecordSet.EOF
EmailBody = EmailBody & ActionsRecordSet.Fields("Issue ID") & vbCrLf
Wend

'CDO OBJECT
'Set iMsg = CreateObject("CDO.Message")
'With iMsg
'Set .Configuration = iConf
'.To = dynamicEmail
'.To =
'.From = "somewhere@my.com"
'.Subject = "Name: " & UserName
'.HTMLBody = "<font face=helvetica, arial>**THIS IS A SYSTEM GENERATED EMAIL. PLEASE DO NOT REPLY**<br><br>Dear " & UserName & ", <br><br> These are your actions: " & EmailBody
'.Send
'End With
'Set iMsg = Nothing

thank you

 

[PHV]

While Not ActionsRecordSet.EOF
EmailBody = EmailBody & ActionsRecordSet.Fields("Issue ID") & vbCrLf
[!]ActionsRecordSet.MoveNext[/!]
Wend

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886