Email Records in Text Body?

[burgerman]

What I am trying to do is to use a query named Schedule Email List to send a series of records in a table named schedule as a full email message using MS outlook. I have been successful in using the Docmd send object but that sends the report as an attachment what I am trying to get accomplished is to be able to draft a email that will only send each individual their respective email that will be included in the text body of the email my only formatting requirement is that each new record be displayed on a new line below the previous one.

Any suggestions?

Thanks


History has demonstrated that the most notable winners usually encountered heart-breaking obstacles before they triumphed. They won because they refused to become discouraged by their defeats.
- B.C. Forbes
(1880-1954) Scottish journalist, founded & edited Forbes magazine, 1916; about business, finance

 

[SgtJarrow]

DoCmd.SendObject, while not always the best solution, can accept body text....the arguements are as follows:

DoCmd.SendObject objecttype, objectname, outputformat, to, cc, bcc, subject, messagetext, editmessage, templatefile

All you would need to do is fill in the message text as you want the information formatted....my suggestion, in your case, would be to fill a string variable as necessary and use this...

If you need some more specific examples, please let me know. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[deduct]

I have used this method (messagetext), but be aware that it will be text only without formatting (i.e., no bold, no italics). Also, if it is very long, there is a limit on the number of continuations you can use, but you can get around this by using string variable(s) as mentioned above by mstrmage1768.

If anybody knows how to introduce formatting into "messagetext", please fill me in.

Karl Karl

 

[burgerman]

A few examples of using the string variable would be very helpful

Thaks

 

[SgtJarrow]

As deduct said, this method cannot be used to include such features as bold, italics, size, etc.....it is just plain old text sent out directly...

A very simple example would be:
Dim strMsgtext As String
strMsgText = "This is a test"
DoCmd.SendObject , , , "bob@somewhere.com", , , "Test", strMsgText, False

Now, if you want to get fancier, you could do:
Dim strMsgtext As String
strMsgText = "This is a test " of Me![fieldname] & " to show how to insert a field value"
DoCmd.SendObject , , , "bob@somewhere.com", , , "Test", strMsgText, False

And finally, if you want to create a long string value:
Dim db As DAO.Recordset
Dim rs As DAO.Recordset
Dim strMsgText As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblRecords", dbOpenSnapshot)
With rs
.MoveFirst
Do While Not .EOF
strMsgText = strMsgText & vbCrLf & .Fields([FieldName}])
.MoveNext
Loop
End With
DoCmd.SendObject , , , "bob@somewhere.com", , , "Test", strMsgText, False
Set rs = Nothing
Set db = Nothing Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[deduct]

In this example, the code is behind a command button (you will modify this to fit your situation), and the strings are being populated in the code (you would probably use field names from a table or query). I have also skipped (accepted the default for) all parameters except To:, Subject: and MessageText.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim strEmailAddress As String 'To:
Dim strSubject As String 'Subject:
Dim strMessage As String 'MessageText

strEmailAddress = "youraddress@isp.com"
strSubject = "Simple use of SendObject"
strMessage = "This is my message."

DoCmd.SendObject acSendNoObject, , , _
strEmailAddress, , , strSubject, strMessage

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub Karl

 

[burgerman]

Currently This is the code I am using to email the schedule

Dim db As Database, rst As Recordset
Dim strSQL As String
Dim Names As String

'Return reference to current database
Set db = CurrentDb
strSQL = "SELECT [Schedule Email List].[emailaddressPrimary] FROM [Schedule Email List];"
Set rst = db.OpenRecordset(strSQL)


With rst
.MoveFirst
Names = ![emailaddressPrimary] & ";"

.MoveNext
Do Until .EOF
Names = Names & ![emailaddressPrimary] & ";"
.MoveNext
Loop
End With

'Sending schedule to employees requesting schedule
DoCmd.SendObject acSendReport, "Weekly Schedule Report", acFormatRTF, Forms!hrmain![Sonic Email], , Names, "Schedule From" & " " & [Text7] & " until " & [Text9], Me!Text46, False


rst.Close
End If
'***************end of code********************

What I tried is to make a nesting with statement before the first move next statement to grab the info from the following SQL String and I commented out the second names statement:

SELECT Schedule.SSN, Schedule.Date, Schedule.Time_In, Schedule.Time_Out
FROM Schedule
WHERE (((Schedule.SSN)=[Forms]![schedule print form]![text48]) AND ((Schedule.Date) Between [Forms]![Schedule Print Menu]![text7] And [Forms]![Schedule Print Menu]![Text9]));

I ran into alot of trouble here since the whole "Quote" "Single Quote" thing is still making my head spin.

Does this make sense?

Thanks