Emailing from a Access Module using variables 1

[platypus71]

Okay, I looked through the forums and I'm left scratching my head in confusion. I've never written any VB script and have had very little exposure to it.

Here is what I want to accomplish:
Send email to anyone listed as having a class tomorrow.
I've setup a Query that takes the three tables the information comes from, filters down to just the people who should get the email and has all information I need.

Here is my query:

SELECT ReconReport.EventID, Courses.CourseID, Courses.CourseName, ReconReport.StartDate, ReconReport.EndDate, ReconReport.Location, ReconReport.Time, Roster.[User ID] AS UserID, Roster.Status
FROM (Courses INNER JOIN ReconReport ON Courses.CourseID = ReconReport.CourseID) INNER JOIN Roster ON ReconReport.EventID = Roster.EventID
WHERE (((ReconReport.StartDate)>(Now()+1) And (ReconReport.StartDate)<(Now()+2)) AND ((Roster.Status)="enrolled"));

What I need is the VB code to write an email that does the following:
Send individual message to everyone who shows up in the UserID field from the query.
The subject of the message should be "REMINDER: You have a class tomorrow"
The body of the message should read:
This is just a reminder that you have (CourseName) beginning on (StartDate) and ending on (EndDate) at (Location) from (Time).

Since we aren't using Outlook, I have special email code to allow me to send the email. My email code uses this function: SendEmail(strTo, strMessage, strAttachment, strSubject, strBCC). So basically, what I need is to create a strTo and strMessage based on the query for each instance of UserID from the query.

As I said, I have not had any success writing my own VB script, so any help would be appreciated.

Thanks.

 

[PWise]

dim mydb as database
dim rst as recordset
dim sqlstr as string
dim sbj as string
dim bod as string
sqlstr = "SELECT ReconReport.EventID, Courses.CourseID, Courses.CourseName, ReconReport.StartDate, ReconReport.EndDate, ReconReport.Location, ReconReport.Time, Roster.[User ID] AS UserID, Roster.Status
FROM (Courses INNER JOIN ReconReport ON Courses.CourseID = ReconReport.CourseID) INNER JOIN Roster ON ReconReport.EventID = Roster.EventID
WHERE (((ReconReport.StartDate)>(Now()+1) And (ReconReport.StartDate)<(Now()+2)) AND ((Roster.Status)="enrolled"));"
sbj="REMINDER:  You have a class tomorrow"
set mydb =currentdb
set rst = mydb.openrecordset(sqlStr)
do while not rst.eof
bod="This is just a reminder that you have " & rst!CourseName & " beginning on " & rst!StartDate & "  and ending on " & rst!EndDate & "  at " & rst!Location & "  from " & rst!Time & " .


SendEmail(rst!email, bod, strAttachment, sbj, strBCC)
rst.movenext
loop

rst!email should be your email address

 

[platypus71]

I'm getting an error on the sqlstr line, but I think it was assuming the " around enrolled was closing the string. I changed it to a single quote and it seemed okay with it.

It is having a problem with the SendEmail function. This is a function used in other functions like this:

Function mailReport()
   Dim X As Boolean

   X = SendEmail("admin@mydomain.com;", "Here is the report for the current month.", "k:\report.xls", "Monthly report", "")
          
End Function

Also, you say rst!email should be my email address....
Either I didn't make it clear or I am misunderstanding. The rst!email field needs to be the results of UserID from above (actually, may need @mydomain.com added to it to work, but I haven't tried it without).

 

[PWise]

what is the error

 

[platypus71]

Compile Error: Syntax error

 

[PWise]

try adding
Dim X As Boolean
X = SendEmail...............

 

[platypus71]

More info:
If I click on the line, then click out of it, it reports

Compile error:

Expected: =

 

[platypus71]

Thanks, there was on more problem but I figured it out.

Two more questions. Is there a way to make the variables inserted into the body of the message in bold?

Also, is there a way to put carriage returns into the body of the message?

 

[Darrylles]

Platypus,

Thanks, there was on more problem but I figured it out.

This forum isn't simply here to solve your problems; it also helps others to solve their identical problems by viewing what was done to solve your problem - IF you can be bothered to state the eventual solutions.

Can you add the 'one more problem', along with how you solved it?

Also, you started this thread with a title. People will search and find solutions based on your title.
If you 'add' further questions that are not truly related, then THOSE solutions will be useless to all other users.

Add your 'Two more questions' as new threads.

Regards,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[PWise]

Bold: I dont know

Carriage Returns:bod="This is just a reminder that you have " & rst!CourseName & vbcrlf & " beginning on " & rst!StartDate & " and ending on " & rst!EndDate & vbcrlf & " at " & rst!Location & " from " & rst!Time & " .

 

[petermeachem]

If you want to do bold then you need to send html emails as far as I know. I use CDO, or whatever it is called now to do that. You should be able to find a sample pretty easily.

 

[platypus71]

Darrylles
[code[Can you add the 'one more problem', along with how you solved it?[/code]

Yeah, when I cut and paste, somehow it did some sort of word-wrap to the sqlstr line and put " at the end of all three lines, causing an unexpected end to the line. I deleted the " that were inserted and it worked. Didn't figure that needed to be reported since the 'paste' didn't match the copy exactly.

As for the two more questions, they were related as they are a part of the message body. I just hadn't mentioned any formatting in the post.